Hey Guys,
Looking for some help.....I have a script that currently provides daily data for the past 3 years. What I need is the query modified to provide only 15 days of daily data, 13 Weeks of weekly data, 13 months of monthly data, 5 quarters worth of quarterly data, and all (program inception) years of yearly data. The data would be all in 1 dataset with and added column to show the TimePeriod (Daily, Weekly, Monthly, etc). I've attached a screenshot of the current output fields, with an added field (TimePeriod) that I'm envisioning. I've done this in the past with unions, but this script is more complex and I'm not sure how to modify it for what I need.
WITH SiteTrafficCTE AS(
SELECT
t.TrafficActivityDateKey
, t.Customer_SK
, l.Site_ID
, l.LocationDescription
, COUNT(DISTINCT(FLOOR( TrafficActivityTimeKey / 3600 ))) AS ActiveHours
, SUM(t.RecordedTraffic) AS RecordedTraffic
FROM (
SELECT
TrafficActivityDateKey
, TrafficActivityTimeKey
, Customer_SK
, LaneLocation_SK
, SUM(totaltrafficactivity) AS RecordedTraffic
FROM FactTrafficSnapshot
WHERE TrafficActivityDateKey >= ( SELECT datekey
FROM DimDate
WHERE date = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -3, 0)
)
GROUP BY
TrafficActivityDateKey
, TrafficActivityTimeKey
, Customer_SK
, LaneLocation_SK
) t
JOIN DimLaneLocation l ON l.LaneLocation_SK = t.LaneLocation_SK
GROUP BY
t.TrafficActivityDateKey
, t.Customer_SK
, l.Site_ID
, l.LocationDescription
),
SiteIDEnforcementBridgeCTE AS(
SELECT DISTINCT
Customer_SK
, l.Site_ID
, e.EnforcementSystem
, e.EnforcementSystem_SK
, MAX(ViolationType_SK) AS ViolationType_SK
, MIN(bl.EnforcementStartDate) AS EnforcementStartDate
, MAX(bl.EnforcementEndDate) AS EnforcementEndDate
FROM BridgeEnforcementLaneLocation be
JOIN DimLaneLocation l ON l.LaneLocation_SK = be.LaneLocation_SK
LEFT JOIN DimEnforcementSystem e ON e.EnforcementSystem_SK = be.EnforcementSystem_SK
JOIN BridgeLaneLocationViolationType bl ON bl.LaneLocation_SK = be.LaneLocation_SK
WHERE EnforcementSystem <> 'Undetermined'
GROUP BY
Customer_SK
, l.Site_ID
, e.EnforcementSystem
, e.EnforcementSystem_SK
),
SystemsCTE AS(
SELECT DISTINCT
be.Customer_SK
, es.EnforcementSystem
FROM DimEnforcementSystem es
LEFT JOIN BridgeEnforcementLaneLocation be ON be.EnforcementSystem_SK = es.EnforcementSystem_SK
JOIN DimCustomer c ON c.Customer_SK = be.Customer_SK
WHERE c.Active = 1
AND es.EnforcementSystem <> 'Undetermined'
-- and be.Customer_SK = 719
),
DatesCTE AS(
SELECT
DateKey
, [Date]
FROM DimDate
WHERE date >= DATEADD(year, DATEDIFF(year, 0, GETDATE()) -3, 0)
AND Date < CAST(GETDATE() as Date)
),
ViolationsCTE AS(
SELECT
EventDateKey
, Customer_SK
, EnforcementSystem_SK
, LaneLocation_SK
, COUNT(1) AS Violations
FROM FactEvent
WHERE EventDateKey >= ( SELECT datekey
FROM DimDate
WHERE date = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -3, 0)
)
GROUP BY
EventDateKey
, Customer_SK
, EnforcementSystem_SK
, LaneLocation_SK
)
SELECT
d.DateKey
, d.Date
, s.Customer_SK
, s.EnforcementSystem
, MIN(seb.Site_ID) AS Site_ID
, t.LocationDescription
, SUM(v.Violations) AS RecordedViolations
, t.RecordedTraffic
, t.ActiveHours AS TrafficActiveHours
, seb.ViolationType_SK
, MIN(seb.EnforcementStartDate) AS EnforcementStartDate
, MAX(seb.EnforcementEndDate) AS EnforcementEndDate
, CASE
WHEN d.Date >= MAX(seb.EnforcementStartDate) AND d.date <= MAX(seb.EnforcementEndDate) AND seb.ViolationType_SK <> 4 THEN '1'
WHEN seb.ViolationType_SK = 4 THEN '2'
ELSE '0'
END AS ExpectedActive
FROM SystemsCTE s
CROSS JOIN DatesCTE d
JOIN SiteIDEnforcementBridgeCTE seb ON seb.EnforcementSystem = s.EnforcementSystem AND seb.Customer_SK = s.Customer_SK
LEFT JOIN ViolationsCTE v ON v.EnforcementSystem_SK = seb.EnforcementSystem_SK AND v.EventDateKey = d.DateKey
LEFT JOIN SiteTrafficCTE t ON t.TrafficActivityDateKey = d.DateKey AND t.Site_ID = seb.Site_ID AND t.Customer_SK = s.Customer_SK
WHERE DateKey IS NOT NULL
--AND s.Customer_SK = 719
--AND DateKey = '20201103'
GROUP BY
d.DateKey
, d.Date
, S.Customer_SK
, S.EnforcementSystem
, t.LocationDescription
, t.RecordedTraffic
, t.ActiveHours
, seb.ViolationType_SK
HAVING (CASE
WHEN d.Date >= MAX(seb.EnforcementStartDate) AND d.date <= MAX(seb.EnforcementEndDate) AND seb.ViolationType_SK <> 4 THEN '1'
WHEN seb.ViolationType_SK = 4 THEN '2'
ELSE '0'
END) <> 0
ORDER BY
d.DateKey DESC
, s.Customer_SK
, s.EnforcementSystem