SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query help for value changes from '1/1/1900'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shilpash
Posting Yak Master

101 Posts

Posted - 02/18/2013 :  14:18:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/18/2013 :  14:31:17  Show Profile  Reply with Quote
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

101 Posts

Posted - 02/18/2013 :  16:24:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 02/18/2013 :  16:52:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000