Creating records in a SQL Table

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 )

Select allOpen in new window

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.

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

And FYI,  this needs to run in SQL 2005.

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

Select allOpen in new window

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

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

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.

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.

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 )

Select allOpen in new window

which for you input table gives:Showing-Monthly-Depreciation-Calcula.sql

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

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

Select allOpen in new window

Thanks,

I will try that tomorrow morning.  Pushing a test database to the cloud at the moment.