I'm trying to create an SSRS report that looks similar to the table below:

I know how to query the last 3 columns individually, but is it possible to get all 3 columns using 1 query? I have tried the query below to show my line of thinking but its not working as desired.

SELECT Report
  ,Min(TimeStart) AS EarliestRun
  ,Max(TimeStart) AS RecentRun
  ,CASE WHEN TimeStart BETWEEN GETDATE()-7 AND GETDATE() THEN COUNT(Report) END AS RunsLast7Days
FROM ReportHistory
WHERE TimeStart BETWEEN '1/1/2019 00:00' AND GETDATE()
GROUP BY Report

CodePudding user response：

Yes - use conditional aggregation. Don't filter the query at all since you need an "all time" value. Instead, use sum with a conditional expression for the periods of interest.

select ... 
sum(case when TimeStart >= dateadd(day, -7, getdate()) then 1 else 0 end) as [Runs Last 7 days], 
sum(case when TimeStart >= datefromparts(year(getdate()), 1, 1) then 1 else 0 end) as [Runs YTD], 
... 
from dbo.ReportHistory
order by ...;

CodePudding user response：

I was going to propose using CROSS APPLY but SMor has done it with less code

CREATE TABLE #Reports (
    ReportId INT NOT NULL,
    ReportName VARCHAR(20) NOT NULL
);

INSERT INTO #Reports(ReportId, ReportName)
VALUES(1,  'Report 1');

CREATE TABLE #ReportRun (
    ReportId INT,
    RunDateTime DATETIME2(2)
);

INSERT INTO #ReportRun(ReportId, RunDateTime)
VALUES
(1, '20220508 10:00:00'),
(1, '20220502 10:00:00'),
(1, '20220101 10:00:00'),
(1, '20210501 10:00:00'),
(1, '20210209 10:00:00'),
(1, '20200509 10:00:00'),
(1, '20190509 10:00:00');

GO

-- SELECT * FROM #Reports
-- SELECT * FROM #ReportRun

SELECT R.ReportName, B.RunLast7Days, C.RunYearToDate, D.RunAllTime
FROM #Reports AS R
CROSS APPLY (
    SELECT TOP 1 RunDateTime
    FROM #ReportRun
    WHERE ReportId = R.ReportId
    ORDER BY RunDateTime DESC
) AS ER
CROSS APPLY (
    SELECT COUNT(*) AS RunLast7Days
    FROM #ReportRun
    WHERE ReportId = R.ReportId
    AND RunDateTime >= DATEADD(day, -7, CONVERT(date, GETDATE())) -- best to set it to the start of the day
    GROUP BY ReportId
) AS B
CROSS APPLY (
    SELECT COUNT(*) AS RunYearToDate
    FROM #ReportRun
    WHERE ReportId = R.ReportId
    AND RunDateTime >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
    GROUP BY ReportId
) AS C
CROSS APPLY (
    SELECT COUNT(*) AS RunAllTime
    FROM #ReportRun
    WHERE ReportId = R.ReportId
    GROUP BY ReportId
) AS D