Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Loop or similar solution Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, August 3, 2014 3:24 AM
 Grasshopper 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 ContractNumberI 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 = 12345StartDate = 01/01/2013RentaAmount = 100RetalFrequency = QuarterlyContractNumber RentalDate RentalAmount12345 01/01/2013 10012345 01/04/2013 10012345 01/07/2013 10012345 01/10/2013 10012345 01/01/2014 10012345 01/04/2014 10012345 01/07/2014 100Any help would be appreciated,THANKS!!
Post #1599010
 Posted Sunday, August 3, 2014 3:29 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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 asSELECT columns_neededFROM unknownWHERE col1=criteria1 AND col2 = criteria2 AND col3 > criteria3 LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1599012
 Posted Sunday, August 3, 2014 1:37 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 11:17 AM Points: 42,046, Visits: 39,424
 P74 (8/3/2014)Hi,I need help with some code!Scenario:I know the ContractNumberI 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 = 12345StartDate = 01/01/2013RentaAmount = 100RetalFrequency = QuarterlyContractNumber RentalDate RentalAmount12345 01/01/2013 10012345 01/04/2013 10012345 01/07/2013 10012345 01/10/2013 10012345 01/01/2014 10012345 01/04/2014 10012345 01/07/2014 100Any 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1599072
 Posted Sunday, August 3, 2014 2:28 PM
 Grasshopper 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 guyspaul
Post #1599075
 Posted Sunday, August 3, 2014 6:01 PM This worked for the OP
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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 #TestDatavalues (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.RentalAmountfrom #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 datadrop table #TestData;`
Post #1599085
 Posted Sunday, August 3, 2014 8:25 PM This worked for the OP
 SSC-Forever Group: General Forum Members Last Login: Today @ 11:17 AM Points: 42,046, Visits: 39,424
 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 guyspaulApologies. 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 Quarterly2 12345 2013-04-01 00:00:00.000 100.00 Quarterly3 12345 2013-07-01 00:00:00.000 100.00 Quarterly4 12345 2013-10-01 00:00:00.000 100.00 Quarterly5 12345 2014-01-01 00:00:00.000 100.00 Quarterly6 12345 2014-04-01 00:00:00.000 100.00 Quarterly7 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 Annual2 12345 2014-01-01 00:00:00.000 1200.00 Annual1 54321 2013-01-01 00:00:00.000 100.00 Quarterly2 54321 2013-04-01 00:00:00.000 100.00 Quarterly3 54321 2013-07-01 00:00:00.000 100.00 Quarterly4 54321 2013-10-01 00:00:00.000 100.00 Quarterly5 54321 2014-01-01 00:00:00.000 100.00 Quarterly6 54321 2014-04-01 00:00:00.000 100.00 Quarterly7 54321 2014-07-01 00:00:00.000 100.00 Quarterly1 24680 2013-01-01 00:00:00.000 100.00 Monthly2 24680 2013-02-01 00:00:00.000 100.00 Monthly3 24680 2013-03-01 00:00:00.000 100.00 Monthly4 24680 2013-04-01 00:00:00.000 100.00 Monthly5 24680 2013-05-01 00:00:00.000 100.00 Monthly6 24680 2013-06-01 00:00:00.000 100.00 Monthly7 24680 2013-07-01 00:00:00.000 100.00 Monthly8 24680 2013-08-01 00:00:00.000 100.00 Monthly9 24680 2013-09-01 00:00:00.000 100.00 Monthly10 24680 2013-10-01 00:00:00.000 100.00 Monthly11 24680 2013-11-01 00:00:00.000 100.00 Monthly12 24680 2013-12-01 00:00:00.000 100.00 Monthly13 24680 2014-01-01 00:00:00.000 100.00 Monthly14 24680 2014-02-01 00:00:00.000 100.00 Monthly15 24680 2014-03-01 00:00:00.000 100.00 Monthly16 24680 2014-04-01 00:00:00.000 100.00 Monthly17 24680 2014-05-01 00:00:00.000 100.00 Monthly18 24680 2014-06-01 00:00:00.000 100.00 Monthly19 24680 2014-07-01 00:00:00.000 100.00 Monthly20 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1599104
 Posted Tuesday, August 5, 2014 11:29 AM
 Grasshopper 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 Group: General Forum Members Last Login: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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.
Post #1599871
 Posted Tuesday, August 5, 2014 3:10 PM
 Grasshopper 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-Forever Group: General Forum Members Last Login: Today @ 11:17 AM Points: 42,046, Visits: 39,424