I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?
I'm trying to pull all records from one table and just a single record from another. I have this join, (see below). It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return. The end result should be the blog record and a single corresponding image record. But always a blog record.
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:
DELETE FROM Invoices WHERE InvoiceID = 153345
Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.
Can you please assist me on how to get the 2nd record in case there are3 or more records of an employee, the query below gets the MAX and MINBasicSalary. However, my MIN Basic Salary is wrong because I should getthe Basic Salary Prior to the 1st Record (DESC)in case there are 3 ormore records and not the last Basic Salary of the Last Record.How to GET the 2nd Row of Record in Case that There are 3 or morerecords IN A SINGLE ROW ???---------------------------------------------------------------------------*-----This query gets the Max and Min Basic Salary on a certain Date Range.In case there are 5 records of an employee on certain date range howcan I get the record before the Max and would reflect as my OLDBASIC,if I use TOP2 DESC it will display 2 records. I only need one recordwhich should be the Basic Salary before the 1st record on a DESC order.Please add the solution to my 2nd Select Statement which get theOLDBASIC salary Thanks ...SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,E.SectionCode, E.Department, E.DateHired, E.Remarks,(SELECT TOP 1 ([BasicSalary])FROM empsalaries AS T14WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND@ToDateORDER BY startdate DESC) AS NEWBASIC,******************************* BELOW I SHOULD ALWAYS GET THE BASICSALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ???(SELECT TOP 1 ([BasicSalary]) (FROM empsalaries AS T14WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND@ToDateORDER BY startdate ASC) AS OLDBASICFROM dbo.Employees EWHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @FromDate AND@ToDate ORDER BY E.LastName
Can someone please tell me which type of Isolation Levels in the Database Engine to avoid the above side effects respectively.
For Record Locking issue: Example, two editors make an electronic copy of the same document. Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. The editor who saves the changed copy last overwrites the changes made by the other editor. This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.
For the above example, is it possible to do like this way: Editor 1: SELECT and Lock a record Editor 2: Before SELECT the record, check for the record whether it is locked or not. If it is not lock, then the record can be selected
how to convert float to timestamp in single select query..for exp. i have float as 1.251152515236 ,i want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
I am using a drop down list box to select values from an SQL 2000 DB. I have put a record into the table which says"...Select Item"I used the dots so that It will apear at the top of the list and therefore display at startup. I also have a gridview to allow users to edit and delete from the same table.I am worried that a user may accidently delete the "...Select Item" row from the table.Can anyone think of a way round this? Is it possible with SQL Server 2000 to stop a single record from being deleted?Any help would be most appreciated.ThanksPaul
HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee
a record value instead of aggregated value with GROUP BY?
Assume that I have a PRODUCT_COMMENT table defined as below. It logs the multiple comments for products. A product may have multiple comments logged at different time.
Code Block
CREATE TABLE [dbo].[PRODUCT_COMMENT]( [COMMENT_ID] [int] IDENTITY(1,1) NOT NULL, [PRODUCT_ID] [int] NOT NULL, [COMMENT] [nvarchar](50) NULL, [UPDATED_ON] [datetime] NOT NULL, CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED ( [COMMENT_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID]) REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID]) GO ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT]
I would like to use the following SQL statement to get the latest comments for all products.
Code Block
SELECT PRODUCT_ID, COMMENT, UPDATED_ON FROM PRODUCT_COMMENT GROUP BY PRODUCT_ID HAVING UPDATED_ON = MAX(UPDATED_ON)
But this leads to the following error:
Code Block
Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT 'Type'[Type] ,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END FROM Table1 WHERE (Code BETWEEN '09' AND '12') GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4 Type 14022731.60 0.00 0.00 0.00 Type 0.00 4749072.19 0.00 0.00 Type 0.00 0.00 149214.04 0.00 Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50)) insert into #temp values(1,'test','head1') insert into #temp values(1,'test','head2') insert into #temp values(1,'test','head3') insert into #temp values(2,'test1','head1') insert into #temp values(2,'test1','head2')
Required output
depID Name LocationHead 1test *head1,*head2,*head3 2test1 *head1,*head2
Is me again,and now i facing problem to retrieve a single record from the database. here is my code:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim user As String user = TextBox1.Text Dim varpassword Dim mydata As SqlDataSource mydata.SelectCommand = "Select * from tbluser where uLogin = '" & user & "'" varpassword = mydata.SelectCommand.uPassword End SubEnd Class
but i get the error : 'uPassword' is not a member of 'String' i wan to retrieve the password of that user,can anyone help me? thanks
that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client
this does not seem like a very good solution ( or is it?) If posible it would be nice to get something like this
TermID, Term, RelatedTermsInformation 1 test RelatedTermsTwoDimentionalArray
but I am not sure how this idea could be implemented using the capabilities of SQL.
my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.
I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.
Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:
I have a sql query which brings me many records. This records have a column in common which have the same value (COL1)
There is a second column (COL2) which has different values bewteen these records. I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.
Let me give you an example: COL1 COL2 RECORD1 1-A HHH RECORD2 1-A GGG RECORD3 1-B LLL RECORD4 1-B MMM RECORD4 1-B OOO RECORD5 1-C NNN
Me result should be:
COL1 COL2 RECORD 1-A HHHGGG RECORD 1-B LLLMMMOOO RECORD 1-C NNN
It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS. Thanks for any help you can give me.
Hi All I'm getting an error that says that this can't be converted to an integer. Here is the line that gets the error. dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable() I have also tried. dt = (DataView)(EventDataSource1.Select(dssa); I am programming in VB here is teh rest of my code. Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()
SELECT U.UserFamilyName, F.FamilyName FROM Users U LEFT JOIN User_Friends F ON U.UserID = F.UserID WHERE U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
What is the most efficient standalone .NET class that can hold a single disconnected record? The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.I'm familiar with DataSet, is that the only .NET-standard class to do this? Thank you,Shannon
Hello, I have problem for insert multiple query for insert in differenr tabels for a single record. I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport, i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then........... so plz help me Thanks
Hello, In SQL Server Management Studio (2005), 'Open table' command or a SELECT query displays table rows in a grid (or text).Please tell how to view a single row at a time i.e. all only ONE row is displayed at a time (evenly arranged to cover the screen). This feature (Single record view) is available in other database client like TOAD. Thank You
I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database.
As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file.
For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure:
I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work
I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.
People Name ID Tom5 Dick2 Harry3 Larry4 Curly1 Scores PrimaryKeyPeopleIDScoreTestNumber 12801
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
