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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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'
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shilpash
Posting Yak Master

103 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
2875 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

103 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

3873 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  
 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.06 seconds. Powered By: Snitz Forums 2000