Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Case select sql 2000 Expand / Collapse
Author
Message
Posted Monday, February 3, 2014 9:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:00 AM
Points: 9, Visits: 25
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'
Post #1537605
Posted Tuesday, February 4, 2014 8:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 3,637, Visits: 7,932
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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1537811
Posted Tuesday, February 4, 2014 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:00 AM
Points: 9, Visits: 25
Thank you. That was meant to be SUM. I will test it and thank you for the link as well.
Post #1537830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse