Dividing a month-department budget into day-department budget

Transact-SQL
1

Hi ,

I have to divide a table that Financial Department gave to me , it is table with three columns:

DEPT MONTH BUDGET
A 1 50.000€
B 2 70.000€
C 3 30.000€
D 4 20.000€

I have to cross it with the DATE table in order to make a daily table:

DEPT MONTH DAY BUDGET
A 1 1 (50.000/31 days of JAN)
A 1 2 (50.000/31 days of JAN)
A 1 3 (50.000/31 days of JAN)
A 1 4 (50.000/31 days of JAN)
....

And so on ...

Anyone knows how to convert or divide a table into a daily table?

Thanks in advance my friends.

2

Please post DDL in the form of create table or declare a table variable. Include inserts of sample data, what you have tried so far and the results you want..

3

DDL OF CREATION

CREATE TABLE [LK_BUDGET_DEPT_MONTH](
[MONTH] varchar NULL,
[ID_DATE] varchar NULL,
[BUDGET] [decimal](18, 2) NULL,
[ID_DEPT] varchar NULL,
) ON [PRIMARY]

4

I have tried several things but no one with success , what i want is to divide into days the budget of each department.

The financial department gave me the table aggregated by department and month and i am trying to divide it into department and day in order to control the budget per day.

Regards,

Sergio

5

Easiest is to use a tally table, which is just a table of sequential numbers. I've used a CTE here to create an in-line tally table, but naturally you could create a separate physical table instead. This code doesn't adjust for a possible leap year for Feb -- you'll have to add that part yourself :slight_smile:

;WITH
    cteTally10 AS (
        SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cteTally100 AS (
        SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
        FROM cteTally10 c1
        CROSS JOIN cteTally10 c2
    )
    SELECT lb.DEPT, lb.MONTH, days.number AS DAY, 
        CAST(lb.BUDGET / days.number AS decimal(9, 3)) AS BUDGET
    FROM dbo.[LK_BUDGET_DEPT_MONTH] lb
    INNER JOIN cteTally100 days ON days.number BETWEEN 1 AND 
        DATEDIFF(DAY, DATEADD(MONTH, month - 1, 0), DATEADD(MONTH, month, 0))
    ORDER BY lb.MONTH, days.number
6 
DECLARE @LK_BUDGET_DEPT_MONTH TABLE (
  [ID_DEPT] [varchar](255) NULL
, [BUDGET] [decimal](18, 2) NULL
, [MONTH] date NULL) ;
INSERT @LK_BUDGET_DEPT_MONTH(ID_DEPT, [MONTH], BUDGET)
VALUES ('A','20170101' ,50.00 )
     , ('B','20170201' ,70.00 )
     , ('C','20170301' ,30.00 )
     , ('D','20170401' ,20.00 );
SELECT
	*, DailyBudget= BUDGET/Day(EOMONTH([MONTH])), DaysInMonth=Day(EOMONTH([MONTH]))
FROM @LK_BUDGET_DEPT_MONTH;
7

Perhaps:

if object_id('tempdb..#M') is not null  drop table #M

create table #M
(
dept char(1),
[month] int,
budget money
)

insert into #M
values
('A', 1, 50.000),
('B', 2, 70.000),
('C', 3, 30.000),
('D', 4 ,20.000);

with T(dept, [month], budget, dayInMnth)
as
(
select * 
,day(dateadd(day,-1,cast('1900-' + cast([month] + 1 as char(2)) + '-1' as date))) 

from #M
)

,TR(dept, [month], [day], dayInMnth, budget)
as
(
select a.dept, a.[month], 1, a.dayInMnth, a.budget/a.dayInMnth  from T a
union all
select  a.dept, a.[month], b.day + 1, a.dayInMnth, a.budget/a.dayInMnth  
from T a
,TR b

where b.day + 1 <= a.dayInMnth
and b.dept = a.dept
)

select  * from TR
order by dept