Creating records in a SQL Table

I have a temp table with 2 date columns BEGDATE and ENDDATE.  I need to be able to create records, 1 for each month from the BEGDATE to the ENDDATE, using the end of each month date.  

So for a record with a BEGDATE of 10/09/2013 and an ENDDATE of 3/29/2014, I need records as follows

10/31/2013
11/20/2013
12/31/2013
01/31/2014
02/28/2014
03/31/2014

I can presently do this with a cursor, but with over 100000 records, you can imagine how SLOOOOOWWW it is.
Any thoughts on how to accomplish this?

I have attached my procedure code to assist in the process.

Thanks!
CALCMCR.sql
lburnsinmagnoliaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
Here is an attempt. Using EOMONTH (SQL 2012+) to get last day of the month.
WITH Nbrs ( n ) AS (
	--this generates a sequence of numbers for us
    SELECT 0 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 --up to 500 months??? 
), Dates(ID, BEGDATE, ENDDATE) as (
	--this is an example of the date table you describe
	select 1, '10/31/2013', '3/29/2014'
	union select 2, '02/26/2015', '08/15/2015'
)
SELECT Dates.*, n, EOMONTH ( DATEADD(month, n, Dates.BEGDATE) ) Dates --add the date sequence to the BEGDATE
FROM Dates, Nbrs
WHERE Nbrs.n <= DATEDIFF(month, Dates.BEGDATE, Dates.ENDDATE) --we want to know the datediff so that we can see how many months we need to fill in
OPTION ( MAXRECURSION 500 )

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Tell you what, how about explaining in simple English the logic behind the dates you are trying to populate, and perhaps we can suggest a faster answer.
lburnsinmagnoliaAuthor Commented:
I have a table called history which contains a date rented (begdate) and a terminated date (enddate).  The terminated date can be NULL which means the item is currently rented.  If it is null, then I use the @ASOFDATE (passed in as a parameter) as the enddate.  Now, with that table, I need to insert depreciation transactions (1 for each month) from the date rented until the end date or until the RBV reaches 0.  The amount of depreciation the item gets for each month is reduced to a daily amount multiplied by the days in each month.  So if you use the example I gave, the number of days in use for this particular rental was 171 days and the daily amount would be the cost/rbv divided by the days rented to get a daily amount.  Then take the daily amount multiplied the days in each month to get a monthly amount which is inserted into the transaction table.  Another complication is that the RBV (remaining book value) of each row in the transaction table must be pulled from the very table we are trying to create to get the starting point for each rental record in the history table.  Obviously, the first rental would be cost, but subsequent rentals would get the RBV column from the transaction table using the BEGDATE to retrieve the record.

If you need more info, let me know.

Thanks
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

lburnsinmagnoliaAuthor Commented:
And FYI,  this needs to run in SQL 2005.
Scott PletcherSenior DBACommented:
An in-line tally table -- a table of sequential numbers from 0 to, say, 1000 -- works great for this.  You join to the tally table to generate/simulate each separate month.  Since last day of month is rather tricky, I instead computed the first of the next month and subtracted one day.

;WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
    SELECT 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally100 c2
)
SELECT CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101) AS OUTPUT_DATE
FROM (
    SELECT CAST('20131009' AS date) AS BEGDATE, CAST('20140329' AS date) AS ENDDATE
) AS sample_data_from_your_table
INNER JOIN cteTally1000 months ON
    months.tally BETWEEN 1 AND DATEDIFF(MONTH, BEGDATE, ENDDATE) + 1
CROSS APPLY (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, BEGDATE), 0) AS BEGDATE_Day_1
) AS assign_alias_names
ORDER BY months.tally

Open in new window

MlandaTCommented:
Oh ok. Just replace the EOMONTH ( DATEADD(month, n, Dates.BEGDATE) ) there with


DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, DATEADD(month, n, Dates.BEGDATE) ) + 1, 0))

Am now on my phone :( Can't test this, but you basically use a formula to find the end of month. This gives you the results you expect
lburnsinmagnoliaAuthor Commented:
Scott,

I am trying to code your solution.  I am only getting the beginning date from the inner join/cross apply.  Using your CTE code followed by my select from the temp table, I only get one row per date instead of the entire sequence.

Here is the code I have.  Any thoughts?
--your cte is here, then the following

SELECT
      MH.STOREID                  
      , MH.ITEMID
      , CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)
      , @CALCMETHOD
      , ROUND(@DAILYDEPR * DATEDIFF(DD, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), DATEADD(S, -1, DATEADD(MM, DATEDIFF(MM, 0, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)) + 1,0))), 2)
      , DBO.FNC_GetRBV(@ITEMID, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), @CALCMETHOD) - ROUND(@DAILYDEPR * DATEDIFF(DD, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), DATEADD(S, -1, DATEADD(MM, DATEDIFF(MM, 0, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)) + 1,0))), 2)
      , MH.COST - DBO.FNC_GetRBV(@ITEMID, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), @CALCMETHOD) - ROUND(@DAILYDEPR * DATEDIFF(DD, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), DATEADD(S, -1, DATEADD(MM, DATEDIFF(MM, 0, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)) + 1,0))), 2)
      , 0
FROM #MCRHIST MH
INNER JOIN cteTally1000 months ON
    months.tally BETWEEN 1 AND DATEDIFF(MONTH, MH.BEGDATE, MH.ENDDATE) + 1
CROSS APPLY (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, MH.BEGDATE), 0) AS BEGDATE_Day_1
) AS assign_alias_names

WHERE DAYSINAGR <> 0
Scott PletcherSenior DBACommented:
Hmm, puzzling, the INNER JOIN on tally should generate the additional rows needed.  Verify that your cte code actually generates rows (I tried to post sample code but the site parser messes up and won't accept it).   Also, make sure BEGDATE is before ENDDATE and that ENDDATE and that neither is NULL.
lburnsinmagnoliaAuthor Commented:
The CTE is producing rows.  In my testing I am limiting my table to the first 100 rows and have made sure the begdate is <= enddate .

Here are the first 5 rows in my table

STOREID       ITEMID      BEGDATE      ENDDATE      RENTAL_PERIODS       NORMAL_METHOD      DAYSINAGR      RBV      COST      DAILYDEPR
1      1070635      2015-06-16 00:00:00.000      2015-07-31 00:00:00.000      78      W      546      79.72      79.72      0.15
1      1065373      2015-07-08 00:00:00.000      2015-07-31 00:00:00.000      39      B      546      169.95      169.95      0.31
1      1071091      2015-07-16 00:00:00.000      2015-08-22 00:00:00.000      78      W      37      107.37      107.37      2.90
4      4038356      2015-08-19 00:00:00.000      2015-07-31 00:00:00.000      78      W      546      84.59      84.59      0.15
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      78      W      235      45.07      45.07      0.19

Here are the first 5 rows from the select statement pulling from the temp table and CTE

STOREID       ITEMID      (No column name)
1      1070635      06/30/2015
1      1065373      07/31/2015
1      1071091      07/31/2015
5      500038575      10/31/2014
5      500036230      12/31/2014

Notice I only get one row per item where it should be one per month from beg to end.
Very strange indeed.
Scott PletcherSenior DBACommented:
Using a physical tally table, instead of an in-line one, I created a mock-up of the data and the rows duplicated per month as needed.  Something else must be going on, I'm just not sure what.


create table #mcrhist (
    storeid int,
    itemid int,
    begdate datetime,
    enddate datetime,
    rental_periods int,
    normal_method char(1),
    daysinagr int,
    rbv decimal(9, 2),
    cost decimal(9, 2),
    dailydepr decimal(9, 2)
    )
insert into #mcrhist
          select 1,      1070635      ,'2015-06-16 00:00:00.000',      '2015-07-31 00:00:00.000'    ,  78,      'W'    ,  546,      79.72    ,  79.72  ,    0.15
union all select 1 ,     1065373     , '2015-07-08 00:00:00.000' ,     '2015-07-31 00:00:00.000'   ,   39 ,     'B'   ,   546 ,     169.95  ,    169.95 ,     0.31
union all select 1  ,    1071091    ,  '2015-07-16 00:00:00.000'  ,    '2015-08-22 00:00:00.000'  ,    78  ,    'W'  ,    37   ,   107.37  ,    107.37   ,   2.90
union all select 4   ,   4038356   ,   '2015-08-19 00:00:00.000'   ,   '2015-07-31 00:00:00.000' ,     78   ,   'W' ,    546    ,  84.59  ,    84.59   ,   0.15
union all select 5    ,  500038575,    '2014-10-31 00:00:00.000'    ,  '2015-06-23 00:00:00.000',      78    ,  'W',      235    ,  45.07,      45.07   ,   0.19

declare @calcmethod int
declare @dailydepr decimal(9, 2)
declare @itemid int
set @calcmethod = 101
set @dailydepr = 0.0
set @itemid = 1

SELECT
      MH.STOREID                  
      , MH.ITEMID
      , MH.BEGDATE
      , MH.ENDDATE
      , CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)
      , @CALCMETHOD
      , ROUND(@DAILYDEPR * DATEDIFF(DD, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), DATEADD(S, -1, DATEADD(MM, DATEDIFF(MM, 0, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)) + 1,0))), 2)
      --, DBO.FNC_GetRBV(@ITEMID, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), @CALCMETHOD) - ROUND(@DAILYDEPR * DATEDIFF(DD, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), DATEADD(S, -1, DATEADD(MM, DATEDIFF(MM, 0, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)) + 1,0))), 2)
      --, MH.COST - DBO.FNC_GetRBV(@ITEMID, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), @CALCMETHOD) - ROUND(@DAILYDEPR * DATEDIFF(DD, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101), DATEADD(S, -1, DATEADD(MM, DATEDIFF(MM, 0, CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, months.tally, BEGDATE_Day_1)), 101)) + 1,0))), 2)
      , 0
FROM #MCRHIST MH
INNER JOIN dbo.tally months ON
    months.tally BETWEEN 1 AND DATEDIFF(MONTH, MH.BEGDATE, MH.ENDDATE) + 1
CROSS APPLY (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, MH.BEGDATE), 0) AS BEGDATE_Day_1
) AS assign_alias_names

WHERE DAYSINAGR <> 0

ORDER BY STOREID, BEGDATE


Output result set:
STOREID      ITEMID      BEGDATE      ENDDATE      (No column name)      (No column name)      (No column name)      (No column name)
1      1070635      2015-06-16 00:00:00.000      2015-07-31 00:00:00.000      06/30/2015      101      0.00      0
1      1070635      2015-06-16 00:00:00.000      2015-07-31 00:00:00.000      07/31/2015      101      0.00      0
1      1065373      2015-07-08 00:00:00.000      2015-07-31 00:00:00.000      07/31/2015      101      0.00      0
1      1071091      2015-07-16 00:00:00.000      2015-08-22 00:00:00.000      07/31/2015      101      0.00      0
1      1071091      2015-07-16 00:00:00.000      2015-08-22 00:00:00.000      08/31/2015      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      10/31/2014      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      11/30/2014      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      12/31/2014      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      01/31/2015      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      02/28/2015      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      03/31/2015      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      04/30/2015      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      05/31/2015      101      0.00      0
5      500038575      2014-10-31 00:00:00.000      2015-06-23 00:00:00.000      06/30/2015      101      0.00      0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MlandaTCommented:
This approach incorporates some of your other logic, like knowing NumberOfDaysInMonth, and handling NULL ENDDATEs with @ASOFDATE
DECLARE @ASOFDATE date = '01-01-2016';

WITH Nbrs ( n ) AS (
	--this generates a sequence of numbers for us
    SELECT 0 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 --up to 500 months??? 
), Data(STOREID,  ITEMID, BEGDATE, ENDDATE, RENTAL_PERIODS,  NORMAL_METHOD, DAYSINAGR, RBV, COST, DAILYDEPR) AS 
(
	select 1, 1070635, '2015-06-16 00:00:00.000', '2015-07-31 00:00:00.000', 78, 'W', 546, 79.72, 79.72, 0.15
	union select 1, 1065373, '2015-07-08 00:00:00.000', '2015-07-31 00:00:00.000', 39, 'B', 546, 169.95, 169.95, 0.31
	union select 1, 1071091, '2015-07-16 00:00:00.000', '2015-08-22 00:00:00.000', 78, 'W', 37, 107.37, 107.37, 2.90
	union select 4, 4038356, '2015-08-19 00:00:00.000', '2015-07-31 00:00:00.000', 78, 'W', 546, 84.59, 84.59, 0.15
	union select 5, 500038575, '2014-10-31 00:00:00.000', '2015-06-23 00:00:00.000', 78, 'W', 235, 45.07, 45.07, 0.19
	union select 99, 99999, '2015-08-31 00:00:00.000', null, 78, 'W', 235, 45.07, 45.07, 0.19
)
SELECT 
	Data.*, n, 
	DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, DATEADD(month, n, Data.BEGDATE) ) + 1, 0)) LastDayOfMonth, --add the date sequence to the BEGDATE
	/*
		to calculate RBV, you want to know the number of days in the month
		special cases are the BEGDATE (n=0) and ENDDATE/@ASOFDATE (n=number of months between begdate and enddate) months
	*/
	DATEDIFF(day, 
		CASE 
			WHEN n = 0 THEN Data.BEGDATE 
			ELSE /*first day of month*/ DATEADD(m, DATEDIFF(m, 0, DATEADD(month, n-1, Data.BEGDATE) ) + 1, 0) 
		END , 
		CASE
			WHEN n = DATEDIFF(month, Data.BEGDATE, ISNULL(Data.ENDDATE,@ASOFDATE)) THEN ISNULL(Data.ENDDATE,@ASOFDATE)
			ELSE /*last day of the month*/ DATEADD(d, 0, DATEADD(m, DATEDIFF(m, 0, DATEADD(month, n, Data.BEGDATE) ) + 1, 0))
		END
	) DaysInMonth
FROM Data, Nbrs
WHERE Nbrs.n <= DATEDIFF(month, Data.BEGDATE, ISNULL(Data.ENDDATE,@ASOFDATE)) --we want to know the datediff so that we can see how many months we need to fill in
ORDER BY STOREID,  ITEMID
OPTION ( MAXRECURSION 500 )

Open in new window

which for you input table gives:Example output. I created a dummy product with no END date, for you to see the handling of @ASOFDATEShowing-Monthly-Depreciation-Calcula.sql
lburnsinmagnoliaAuthor Commented:
Scott,

Can you show me how you made the tally table a physical one.  And will I need to truncate it on each run and re-create?

Thanks
Scott PletcherSenior DBACommented:
Sure.  Code below will create a tally table with row values from 0 to 1M.  No, you only have to create the tally table once, you never need to touch it after that (unless you need more than 1,000,000 rows in it at some point!?).

CREATE TABLE dbo.tally (
    tally int NOT NULL,
    CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( tally )
    )
INSERT INTO dbo.tally VALUES(0);

WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
    SELECT 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
INSERT INTO dbo.tally
SELECT tally
FROM cteTally1Mil
ORDER BY tally

Open in new window

lburnsinmagnoliaAuthor Commented:
Thanks,

I will try that tomorrow morning.  Pushing a test database to the cloud at the moment.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.