Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query help for value changes from '1/1/1900'

Author  Topic 

shilpash
Posting Yak Master

103 Posts

Posted - 2013-02-18 : 14:18:04
i need a query where value changes from 1/1/1900

i have id,kDueDate,asofdate as a column
if kduedate vlaue change from '1/1/1900 to other value ,i need date when it changes.
eg
id kduedate, asofdate
1 1900-01-01 2/12/2011
1 2012-01002 2/13/2011

I need to display that 2/13/2011 as this is the date when kduedate value changes from 1900-01-01.
Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-18 : 14:31:17
This works for your example. I threw in an extra value for when the kduedate didn't change

DECLARE @Table Table (id tinyint,kduedate date,asofdate date)
INSERT INTO @Table
VALUES
(1, '1900-01-01','2/12/2011'),
(1,'2012-01-02','2/13/2011'),
(2,'1900-01-01','2/12/2011')


SELECT t1.*,tgt.kduedate
FROM @TABLE T1
CROSS APPLY
( select top 1 *
from @Table t2
where t1.id = t2.id
and t2.kduedate <> '19000101'

) tgt
WHERE t1.kduedate = '19000101'


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 2013-02-18 : 16:24:21
Id remains the same,what I mean is for that particular id,value of kduedate change from '1/1/1900' to some other in particular day(asofdate) and I need that day(asodate),for that id.Eg-s

id kduedate asofdate
1 1900-01-01 '2/12/2011'
1 2012-01-02 '2/13/2011'
1 1900-01-01 '2/12/2011'
2 1900-01-01 '2/13/2011'
2 1900-01-01 '2/14/2011'
2 1900-01-01 '2/15/2011'
2 2012-01-25 '2/16/2011'

So My results would be
1 2/13/2011
2 2/16/2011
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 16:52:18
Adapting Jim's sample data and query a little bit:
DECLARE @Table Table (id tinyint,kduedate date,asofdate date)
INSERT INTO @Table
VALUES
(1,'1900-01-01','2/12/2011'),
(1,'2012-01-02', '2/13/2011'),
(1,'1900-01-01', '2/12/2011'),
(2,'1900-01-01', '2/13/2011'),
(2,'1900-01-01', '2/14/2011'),
(2,'1900-01-01', '2/15/2011'),
(2,'2012-01-25', '2/16/2011')


SELECT DISTINCT id,
COALESCE(tgt.newkduedate,t1.kduedate) AS newkduedate
FROM @TABLE T1
OUTER APPLY
(
SELECT MIN(asofdate) AS newkduedate
FROM @Table t2
WHERE t1.id = t2.id
AND t2.kduedate <> '19000101'
AND t2.asofdate > t1.asofdate

) tgt
WHERE t1.kduedate = '19000101'
Go to Top of Page
   

- Advertisement -