Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Loop or similar solution Expand / Collapse
Author
Message
Posted Sunday, August 3, 2014 3:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:57 AM
Points: 12, Visits: 47
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!!
Post #1599010
Posted Sunday, August 3, 2014 3:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:35 PM
Points: 6,829, Visits: 13,291
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




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1599012
Posted Sunday, August 3, 2014 1:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:54 PM
Points: 35,266, Visits: 31,758
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1599072
Posted Sunday, August 3, 2014 2:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:57 AM
Points: 12, Visits: 47
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
Post #1599075
Posted Sunday, August 3, 2014 6:01 PM This worked for the OP Answer marked as solution


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1599085
Posted Sunday, August 3, 2014 8:25 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:54 PM
Points: 35,266, Visits: 31,758
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1599104
Posted Tuesday, August 5, 2014 11:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:57 AM
Points: 12, Visits: 47
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?
Post #1599869
Posted Tuesday, August 5, 2014 11:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1599871
Posted Tuesday, August 5, 2014 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:57 AM
Points: 12, Visits: 47
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
Post #1599943
Posted Tuesday, August 5, 2014 3:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:54 PM
Points: 35,266, Visits: 31,758
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1599949
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse