DateDiff for each month

Question

My current query give my this as a result;

Address        PK    StartDate    EndDate    Rent   Cost       NoDays 
1 water lane   3435  01/04/2018  12/02/2020  500    11210.95   682
7 get road     5456  14/06/2019  01/02/2020  700    5339.18    232

I want to outline how many days per month/ or how much per month spent. this is what i want to see after NoDays or even on a new query result.

04/2018  05/2018  06/2018  07/2018 so on ....
30        31        30       31    so on ....
0         0          0        0    so on ....

or

04/2018  05/2018  06/2018  07/2018 so on ....
500        500        500      500    so on ....
0            0        0        0      so on ....

I suggest implementing a calendar table. Then, honestly, I suggest binning the idea of doing this in columns and do it in rows; not unless you want to use dynamic SQL. Without offence, but considering that you're asking how to get the number of days per month between the 2 dates, I would suggest that a dynamic SQL statement is beyond your current skill set; thus you shouldn't really be using it. — Thom A, Mar 10 '20 at 14:34
can you point me to the right path on both ways i dont mind leaning new things or give me a example. — , Mar 10 '20 at 14:37
Here is a good example of generating date range. --> https://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges As suggested by @Larnu, I would not recommend to get values in rows instead of columns — Digvijay S, Mar 10 '20 at 14:37
Here's an article on [Calendar Tables](https://www.sqlservercentral.com/steps/bones-of-sql-the-calendar-table). — Thom A, Mar 10 '20 at 14:38
Dont think this works with what i going to build (hopefully build) I want. i am trying to calculate the money i have spent per month per unit . for example i have tenancy 1 made on 01/04/2018 till 12/02/2020 which is 682 days the rent is 500 a month so my overall spend on this is 6279.45 i want to know how much per month for all my 50 different tenancies. maybe something like a pivot table then ?? — , Mar 10 '20 at 14:50
The SQL language **strictly requires** the number and types of column in a result set be known and fixed at query compile time, _before_ looking at any data. Even "SELECT * " queries meet this rule, because the number of columns meant by the * is static. If you must determine the number of columns based on data, you **must** run the query in three steps: **1)** Run a query to get data for the column listings you need. **2)** Build a new dynamic SQL statement based on results from 1. **3)** Run the query built in step 2. — Joel Coehoorn, Mar 10 '20 at 14:58
Really, the best option here is almost always returning more rows, with the data for the column headers rolled up into a single named column, and then **pivot the results in the client program or reporting tool.** — Joel Coehoorn, Mar 10 '20 at 15:00
@JD_123 can you please share how the input table looks like? and we'll get a solution for you :) — Mohamed Azizi, Mar 10 '20 at 15:07
*"I am trying to calculate the money i have spent per month per unit ."* This sounds like you're moving the goal posts. You should really be giving us *all** the information here. — Thom A, Mar 10 '20 at 15:10

Kemal AL GAZZAH · Accepted Answer · 2020-03-10T17:34:27.947

Here a solution which generates a calendar using recursion then adding columns and updating them using cursor and dynamic script

set dateformat dmy

declare @table as table(pk int, startdate date,enddate date,rent int,cost float)
insert into @table values(3435,'01/04/2018','12/02/2020',500,11210.95),(5456,'14/06/2019','01/02/2020',700,5339.18)


declare @table2 as table(pk int)
insert into @table2 select distinct(pk) from @table


declare @calendar as table (date date)
declare @mindate as date
declare @maxdate as date
select @mindate=min(startdate) from @table
select @maxdate=max(enddate) from @table;

with cte as(select @mindate as mydate union all select dateadd(day,1,mydate) from cte
where mydate < @maxdate)
insert into @calendar select * from cte
option(maxrecursion 0);

declare @tabresultsrows as table(pk int,MO varchar(7),N int,M int,Y int);

declare @tabmonths as table(Mo varchar(7),M int,Y int);

with cal as(
select t2.pk,c.date ,t.startdate,t.enddate ,month(date) M, year(date) y ,concat(RIGHT('00' + CONVERT(NVARCHAR(2), month(date)), 2),'/', year(date)) Mo,
case when c.date >= t.startdate and c.date <=t.enddate then 1 else 0 end N from @calendar c 
cross join @table2 t2 
inner join @table t on t2.pk=t.pk),
caltab as(select pk,Mo,sum(N) N ,Y,M from cal group by pk,Y,M,Mo )
insert into @tabresultsrows select pk,MO,N,M,Y from caltab order by pk,Y,M

insert into @tabmonths select distinct(MO),M,Y from @tabresultsrows

IF OBJECT_ID('tempdb..#tabresultscolumns') IS NOT NULL DROP TABLE #tabresultscolumns

select * into #tabresultscolumns from @table


declare @script as varchar(max)
declare mycursor  cursor for select mo from @tabmonths order by Y,M
declare @mo as varchar(7)
open mycursor
fetch mycursor into @mo
while @@fetch_status=0
begin
set @script='alter table #tabresultscolumns add  ['+@mo+'] int'
 print @script
 exec(@script)
 fetch mycursor into @mo
end
close mycursor
deallocate mycursor

declare secondcursor  cursor for select pk,Mo,N from @tabresultsrows 
declare @PK AS INT
declare @n as int
open secondcursor
fetch secondcursor into @pk,@mo,@n
while @@fetch_status=0
begin
set @script=concat('update #tabresultscolumns set ['+@mo+']=',@n,' where pk=',@pk )
print @script
exec(@script)
fetch secondcursor into @pk,@mo,@n
end
close secondcursor
deallocate secondcursor



select * from #tabresultscolumns

score 0 · Answer 2 · answered Mar 11 '20 at 16:43

Try something like this:

-- You can set the variables to get as an input the StartDate, EndDate from your table

    DECLARE @StartDate DATE = '20200101'
          , @EndDate DATE = '20200331'


    ;with datecreator as (
    SELECT  DATEADD(DAY, nbr - 1, @StartDate) as dates
    FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.PK ) AS Nbr
              FROM      Test c
            ) nbrs
    WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
    )

    ,CTE AS
    (
    select distinct Month(dates) rnk,Convert(char(3), dates, 0) MM from datecreator
    )

    ,CTE3 AS
    (
           SELECT T.* , rnk 
           FROM Test T INNER JOIN CTE c ON C.Mm = T.Months
    )

    ,CTE4 AS
    (
           SELECT Years,[1] Jan ,[2] Feb ,[3] Mar FROM CTE3
           PIVOT 
           (SUM(Rent) FOR rnk IN ([1],[2],[3])) p
    ) 

    SELECT Years , SUM(Jan) Jan , SUM(Feb) Feb , SUM(Mar) Mar FROM CTE4 GROUP  BY Years

You'll get the result :

Year1   Jan     Feb     Mar
2013    3000    3000    3000
2014    3500    3500    3500

As you can see above I used only the first Quarter (First 3 Months) but you can use the whole year.

Please let me know if you have any questions/Feedbacks :)

DateDiff for each month

2 Answers2