December 2, 2020 at 6:29 pm
was hoping to get some help as it appears I'm not close. Trying to get all year/mo combinations even when there's no data...based on year/mo, Site, Dept and Cat. Appreciate any help!
create table #Date_Dim
(Date date
)
Insert into #Date_Dim
values
('2020-01-01')
,('2020-01-02')
,('2020-01-03')
,('2020-01-04')
,('2020-02-01')
,('2020-02-02')
,('2020-02-03')
,('2020-02-04')
create table #Data
(Date date
,Site varchar(30)
,Dept varchar(30)
,Cat varchar(10)
,Num int
)
Insert into #Data
values
('2020-01-01', 'Salem', 'Inp', 'A', 2)
,('2020-01-01', 'Salem', 'Inp', 'B', 2)
,('2020-02-04', 'Hillsboro', 'Inp', 'A', 4)
--Main Query
select
DATEADD(m, DATEDIFF(m, 0, dd.Date), 0) as 'Month'
,d.Site, d.Dept, d.Cat, sum(d.Num) as 'Sum'
from #Date_Dim dd
left join #Data d on DATEADD(m, DATEDIFF(m, 0, dd.Date), 0)=DATEADD(m, DATEDIFF(m, 0, d.Date), 0)
group by DATEADD(m, DATEDIFF(m, 0, dd.Date), 0), d.Site, d.Dept, d.Cat
order by DATEADD(m, DATEDIFF(m, 0, dd.Date), 0)
--Attempted Results
--('2020-01-01', 'Salem', 'Inp', 'A', 2)
--('2020-01-01', 'Salem', 'Inp', 'B', 2)
--('2020-01-01', 'Hillsboro', 'Inp', 'A', 0)
--('2020-02-01', 'Salem', 'Inp', 'A', 0)
--('2020-02-01', 'Salem', 'Inp', 'B', 0)
--('2020-02-01', 'Hillsboro', 'Inp', 'A', 4)
--drop table #Data
--drop table #Date_Dim
December 2, 2020 at 11:34 pm
Not sure... something like this?
SELECT YEAR(t.Date) AS yr
, MONTH(t.Date) AS mo
, d.Site
, d.Dept
, d.Cat
, SUM(d.Num) AS Total
FROM #date_dim t
LEFT JOIN #Data d
ON t.Date = d.Date
GROUP BY YEAR(t.Date)
, MONTH(t.Date)
, d.Site
, d.Dept
, d.Cat;
Might be easier with a full Calendar table.
December 3, 2020 at 2:56 am
When "trying to get all combinations..." it usually means CROSS JOIN. In this case the distinct year/month dates are selected in the CTE. Then the CTE is 'row multiplied' or CROSS JOIN'ed to the rows in the #Data table. When the year/month date of the CTE (distinct list) is not equal to the year/month date of the #Data table then the 'Num' column is set to zero. Something like this
with unq_yr_mo_cte(yr_month) as (
select distinct cast(dateadd(m, datediff(m, 0, [date]), 0) as date)
from #Date_Dim)
select ym.yr_month, d.[Site], d.Dept, d.Cat,
case when ym.yr_month=dateadd(m, datediff(m, 0, d.[date]), 0)
then d.Num else 0 end as Num
from unq_yr_mo_cte ym
cross join #Data d
order by ym.yr_month;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply