February 3, 2014 at 9:35 pm
Good day,
Is it possible to do this on a case select on sql query. Thanks
ALTER PROC DoubleSidedSheets
@StartDate datetime ,
@EndDate datetime ,
@SCount int=0,
@DCount int=0,
@DSheets int=0,
@SSheets int=0
AS
SELECT Right(rpt.[Cost Center],4) as [Cost Center Name] ,rpt.[User Id] as [User ID],
COUNT (CASE when rpt.Pages = rpt.[Sheets] THEN
@SCount=(@SCount + rpt.[Sheets]) as [Single Sided Print],
@DCount=0 as [Double Sided Print],
ELSE
@DSheets = (Pages /2)
@SSheets = (Sheets - @DSheets)
@DCount = @DCount + @DSheets as[Double Sided Print],
@SCount = @SCount + @SSheets as [Single Sided Print]
END
FROM rpt_print_transactions rpt
WHERE
rpt.[Date/Time] >=@StartDate and rpt.[Date/Time] <=@EndDate
Group By rpt.[Cost Center],rpt.[User Id]
exec DoubleSidedSheets
'2014-02-01','2014-02-02'
February 4, 2014 at 8:52 am
You have some errors on the way you want to generate the query. You can't combine the assignment of variables with the return of results and you need to write a CASE for each column on your query (at least).
However, I don't understand why would you want to count a value returned by a formula. Wouldn't you need to use SUM?
Here's my interpretation from your query. I have nothing to test and I don't know what you expect from this. For better answers, please read the article linked in my signature.
ALTER PROCEDURE DoubleSidedSheets @StartDate DATETIME,
@EndDate DATETIME,
@SCount INT = 0,
@DCount INT = 0,
@DSheets INT = 0,
@SSheets INT = 0
AS
SELECT Right(rpt.[Cost Center], 4) AS [Cost Center Name],
rpt.[User Id] AS [User ID],
CASE
WHEN rpt.Pages = rpt.[Sheets]
THEN @SCount + rpt.[Sheets]
ELSE @SCount + (Sheets - (Pages / 2))
END AS [Single Sided Print],
CASE
WHEN rpt.Pages = rpt.[Sheets]
THEN @DCount = 0
ELSE (Pages / 2) + @DCount
END AS [Double Sided Print]
FROM rpt_print_transactions rpt
WHERE rpt.[Date/Time] >= @StartDate
AND rpt.[Date/Time] <= @EndDate
GROUP BY rpt.[Cost Center],
rpt.[User Id]
GO
---OR
ALTER PROCEDURE DoubleSidedSheets @StartDate DATETIME,
@EndDate DATETIME,
@SCount INT = 0,
@DCount INT = 0,
@DSheets INT = 0,
@SSheets INT = 0
AS
SELECT Right(rpt.[Cost Center], 4) AS [Cost Center Name],
rpt.[User Id] AS [User ID],
SUM(CASE
WHEN rpt.Pages = rpt.[Sheets]
THEN @SCount + rpt.[Sheets]
ELSE @SCount + (Sheets - (Pages / 2))
END) AS [Single Sided Print],
SUM(CASE
WHEN rpt.Pages = rpt.[Sheets]
THEN @DCount = 0
ELSE (Pages / 2) + @DCount
END) AS [Double Sided Print]
FROM rpt_print_transactions rpt
WHERE rpt.[Date/Time] >= @StartDate
AND rpt.[Date/Time] <= @EndDate
GROUP BY rpt.[Cost Center],
rpt.[User Id]
GO
February 4, 2014 at 9:23 am
Thank you. That was meant to be SUM. I will test it and thank you for the link as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply