Generating a table with records with all possible values from frequency and interval columns

Question

Post reply

Generating a table with records with all possible values from frequency and interval columns

Griffster

SSCertifiable

Points: 7097
More actions
April 9, 2009 at 9:11 am

#136415

I'm not great with cursors anyway, but I'm desperately trying to avoid using them at the moment for the purpose of my query.
I have a table with a reference number, amount, next_payment_date, interval_type, and interval_unit columns. I need to feed in a date range to the query so that I can populate a table with every payment date within this range.
If my start date was 1st of May 2009 and end date is 1st of November 2009 and I have the following values:
Reference_number = 100001
Amount = 100.00
next_payment_date = 15th of April 2009
interval_type = M (monthly)
interval_unit = 1
i.e. paying 100 pounds every 1 month on the 15th of the month.
I would want the following entered in my table (three columns: Reference_number, payment_date, payment_amount):
Reference_number Payment_date payment_amount
100001 15 may 2009 100.00
100001 15 jun 2009 100.00
100001 15 jul 2009 100.00
100001 15 aug 2009 100.00
100001 15 sep 2009 100.00
100001 15 oct 2009 100.00
Can anyone help me here please ideally avoiding cursors (so I don't slip in to bad habits!)?

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Use the Tally table to handle this:

DECLARE @t TABLE (Reference VARCHAR(30), PaymentDate DATETIME, Amount MONEY)

INSERT INTO @t (

Reference,

PaymentDate,

Amount

)

SELECT

'100001',

DATEADD(MONTH, N - 1, '20090115'),

100.00

FROM Tally

WHERE N BETWEEN 5 AND 10

SELECT * FROM @t

If you don't know the Tally table search this site. There you will find an article from Jeff Moden. It is very handy for all those things.

Greets

Flo

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hey Flo that was quick:-P

DECLARE @Reference_number VARCHAR(6),

@Amount MONEY,

@Start_payment_date DATETIME,

@interval_type CHAR(1),

@interval_unit SMALLINT, @sql VARCHAR(200)

SELECT @Reference_number = '100001',

@Amount = 100.0,

@Start_payment_date = '15 may 2009',

@interval_type = 'm', --(monthly)

@interval_unit = 1

SELECT @Reference_number AS [Reference_number],

DATEADD(m, (@interval_unit*(n.number-1)), @Start_payment_date) AS [Payment_date],

@Amount AS [payment_amount]

FROM Numbers n

WHERE n.number <= 12

Although the spec includes an interval, there's no duration so a year has been arbitrarily chosen.

Note that the first parameter of the DATEADD function doesn't like expressions, so you would have to use a CASE to distinguish between different values of @interval_type.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Griffster SSCertifiable Points: 7097 More actions · Answer 3

Here's some code to create a temp table with the values I have in the interval table (M = Months, D = Days in the pay_sched_code column to signify the frequency type with the units_pay_sched = number of frequency type e.g. D & 7 would mean every 7 days):

IF OBJECT_ID('TempDB..#IntervalData','U') IS NOT NULL

DROP TABLE #IntervalData

CREATE TABLE #IntervalData

(

[ref_no] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[prom_to_pay_date] [datetime] NULL,

[amt_prom_pay] [decimal](14, 2) NULL,

[pay_sched_code] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[units_pay_sched] [int] NULL

)

INSERT INTO #IntervalData

(ref_no, prom_to_pay_date, amt_prom_pay, pay_sched_code, units_pay_sched)

SELECT '123597','Apr 5 2009 12:00AM','30.00','M','1' UNION ALL

SELECT '123700','May 9 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '123742','Apr 28 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '123743','Mar 27 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '123817','May 6 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '123830','Apr 25 2009 12:00AM','40.00','M','1' UNION ALL

SELECT '123884','May 25 2009 12:00AM','30.00','M','1' UNION ALL

SELECT '123900','Apr 28 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '123969','Apr 25 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '123989','Apr 27 2009 12:00AM','20.00','M','1' UNION ALL

SELECT '124008','Aug 6 2009 12:00AM','3.00','M','1' UNION ALL

SELECT '124017','May 4 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '124021','Mar 28 2009 12:00AM','2.59','M','1' UNION ALL

SELECT '124035','Apr 28 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '124085','Apr 25 2009 12:00AM','25.00','M','1' UNION ALL

SELECT '124094','Apr 28 2009 12:00AM','25.00','M','1' UNION ALL

SELECT '124100','Apr 14 2009 12:00AM','35.00','D','7' UNION ALL

SELECT '124101','May 1 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '124186','Apr 1 2009 12:00AM','200.00','M','1' UNION ALL

SELECT '124194','Apr 10 2009 12:00AM','5.00','D','7' UNION ALL

SELECT '124202','Mar 28 2009 12:00AM','20.00','M','1' UNION ALL

SELECT '124213','Apr 25 2009 12:00AM','30.00','M','1' UNION ALL

SELECT '124234','Apr 7 2009 12:00AM','30.00','M','1' UNION ALL

SELECT '124269','Apr 2 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '124291','Apr 14 2009 12:00AM','40.00','M','1' UNION ALL

SELECT '124362','Mar 28 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '124401','Apr 18 2009 12:00AM','60.00','M','1' UNION ALL

SELECT '124406','May 4 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '124414','Apr 19 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '124432','Apr 16 2011 12:00AM','7.00','M','1' UNION ALL

SELECT '124440','Jul 25 2009 12:00AM','6.00','M','6' UNION ALL

SELECT '124450','Apr 25 2009 12:00AM','25.00','M','1' UNION ALL

SELECT '124482','Jul 16 2009 12:00AM','40.00','M','1' UNION ALL

SELECT '124541','Apr 13 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '124544','Apr 18 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '124632','Apr 17 2009 12:00AM','5.00','D','7' UNION ALL

SELECT '124669','Apr 11 2009 12:00AM','30.00','M','1' UNION ALL

SELECT '124687','Jun 1 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '124704','Apr 28 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '124715','May 28 2009 12:00AM','5.00','M','1' UNION ALL

SELECT '124737','Mar 25 2009 12:00AM','5.18','M','1' UNION ALL

SELECT '124751','Apr 13 2009 12:00AM','35.00','M','1' UNION ALL

SELECT '124754','Apr 17 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '124773','Mar 28 2009 12:00AM','7.50','D','7' UNION ALL

SELECT '124791','Apr 13 2009 12:00AM','20.00','D','7' UNION ALL

SELECT '124906','Apr 16 2009 12:00AM','35.00','M','1' UNION ALL

SELECT '124911','May 4 2009 12:00AM','30.00','M','1' UNION ALL

SELECT '124968','Apr 18 2009 12:00AM','2.50','D','7' UNION ALL

SELECT '125002','Apr 27 2009 12:00AM','80.00','M','1' UNION ALL

SELECT '125018','Apr 8 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '125040','Apr 27 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '125062','Apr 10 2009 12:00AM','5.00','M','1' UNION ALL

SELECT '125128','Apr 20 2009 12:00AM','1.00','M','1' UNION ALL

SELECT '125133','Apr 13 2009 12:00AM','110.00','M','1' UNION ALL

SELECT '125215','Apr 25 2009 12:00AM','82.00','M','1' UNION ALL

SELECT '125254','Apr 10 2009 12:00AM','15.00','D','7' UNION ALL

SELECT '125291','May 22 2009 12:00AM','3.00','M','1' UNION ALL

SELECT '125302','May 2 2009 12:00AM','25.00','M','1' UNION ALL

SELECT '125318','Apr 27 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '125329','Apr 28 2009 12:00AM','40.00','M','1' UNION ALL

SELECT '125331','Apr 19 2009 12:00AM','135.00','M','1' UNION ALL

SELECT '125377','Apr 27 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '125378','Apr 15 2009 12:00AM','20.00','D','7' UNION ALL

SELECT '125613','Apr 20 2009 12:00AM','10.00','M','1' UNION ALL

SELECT '125644','Apr 30 2009 12:00AM','60.00','M','1' UNION ALL

SELECT '129039','Mar 28 2009 12:00AM','40.00','M','1' UNION ALL

SELECT '128981','May 2 2009 12:00AM','60.00','M','1' UNION ALL

SELECT '128973','Apr 28 2009 12:00AM','50.00','M','1' UNION ALL

SELECT '128965','May 28 2009 12:00AM','25.00','M','1' UNION ALL

SELECT '128940','May 2 2009 12:00AM','30.00','M','1' UNION ALL

SELECT '129060','Apr 3 2009 12:00AM','30.00','M','1'

Griffster SSCertifiable Points: 7097 More actions · Answer 4

[font="Tahoma"]I can just about work out what you're doing here. First section declaring the variables, second initialising them, and the third looping through your tally table. In a bit of a muddle as to how to apply it to my test file though as I need to loop through each record in the table. I need to produce a record for all possible scheduled payment dates that would be added between two dates, say between 1st of May 2009 and 1st of November 2009. [/font]

Chris Morris (4/9/2009)
Hey Flo that was quick:-P
DECLARE @Reference_number VARCHAR(6),
@Amount MONEY,
@Start_payment_date DATETIME,
@interval_type CHAR(1),
@interval_unit SMALLINT, @sql VARCHAR(200)
SELECT @Reference_number = '100001',
@Amount = 100.0,
@Start_payment_date = '15 may 2009',
@interval_type = 'm', --(monthly)
@interval_unit = 1
SELECT @Reference_number AS [Reference_number],
DATEADD(m, (@interval_unit*(n.number-1)), @Start_payment_date) AS [Payment_date],
@Amount AS [payment_amount]
FROM Numbers n
WHERE n.number <= 12
Although the spec includes an interval, there's no duration so a year has been arbitrarily chosen.
Note that the first parameter of the DATEADD function doesn't like expressions, so you would have to use a CASE to distinguish between different values of @interval_type.
Cheers
ChrisM

Griffster SSCertifiable Points: 7097 More actions · Answer 5

Florian, thanks for your comment here too, but my reply to Chris' comment applies to yours too. Can you see my test data please?

Griffster SSCertifiable Points: 7097 More actions · Answer 6

Anyone able to help me with this or my last comment please?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Using just the first 2 or 3 values in your test data, could you please provide us with the expected output from the process? This will greatly assist in developing a solution to your problem.

Griffster SSCertifiable Points: 7097 More actions · Answer 8

Here you go. I've put the results in an excel format to make it clearer and changed the interval type for the third record from M (monthly) to D (days) to illustrate how that would act differently. Ignore the formatting for the result by the way. Hope this helps.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

You indicate an end date in your original post, but I don't see that in your test data. How is this determined or is it passes as a parameter?

Your original post had an end date of 2009-11-01.

Griffster SSCertifiable Points: 7097 More actions · Answer 10

I'd be entering that date as a parameter (as well as the start date of 1st of May 2009 in my example run). Would need to accept two entered dates for start and finish.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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;

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 12

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 int

DECLARE @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

Griffster SSCertifiable Points: 7097 More actions · Answer 13

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 SSCertifiable Points: 7097 More actions · Answer 14

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 int
DECLARE @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?