June 21, 2010 at 11:17 am
hi guys..
help me out
i need to update the column value with the next valid value i.e.,
SampleData
ID DID value DateTime
1 1 10 '2010-05-14 00:00:00'
2 1 0 '2010-05-14 05:00:00'
3 1 0 '2010-05-14 10:00:00'
4 1 0 '2010-05-14 10:00:00'
5 1 35 '2010-05-14 30:00:00'
6 2 20 '2010-06-18 00:00:00'
7 2 0 '2010-06-18 00:00:00'
8 2 10 '2010-06-18 05:00:00'
SampleData Answer
ID DID value DateTime
1 1 10 '2010-05-14 00:00:00'
2 1 0 '2010-05-14 30:00:00'
3 1 0 '2010-05-14 30:00:00'
4 1 0 '2010-05-14 30:00:00'
5 1 35 '2010-05-14 30:00:00'
6 2 20 '2010-06-18 00:00:00'
7 2 0 '2010-06-18 05:00:00'
8 2 10 '2010-06-18 05:00:00'
Basically, If value = 0 then, I need to update the DateTime Value column with the next DateTime Value where Value != 0
CREATE TABLE A
(
ID INT IDENTITY(1,1)
, DID INT
, Value INT
,[DateTime] DateTime
)
INSERT INTO A (DID,Value,[DateTime])
SELECT 1,10,'2010-05-14 00:00:00' UNION ALL
SELECT 1, 0,'2010-05-14 30:00:00' UNION ALL
SELECT 1, 0,'2010-05-14 30:00:00' UNION ALL
SELECT 1, 0,'2010-05-14 30:00:00' UNION ALL
SELECT 1, 35, '2010-05-14 30:00:00' UNION ALL
SELECT 2, 20, '2010-06-18 00:00:00' UNION ALL
SELECT 2, 0, '2010-06-18 05:00:00' UNION ALL
SELECT 2, 10, '2010-06-18 05:00:00'
Thanks in advance
June 21, 2010 at 12:59 pm
I had to make a change to your sample data 30:00:00 is not a valid time.
hopefully this will take care of your problem.
Declare @sample TABLE
(ID INT IDENTITY(1,1), DID INT, [Value]INT,[DateTime] DateTime)
INSERT INTO @sample (DID,[Value],[DateTime]) values(1,10,'2010-05-14 00:00:00')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0,'2010-05-14 03:00:00')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0,'2010-05-14 03:00:00')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0,'2010-05-14 03:00:00')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 35, '2010-05-14 03:00:00')
INSERT INTO @sample (DID,[Value],[DateTime]) values(2, 20, '2010-06-18 00:00:00')
INSERT INTO @sample (DID,[Value],[DateTime]) values(2, 0, '2010-06-18 05:00:00')
INSERT INTO @sample (DID,[Value],[DateTime]) values(2, 10, '2010-06-18 05:00:00')
select a.ID,a.DID,a.Value,
case
when [Value]=0 then (Select top 1 [DateTime] from @sample where [Value]<>0 and ID>a.ID)
else [DateTime]
end
from @sample a
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 21, 2010 at 1:31 pm
Thanks for correcting the statement.. I have tried the sql and it updates the value with the last DateTime value i.e., '2010-04-13 04:00:00.000' instead of updating it with the next datetime value... Below is the expected output and the Insert SQL... I have underlined the values that should be updated... Please help me out
Expected output
1011002010-04-13 02:30:00.000
1111002010-04-13 02:35:00.000
1211002010-04-13 02:40:00.000
13102010-04-13 02:55:00.000
14102010-04-13 02:55:00.000
1511002010-04-13 02:55:00.000
1611002010-04-13 03:00:00.000
1711002010-04-13 03:05:00.000
18102010-04-13 03:55:00.000
19102010-04-13 03:55:00.000
20102010-04-13 03:55:00.000
21102010-04-13 03:55:00.000
22102010-04-13 03:55:00.000
23102010-04-13 03:55:00.000
24102010-04-13 03:55:00.000
25102010-04-13 03:55:00.000
26102010-04-13 03:55:00.000
2711002010-04-13 03:55:00.000
281202010-04-13 04:00:00.000
Declare @sample TABLE
(ID INT IDENTITY(1,1), DID INT, [Value]INT,[DateTime] DateTime)
INSERT INTO @sample (DID,[Value],[DateTime]) values(1,100,'2010-04-13 02:30:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1,100,'2010-04-13 02:35:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1,100,'2010-04-13 02:40:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0,'2010-04-13 02:45:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 02:50:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 02:55:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 03:00:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 03:05:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:10:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:15:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:20:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:25:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:30:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:35:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:40:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:45:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:50:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 03:55:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 20, '2010-04-13 04:00:00.000')
June 21, 2010 at 1:33 pm
Dan,
Good job, and thanks for posting the data in a readily consumable format.
Edit: deleted code... didn't do the job.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 21, 2010 at 1:42 pm
danny,
you indicate you are having a problem with the Query I provided but I am not sure what problem you are having. I ran it against your sample data and I got back the exact output you indicate.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 21, 2010 at 1:48 pm
Hi wayne,
Thanks for pointing about the indexes.. i do have indexing on the table. i tested your SQL and i see that it results the same inserted data without any changes.. Am i doing something wrong ? help me .thanks
June 21, 2010 at 2:05 pm
you indicate you are having a problem with the Query I provided but I am not sure what problem you are having.
Dan, When i execute the SQL.. it is selecting the last row and replacing it with the Date where the value is 0 instead of picking the dateValue after it.. probably i was not clear in my 1st post below are the examples along with the output that i get when i run the sql you have posted
for example
ID DID Value DateTime
1 1 100 2010-03-12 00:00:00
2 1 0 2010-03-12 01:00:00
3 1 0 2010-03-12 02:00:00
4 1 10 2010-03-12 05:00:00
5 1 10 2010-03-12 10:00:00
So, the expected output is as below
ID DID Value DateTime
1 1 100 2010-03-12 00:00:00
2 1 0 2010-03-12 05:00:00
3 1 0 2010-03-12 05:00:00
4 1 10 2010-03-12 05:00:00
5 1 10 2010-03-12 10:00:00
But when i run the SQL you have posted i am getting the below output
for example
ID DID Value DateTime
1 1 100 2010-03-12 00:00:00
2 1 0 2010-03-12 10:00:00
3 1 0 2010-03-12 10:00:00
4 1 10 2010-03-12 05:00:00
5 1 10 2010-03-12 10:00:00
please see the diff in the ITALIC Characters
June 21, 2010 at 2:12 pm
I know it is monday but I really feal dense.
When I run the following Query I get the exact results row by row you say you are expecting. When the value is 0 it will go pull the first date after that ID where the value is not equal to zero.
I do not get the reults you get with this query. Again I get the exact results that you expect
select a.ID,a.DID,a.Value,
case
when [Value]=0 then (Select top 1 [DateTime] from @sample where [Value]<>0 and ID>a.ID)
else [DateTime]
end
from @sample a
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 21, 2010 at 2:29 pm
Dan.Humphries (6/21/2010)
I know it is monday but I really feal dense....I get the exact results that you expect
Danny, I'm agreeing with Dan here. If you take the below code, you get the results that you posted.
(Note that I'm incrementing the ID by 9 - it seems that you ran it with both sets of insert data instead of just the data that you posted.
Declare @sample TABLE
(ID INT IDENTITY(1,1), DID INT, [Value]INT,[DateTime] DateTime)
INSERT INTO @sample (DID,[Value],[DateTime]) values(1,100,'2010-04-13 02:30:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1,100,'2010-04-13 02:35:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1,100,'2010-04-13 02:40:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0,'2010-04-13 02:45:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 02:50:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 02:55:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 03:00:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 03:05:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:10:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:15:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:20:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:25:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:30:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:35:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:40:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:45:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 0, '2010-04-13 03:50:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 100, '2010-04-13 03:55:00.000')
INSERT INTO @sample (DID,[Value],[DateTime]) values(1, 20, '2010-04-13 04:00:00.000')
select a.ID+9,a.DID,a.Value,
case
when [Value]=0 then (Select top 1 [DateTime] from @sample where [Value]<>0 and ID>a.ID)
else [DateTime]
end
from @sample a
Danny, based on this sample data, exactly which ID lines are wrong, and what do you think they should be.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 21, 2010 at 2:35 pm
danny09 (6/21/2010)
Hi wayne,Thanks for pointing about the indexes.. i do have indexing on the table. i tested your SQL and i see that it results the same inserted data without any changes.. Am i doing something wrong ? help me .thanks
Nope, I did something wrong... I didn't compare results to expected results. When I did, I found my solution was wrong in several ways, so I removed it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 21, 2010 at 3:18 pm
I know it is monday but I really feal dense.
Sorry Dan..It works perfectly .. i screwed up something and i was testing it against my table and was getting wrong values but the It works fine agianst the sample table.. Checking my table/query for errors
Thanks a lot Dan, wayne
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply