June 24, 2011 at 7:35 pm
I have a test table that looks like this:
Part AptDue Visit
Bobby2000-01-01 0
Mary2000-02-29 0
I am using a recursive cte to project date windows based on AptDue. The code looks like this and runs just fine:
with Windows as (
select part,
visit,
dateadd(mm,-6,aptdue) as bigwinopen,
dateadd(dd,-90,aptdue) as smallwinopen,
aptdue,
dateadd(dd,90,aptdue) as smallwinclose,
dateadd(mm,6,aptdue) as bigwinclose
from TEST
union All
select part,
visit + 10,
dateadd(mm,-6,dateadd(yy,1,aptdue)) as bigwinopen,
dateadd(dd,-90,dateadd(yy,1,aptdue)) as smallwinopen,
dateadd(yy,1,aptdue),
dateadd(dd,90,dateadd(yy,1,aptdue)) as smallwinclose,
dateadd(mm,6,dateadd(yy,1,aptdue)) as bigwinclose
from Windows w
where aptdue < dateadd(yy,-1,getdate())
)
select * from windows
where part = 'Mary'
My only problem is the leap day. See how Mary's first AptDue is 2-29-2000? Well, of course, in the results, all her following AptDue dates are 2-28. But in 2004, 2008, etc., I'd like to see her AptDue show up as 2-29. I understand why this is happening, but I'm not sure of a good way to handle it.
Anyone have any suggestions?
part visit bigwinopen smallwinopen aptdue smallwinclose bigwinclose
Mary01999-08-29 1999-12-01 2000-02-29 2000-05-29 2000-08-29
Mary102000-08-28 2000-11-30 2001-02-28 2001-05-29 2001-08-28
Mary202001-08-28 2001-11-30 2002-02-28 2002-05-29 2002-08-28
Mary302002-08-28 2002-11-30 2003-02-28 2003-05-29 2003-08-28
Mary402003-08-28 2003-11-30 2004-02-28 2004-05-28 2004-08-28
Mary502004-08-28 2004-11-30 2005-02-28 2005-05-29 2005-08-28
Mary602005-08-28 2005-11-30 2006-02-28 2006-05-29 2006-08-28
Mary702006-08-28 2006-11-30 2007-02-28 2007-05-29 2007-08-28
Mary802007-08-28 2007-11-30 2008-02-28 2008-05-28 2008-08-28
Mary902008-08-28 2008-11-30 2009-02-28 2009-05-29 2009-08-28
Mary1002009-08-28 2009-11-30 2010-02-28 2010-05-29 2010-08-28
Mary1102010-08-28 2010-11-30 2011-02-28 2011-05-29 2011-08-28
June 24, 2011 at 10:19 pm
I would like to help. Do you mind providing some DDL and DML to build the test environment consistent with your post?
Here is what I mean:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 24, 2011 at 10:53 pm
this is one suggestion
with Windows as (
select part,
visit,
dateadd(mm,-6,aptdue) as bigwinopen,
dateadd(dd,-90,aptdue) as smallwinopen,
aptdue,
dateadd(dd,90,aptdue) as smallwinclose,
dateadd(mm,6,aptdue) as bigwinclose
from TEST2
union All
select part,
visit + 10,
dateadd(mm,-6,dateadd(yy,1,aptdue)) as bigwinopen,
dateadd(dd,-90,dateadd(yy,1,aptdue)) as smallwinopen,
case when (year(dateadd(yy,1,aptdue)) % 4 = 0
and year(dateadd(yy,1,aptdue)) % 100 != 0)
or year(dateadd(yy,1,aptdue)) % 400 = 0
then dateadd(d,1,dateadd(yy,1,aptdue))
else dateadd(yy,1,aptdue)
end,
dateadd(dd,90,dateadd(yy,1,aptdue)) as smallwinclose,
dateadd(mm,6,dateadd(yy,1,aptdue)) as bigwinclose
from Windows w
where aptdue < dateadd(yy,-1,getdate())
)
select * from windows
where part = 'Mary'
June 25, 2011 at 1:49 am
opc.three (6/24/2011)
I would like to help. Do you mind providing some DDL and DML to build the test environment consistent with your post?Here is what I mean:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
create table #test
(Part varchar(10),
AptDue datetime,
Visit int)
insert into #test(Part, AptDue, Visit)
values ('Bobby', '2000-01-01', 0)
insert into #test(Part, AptDue, Visit)
values ('Mary', '2000-02-29', 0)
Is this what you're looking for?
June 25, 2011 at 1:57 am
prvmine (6/24/2011)
this is one suggestion
with Windows as (select part,
visit,
dateadd(mm,-6,aptdue) as bigwinopen,
dateadd(dd,-90,aptdue) as smallwinopen,
aptdue,
dateadd(dd,90,aptdue) as smallwinclose,
dateadd(mm,6,aptdue) as bigwinclose
from TEST2
union All
select part,
visit + 10,
dateadd(mm,-6,dateadd(yy,1,aptdue)) as bigwinopen,
dateadd(dd,-90,dateadd(yy,1,aptdue)) as smallwinopen,
case when (year(dateadd(yy,1,aptdue)) % 4 = 0
and year(dateadd(yy,1,aptdue)) % 100 != 0)
or year(dateadd(yy,1,aptdue)) % 400 = 0
then dateadd(d,1,dateadd(yy,1,aptdue))
else dateadd(yy,1,aptdue)
end,
dateadd(dd,90,dateadd(yy,1,aptdue)) as smallwinclose,
dateadd(mm,6,dateadd(yy,1,aptdue)) as bigwinclose
from Windows w
where aptdue < dateadd(yy,-1,getdate())
)
select * from windows
where part = 'Mary'
Well, this is headed in the right direction. Works for Mary's AptDue dates, but doesn't fix Mary's smallwinclose or bigwinclose on those leap years, so I guess I'd have to add case statements to those lines as well...
But it doesn't work for Bobby. It actually causes his AptDue dates to increment by one day every four years. So for four years his AptDue date is 01-01, but the four years after that it's 01-02, and then the four years after that it's 01-03, and so on.
June 25, 2011 at 8:20 am
srferson-957148 (6/25/2011)
opc.three (6/24/2011)
I would like to help. Do you mind providing some DDL and DML to build the test environment consistent with your post?Here is what I mean:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
create table #test
(Part varchar(10),
AptDue datetime,
Visit int)
insert into #test(Part, AptDue, Visit)
values ('Bobby', '2000-01-01', 0)
insert into #test(Part, AptDue, Visit)
values ('Mary', '2000-02-29', 0)
Is this what you're looking for?
:pinch: Yip, sorry, I could have done that part myself without too much hassle...I forgot to ask for the most important thing which are your expected results. I need to save the request to a file so I can just copy and paste it, I probably ask it at least 4 times a week. Re: the DDL and DML, you did a nice job in your initial post I just threw in the link so you could see that article for future posts, it was the expected results I was really after. Since I am asking you for stuff, any business rules that apply to the non-AptDue columns you could talk about would be helpful too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply