August 3, 2014 at 3:24 am
Hi,
I need help with some code!
Scenario:
I know the ContractNumber
I know the StartDate of a contract (always in the past)
I know the RentalAmount (fixed for the duration)
I know the RentalFrequency (Monthly, Quarterly, Annual from StartDate)
I need the code to recreate all payments dates (RentalDate) to date, showing value paid at each interval.
Output would look something like this:
eg.
ContractNumber = 12345
StartDate = 01/01/2013
RentaAmount = 100
RetalFrequency = Quarterly
ContractNumber RentalDate RentalAmount
12345 01/01/2013 100
12345 01/04/2013 100
12345 01/07/2013 100
12345 01/10/2013 100
12345 01/01/2014 100
12345 01/04/2014 100
12345 01/07/2014 100
Any help would be appreciated,
THANKS!!
August 3, 2014 at 3:29 am
Please provide table definition and sample data leading to your expected result in a ready to use format as described in the first link in my signature.
Also, please include what you've tried so far and where you get stuck.
Without knowing your table structure I'd guess it's as simple as
SELECT columns_needed
FROM unknown
WHERE col1=criteria1 AND col2 = criteria2 AND col3 > criteria3
August 3, 2014 at 1:37 pm
P74 (8/3/2014)
Hi,I need help with some code!
Scenario:
I know the ContractNumber
I know the StartDate of a contract (always in the past)
I know the RentalAmount (fixed for the duration)
I know the RentalFrequency (Monthly, Quarterly, Annual from StartDate)
I need the code to recreate all payments dates (RentalDate) to date, showing value paid at each interval.
Output would look something like this:
eg.
ContractNumber = 12345
StartDate = 01/01/2013
RentaAmount = 100
RetalFrequency = Quarterly
ContractNumber RentalDate RentalAmount
12345 01/01/2013 100
12345 01/04/2013 100
12345 01/07/2013 100
12345 01/10/2013 100
12345 01/01/2014 100
12345 01/04/2014 100
12345 01/07/2014 100
Any help would be appreciated,
THANKS!!
Do you want an EndDate or NumberOfPeriods?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2014 at 2:28 pm
Lutz, i will try to follow your steps in details tomorrow...
Jeff, i don't need an EndDate and rather than a total NumberOfPeriods i would need to see the period details (i.e. RentalDate):
at present i have 1 line (ContractNumber,StartDate,RentaAmount,RetalFrequency)
i need to get to the 7 lines in my example above (ContractNumber, RentalDate, RentalAmount)
for each ContractNumber i always know the StartDate and need to recreate each RentalDate between StartDate and GetDate() based on RentalFrequency.
so the example above would output 7 records, i.e. 7 quarters between StartDate and GetDate()
if the RentalFrequency was 'monthly' then the query should have returned 20 records, i.e. 20 monthly payment from StartDate '01/01/2013' to GetDate() '03/08/2014'
thanks guys
paul
August 3, 2014 at 6:01 pm
I am taking a guess, but something like this:
-- Setup sample table a and data.
create table #TestData(
ContractNumber int,
StartDate date,
RentalAmount int,
RentalFrequency varchar(10)
);
insert into #TestData
values (12345,'2013-01-01',100,'Quarterly'),
(23456,'2014-02-01',125,'Monthly'),
(34567,'2014-02-01',135,'Quarterly'),
(23456,'2014-02-01',150,'Annual');
with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eTally(n) as (select row_number() over (order by (select null)) from e4 a cross join e2 b)
select
td.ContractNumber,
rd.RentalDate,
td.RentalAmount
from
#TestData td
cross apply (select top (datediff(month, td.StartDate, getdate()) / case td.RentalFrequency when 'Monthly' then 1 when 'Quarterly' then 3 when 'Annual' then 12 end + 1)
dateadd(month, (n - 1) * case td.RentalFrequency when 'Monthly' then 1 when 'Quarterly' then 3 when 'Annual' then 12 end, td.StartDate)
from
eTally) rd(RentalDate);
-- drop test data
drop table #TestData;
August 3, 2014 at 8:25 pm
Lynn's code is excellent for this problem.
I've modularized it a bit to make it reusable depending on your needs and have added a couple of extra columns to the output, which don't need to be selected if not needed. Here we go...
P74 (8/3/2014)
Lutz, i will try to follow your steps in details tomorrow...Jeff, i don't need an EndDate and rather than a total NumberOfPeriods i would need to see the period details (i.e. RentalDate):
at present i have 1 line (ContractNumber,StartDate,RentaAmount,RetalFrequency)
i need to get to the 7 lines in my example above (ContractNumber, RentalDate, RentalAmount)
for each ContractNumber i always know the StartDate and need to recreate each RentalDate between StartDate and GetDate() based on RentalFrequency.
so the example above would output 7 records, i.e. 7 quarters between StartDate and GetDate()
if the RentalFrequency was 'monthly' then the query should have returned 20 records, i.e. 20 monthly payment from StartDate '01/01/2013' to GetDate() '03/08/2014'
thanks guys
paul
Apologies. It was there but I missed that in the original post. The "EndDate" is today. Thanks for pointing that out.
First things first. We're getting ready to "count" so we need something to count with. WHILE loops and recursive CTEs are terrible for such things even with such a small number of rows. With that in mind, here's a function that will count from either 1 or 0 up to a desired number not to exceed 1 Billion. Run this code in the database that you'll be working out of...
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Billion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works. http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10E1 or 10 rows
, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or 1 Thousand rows
, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or 1 Billion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9 -- Values from 1 to @MaxN
;
Once that's done, things get kind of simple. The following code creates a high performance, inline Table Valued Function to do what you've asked.
CREATE FUNCTION dbo.GetPreviousRentalDates
(
@pContractNumber INT
,@pStartDate DATETIME
,@pRentalAmount DECIMAL(9,2)
,@pRentalFrequency VARCHAR(20)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN (
WITH ctePeriodCount AS
(--==== Get a count of period boundaries cross from ctePeriodCount to right now.
-- It could be one more than what we need but we'll take care of that later.
SELECT PeriodCount = CASE
WHEN @pRentalFrequency = 'Annual' THEN DATEDIFF(yy,@pStartDate,GETDATE())
WHEN @pRentalFrequency = 'Quarterly' THEN DATEDIFF(qq,@pStartDate,GETDATE())
WHEN @pRentalFrequency = 'Monthly' THEN DATEDIFF(mm,@pStartDate,GETDATE())
END
)
,
cteExpandDates AS
(--==== Expand the dates using a "zero base" count based on the desired period.
SELECT RentalDate = CASE
WHEN @pRentalFrequency = 'Annual' THEN DATEADD(yy,t.N,@pStartDate)
WHEN @pRentalFrequency = 'Quarterly' THEN DATEADD(qq,t.N,@pStartDate)
WHEN @pRentalFrequency = 'Monthly' THEN DATEADD(mm,t.N,@pStartDate)
END
FROM ctePeriodCount
CROSS APPLY dbo.fnTally(0,PeriodCount)t
)
--===== Create the final return with a date restriction to make sure we don't go past today.
SELECT PeriodNumber = CAST(ROW_NUMBER() OVER (ORDER BY RentalDate) AS INT)
,ContractNumber = @pContractNumber
,RentalDate = RentalDate
,RentalAmount = @pRentalAmount
,RentalFrequency = @pRentalFrequency
FROM cteExpandDates
WHERE RentalDate <= GETDATE()
)
;
That being done, you can use the function for a one-off query...
SELECT *
FROM dbo.GetPreviousRentalDates(12345,'01/01/2013',100,'Quarterly')
;
Results:
PeriodNumber ContractNumber RentalDate RentalAmount RentalFrequency
------------ -------------- ----------------------- --------------------------------------- --------------------
1 12345 2013-01-01 00:00:00.000 100.00 Quarterly
2 12345 2013-04-01 00:00:00.000 100.00 Quarterly
3 12345 2013-07-01 00:00:00.000 100.00 Quarterly
4 12345 2013-10-01 00:00:00.000 100.00 Quarterly
5 12345 2014-01-01 00:00:00.000 100.00 Quarterly
6 12345 2014-04-01 00:00:00.000 100.00 Quarterly
7 12345 2014-07-01 00:00:00.000 100.00 Quarterly
(7 row(s) affected)
... or you can play it against a whole table..
--===== If the test table already exists, drop it to make reruns easier in SSMS
-- This is NOT a part of the solution. We're just building a test table here.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
-- This is NOT a part of the solution. We're just building test data here.
SELECT td.ContractNumber
,StartDate = CAST(td.StartDate AS DATETIME)
,RentalAmount
,RentalFrequency
INTO #Testtable
FROM (--==== Test data
SELECT 12345,'2013-01-01',1200,'Annual' UNION ALL
SELECT 54321,'2013-01-01', 100,'Quarterly' UNION ALL
SELECT 24680,'2013-01-01', 100,'Monthly'
)td(ContractNumber,StartDate,RentalAmount,RentalFrequency)
;
--===== Demonstrate the function played against the test table
SELECT fn.*
FROM #TestTable tt
CROSS APPLY dbo.GetPreviousRentalDates(tt.ContractNumber, tt.StartDate, tt.RentalAmount, tt.RentalFrequency) fn
;
Results:
PeriodNumber ContractNumber RentalDate RentalAmount RentalFrequency
------------ -------------- ----------------------- --------------------------------------- --------------------
1 12345 2013-01-01 00:00:00.000 1200.00 Annual
2 12345 2014-01-01 00:00:00.000 1200.00 Annual
1 54321 2013-01-01 00:00:00.000 100.00 Quarterly
2 54321 2013-04-01 00:00:00.000 100.00 Quarterly
3 54321 2013-07-01 00:00:00.000 100.00 Quarterly
4 54321 2013-10-01 00:00:00.000 100.00 Quarterly
5 54321 2014-01-01 00:00:00.000 100.00 Quarterly
6 54321 2014-04-01 00:00:00.000 100.00 Quarterly
7 54321 2014-07-01 00:00:00.000 100.00 Quarterly
1 24680 2013-01-01 00:00:00.000 100.00 Monthly
2 24680 2013-02-01 00:00:00.000 100.00 Monthly
3 24680 2013-03-01 00:00:00.000 100.00 Monthly
4 24680 2013-04-01 00:00:00.000 100.00 Monthly
5 24680 2013-05-01 00:00:00.000 100.00 Monthly
6 24680 2013-06-01 00:00:00.000 100.00 Monthly
7 24680 2013-07-01 00:00:00.000 100.00 Monthly
8 24680 2013-08-01 00:00:00.000 100.00 Monthly
9 24680 2013-09-01 00:00:00.000 100.00 Monthly
10 24680 2013-10-01 00:00:00.000 100.00 Monthly
11 24680 2013-11-01 00:00:00.000 100.00 Monthly
12 24680 2013-12-01 00:00:00.000 100.00 Monthly
13 24680 2014-01-01 00:00:00.000 100.00 Monthly
14 24680 2014-02-01 00:00:00.000 100.00 Monthly
15 24680 2014-03-01 00:00:00.000 100.00 Monthly
16 24680 2014-04-01 00:00:00.000 100.00 Monthly
17 24680 2014-05-01 00:00:00.000 100.00 Monthly
18 24680 2014-06-01 00:00:00.000 100.00 Monthly
19 24680 2014-07-01 00:00:00.000 100.00 Monthly
20 24680 2014-08-01 00:00:00.000 100.00 Monthly
(29 row(s) affected)
to summarize it all, the number of periods required is determined, the fnTally provides numbers from 0 to those numbers of periods and is CROSS JOIN with the single rows in the test table to produce the dates using DATEADD. Both Lynn's and my solution work just like a WHILE loop except we both use a trick that takes advantage of the looping that every SELECT has behind the scenes. That looping effect is known to some here on SSC as a "Pseudo Cursor", a phrase for the phenomenon first coined by R. Barry Young and taken full advantage of in the cascading CTEs that are credited to Itzik Ben-Gan.
If you have any questions on any of this, please don't hesitate to ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2014 at 11:29 am
Wow! thanks for the help Lynn, Jeff.
just going through the code now... what is the use of (n) after the cte name or after a table name?
August 5, 2014 at 11:41 am
P74 (8/5/2014)
Wow! thanks for the help Lynn, Jeff.just going through the code now... what is the use of (n) after the cte name or after a table name?
e1(n) << the n here is the column name. Read about CTEs is Books Online.
#TestData td << td is a table alias for the table #TestData. Again, read about this in Books Online as well.
August 5, 2014 at 3:10 pm
Hi Lynn, I do use CTEs, but i've never specified the column names in that way... i just normally go "with myCTE as (select col1,col2 from tab1)". is this bad practice?
thanks again guys for the tips, i found these two articles very interesting:
http://www.sqlservercentral.com/articles/T-SQL/74118/
http://www.sqlservercentral.com/articles/T-SQL/62867/
cheers
😀
August 5, 2014 at 3:22 pm
P74 (8/5/2014)
Hi Lynn, I do use CTEs, but i've never specified the column names in that way... i just normally go "with myCTE as (select col1,col2 from tab1)". is this bad practice?thanks again guys for the tips, i found these two articles very interesting:
http://www.sqlservercentral.com/articles/T-SQL/74118/
http://www.sqlservercentral.com/articles/T-SQL/62867/
cheers
😀
Just to be sure, that first article is about the performance pitfalls of "Recursive CTEs" (rCTE for short). Neither Lynn or I have used rCTEs. Instead, we've used versions of Itzik Ben-Gan's "Cascading CTEs", which are quite fast and resource efficient. In fact, they produce exactly ZERO reads by themselves.
As for your question, your method of writing CTEs is just fine. Lynn and I wanted to create a column name for the constant (1) and for the final ROW_NUMBER in the CTEs. We could have done that in the SELECTs inside the CTE or like we did outside the CTE. In this case, we did it just for easier, more consistent formatting because we only need a single column alias. There is no "best practice" in this particular area. Rather, "It Depends" comes into play for many reasons.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2014 at 4:45 pm
Hi Jeff, yes to confirm the first link was me searching in SSC for "Pseudo Cursor" and that came back with a very easy to understand definition...
thanks
August 5, 2014 at 8:46 pm
Very cool. Glad to see someone with so much intellectual curiosity. Well done!
Also, thanks for the feeeddback on what you were looking for. I sometimes wonder if anyone looks up such terms.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply