Select Multiple Sums - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Multiple Sums

Author

Topic

crwenger
Starting Member

2 Posts

Posted - 2007-01-04 : 13:12:45

Hi,

I've written this query that returns a single value as the SUM of 3 interger
columns using a single DateTime parameter. I would like to modify this query
to return this sum and 4 more following days values in a single row as Day1,
Day2, and so forth. Hope I made myself understood and any help would be
greatly appreciated.

SELECT SUM(JobHistoryDetail.NumberOfSkippedFiles +
JobHistoryDetail.NumberOfCorruptFiles + JobHistoryDetail.NumberOfInUseFiles)
AS Day1
FROM JobHistorySummary INNER JOIN
JobHistoryDetail ON JobHistorySummary.JobHistoryID =
JobHistoryDetail.JobHistoryID INNER JOIN
JobHistoryDetailInfo ON
JobHistoryDetail.JobHistoryDetailID = JobHistoryDetailInfo.JobHistoryDetailID
WHERE (JobHistorySummary.OriginalStartTime BETWEEN DATEADD(HOUR, 5,
@PARAM1) AND DATEADD(HOUR, 29, @PARAM1))

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-04 : 13:39:04

This should do it

SELECT SUM(CASE WHEN JobHistorySummary.OriginalStartTime BETWEEN DATEADD(HOUR, 5,
@PARAM1) AND DATEADD(HOUR, 29, @PARAM1) THEN JobHistoryDetail.NumberOfSkippedFiles +
JobHistoryDetail.NumberOfCorruptFiles + JobHistoryDetail.NumberOfInUseFiles ELSE 0 END)
AS Day1,
SUM(CASE WHEN JobHistorySummary.OriginalStartTime BETWEEN DATEADD(HOUR, 29,
@PARAM1) AND DATEADD(HOUR, 53, @PARAM1) THEN JobHistoryDetail.NumberOfSkippedFiles +
JobHistoryDetail.NumberOfCorruptFiles + JobHistoryDetail.NumberOfInUseFiles ELSE 0 END)
AS Day2,
SUM(CASE WHEN JobHistorySummary.OriginalStartTime BETWEEN DATEADD(HOUR, 53,
@PARAM1) AND DATEADD(HOUR, 77, @PARAM1) THEN JobHistoryDetail.NumberOfSkippedFiles +
JobHistoryDetail.NumberOfCorruptFiles + JobHistoryDetail.NumberOfInUseFiles ELSE 0 END)
AS Day3,
SUM(CASE WHEN JobHistorySummary.OriginalStartTime BETWEEN DATEADD(HOUR, 77,
@PARAM1) AND DATEADD(HOUR, 101, @PARAM1) THEN JobHistoryDetail.NumberOfSkippedFiles +
JobHistoryDetail.NumberOfCorruptFiles + JobHistoryDetail.NumberOfInUseFiles ELSE 0 END)
AS Day4,
SUM(CASE WHEN JobHistorySummary.OriginalStartTime BETWEEN DATEADD(HOUR, 101,
@PARAM1) AND DATEADD(HOUR, 125, @PARAM1) THEN JobHistoryDetail.NumberOfSkippedFiles +
JobHistoryDetail.NumberOfCorruptFiles + JobHistoryDetail.NumberOfInUseFiles ELSE 0 END)
AS Day5
FROM JobHistorySummary INNER JOIN
JobHistoryDetail ON JobHistorySummary.JobHistoryID =
JobHistoryDetail.JobHistoryID INNER JOIN
JobHistoryDetailInfo ON
JobHistoryDetail.JobHistoryDetailID = JobHistoryDetailInfo.JobHistoryDetailID
WHERE (JobHistorySummary.OriginalStartTime BETWEEN DATEADD(HOUR, 5,
@PARAM1) AND DATEADD(HOUR, 125, @PARAM1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources