April 20, 2009 at 8:21 am
Griffster (4/20/2009)
Lynn Pettis (4/15/2009)
Here is some code for you to examine and play with. I hope it helps.
declare @StartDate datetime,
@EndDate datetime;
select
@StartDate = '2009-05-01',
@EndDate = '2009-11-01';
with
CTE (Number) as
(select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10)
,CTE2 (Number) as
(select c1.Number
from CTE c1
cross join CTE c2)
,Tally (N) as
(select row_number() over (order by c3.Number)
from CTE2 c3
cross join CTE2 c4)
select
ref_no,
amt_prom_pay,
case pay_sched_code
when 'M' then dateadd(mm, (N - 1) * units_pay_sched, prom_to_pay_date)
when 'D' then dateadd(dd, (N - 1) * units_pay_sched, prom_to_pay_date)
end as PayDate
from
#IntervalData id
cross join Tally t
where
case pay_sched_code
when 'M' then dateadd(mm, (N - 1) * units_pay_sched, prom_to_pay_date)
when 'D' then dateadd(dd, (N - 1) * units_pay_sched, prom_to_pay_date)
end between @StartDate and @EndDate
order by
ref_no,
case pay_sched_code
when 'M' then dateadd(mm, (N - 1) * units_pay_sched, prom_to_pay_date)
when 'D' then dateadd(dd, (N - 1) * units_pay_sched, prom_to_pay_date)
end;
That seems to have worked great although, I don't understand it really...lol. Could you explain the principles here for me please?
Griffster (4/20/2009)
andrewd.smith (4/15/2009)
This query appears to reproduce your expected results.It uses a Tally / Numbers table with name Tally that includes the value 0 (zero).
There is a pre-query to retrieve the minimum and maximum values (@MinN int, @MaxN) to use from the Tally table in the subsequent query. This is intended to improve performance by limiting the number of rows on which the "complex" date calculation is performed.
DECLARE @MinN int, @MaxN intDECLARE @StartDate datetime, @EndDate datetime
SELECT
@StartDate = '20090501',
@EndDate = '20091101'
SELECT
@MaxN = MAX(
CASE WHEN (TD.prom_to_pay_date <= @EndDate) THEN
CASE TD.pay_sched_code
WHEN 'M' THEN DATEDIFF(month, TD.prom_to_pay_date, @EndDate)
WHEN 'W' THEN DATEDIFF(week, TD.prom_to_pay_date, @EndDate)
WHEN 'D' THEN DATEDIFF(day, TD.prom_to_pay_date, @EndDate)
ELSE NULL
END
ELSE NULL
END / TD.units_pay_sched + 1),
@MinN = MIN(
CASE WHEN (TD.prom_to_pay_date <= @StartDate) THEN
CASE TD.pay_sched_code
WHEN 'M' THEN DATEDIFF(month, TD.prom_to_pay_date, @StartDate)
WHEN 'W' THEN DATEDIFF(week, TD.prom_to_pay_date, @StartDate)
WHEN 'D' THEN DATEDIFF(day, TD.prom_to_pay_date, @StartDate)
ELSE NULL
END
ELSE 0
END / TD.units_pay_sched)
FROM #IntervalData TD
SELECT
TD.ref_no AS Ref_No,
CASE TD.pay_sched_code
WHEN 'M' THEN DATEADD(month, T.N * TD.units_pay_sched, TD.prom_to_pay_date)
WHEN 'W' THEN DATEADD(week, T.N * TD.units_pay_sched, TD.prom_to_pay_date)
WHEN 'D' THEN DATEADD(day, T.N * TD.units_pay_sched, TD.prom_to_pay_date)
ELSE NULL
END AS Payment_date,
amt_prom_pay AS Amount
FROM dbo.Tally T
CROSS JOIN #IntervalData TD
WHERE (T.N BETWEEN @MinN AND @MaxN)
AND (
CASE TD.pay_sched_code
WHEN 'M' THEN DATEADD(month, T.N * TD.units_pay_sched, TD.prom_to_pay_date)
WHEN 'W' THEN DATEADD(week, T.N * TD.units_pay_sched, TD.prom_to_pay_date)
WHEN 'D' THEN DATEADD(day, T.N * TD.units_pay_sched, TD.prom_to_pay_date)
ELSE NULL
END
) BETWEEN @StartDate AND @EndDate
ORDER BY Ref_No, Payment_date
This crashes as there's no Tally table defined. How did you set this up please?
Search this site for Tally Table. You should find an article written by Jeff Moden that will discribe it quite well. Also, the query I provided has the Tally table included dynamically using CTE's.
The best way to explain my code would be to have you ask specific questions regarding the code. I'd also suggest reading Books Online about CTE's and the different types of joins. That will give you some background that will make the explainations easier to understand.
April 20, 2009 at 8:40 am
There are many ways to set up a permanent Tally or Numbers table.
Here's one way that inserts all the integers from 0 to 9999.
If you wish to use a permanent Tally table you might wish to put it in the master database.
CREATE TABLE dbo.Tally (
N int NOT NULL PRIMARY KEY
)
GO
;WITH CTE (N) AS (
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9
)
INSERT dbo.Tally (N)
SELECT C1.N + 10 * (C2.N + 10 * (C3.N + 10 * C4.N))
FROM CTE C1
CROSS JOIN CTE C2
CROSS JOIN CTE C3
CROSS JOIN CTE C4
April 20, 2009 at 9:50 am
Analysed the links to the Tally tables etc now and I think I've got to the bottom of the code now thanks. See how the CTE tally table works now too but a bit curious as to why you did it that way rather than something like the following that Jeff Moden uses as an example
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
Is this dynamic one much quicker may be?
April 20, 2009 at 10:16 am
One reason I used a dynamic tally table was simple, what if you didn't already have one or you called it by another name such as Numbers? By doing it dynamically, my code was not dependent on it existing and would work properly.
In addition I have found a dynamic tally table works equally as well as a permanent tally table. To expand this more, I have recently submitted an article to SSWUG (sorry Steve, but I'll keep you posted regarding the article) regarding dynamic tally (or numbers) tables in response to an article in SQL Server Magazine regarding the same. Personally, I don't recommend the method proposed in SQL Server Magazine as it isn't very scalable.
April 20, 2009 at 10:29 am
Makes sense that.
Do you have a link I could navigate to to read your article please? Interested in increasing my knowledge of this part of sql.
April 20, 2009 at 10:35 am
Griffster (4/20/2009)
Makes sense that.Do you have a link I could navigate to to read your article please? Interested in increasing my knowledge of this part of sql.
Not at this time. I just submitted it this weekend after having it reviewed by a respected source. Once I hear more I will post the information on my blog (SQL Musings from the Desert).
April 20, 2009 at 10:35 am
Thanks Andrew by the way too. Your query now works with that tally table setup.
April 20, 2009 at 10:38 am
Ok. I'll set myself a reminder to check the blog out every few days then. Many thanks.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply