need help in case stmt

Question

Post reply

need help in case stmt

isa-369599

SSCommitted

Points: 1602
More actions
December 9, 2007 at 11:08 pm

#201540

Hello evryone, i m using SQL Server 2000, i need help from all of you, Here are the query and Table structure :
Transactions
TransactionID int (Pk)
TrasactionDate Datetime
TransactionCode varchar(50)
TollAmount money
ChargedAmount money
Declare @TranDate datetime
set @Trandate = '12/8/2007'
select
COUNT(CASE WHEN datepart(hh,TransactionDate) = 0 THEN dbo.Transactions.TransactionID END) AS [12:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 1 THEN dbo.Transactions.TransactionID END) AS [1:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 2 THEN dbo.Transactions.TransactionID END) AS [2:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 3 THEN dbo.Transactions.TransactionID END) AS [3:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 4 THEN dbo.Transactions.TransactionID END) AS [4:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 5 THEN dbo.Transactions.TransactionID END) AS [5:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 6 THEN dbo.Transactions.TransactionID END) AS [6:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 7 THEN dbo.Transactions.TransactionID END) AS [7:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 8 THEN dbo.Transactions.TransactionID END) AS [8:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 9 THEN dbo.Transactions.TransactionID END) AS [9:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 10 THEN dbo.Transactions.TransactionID END) AS [10:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 11 THEN dbo.Transactions.TransactionID END) AS [11:00 AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 12 THEN dbo.Transactions.TransactionID END) AS [12:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 13 THEN dbo.Transactions.TransactionID END) AS [1:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 14 THEN dbo.Transactions.TransactionID END) AS [2:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 15 THEN dbo.Transactions.TransactionID END) AS [3:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 16 THEN dbo.Transactions.TransactionID END) AS [4:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 17 THEN dbo.Transactions.TransactionID END) AS [5:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 18 THEN dbo.Transactions.TransactionID END) AS [6:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 19 THEN dbo.Transactions.TransactionID END) AS [7:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 20 THEN dbo.Transactions.TransactionID END) AS [8:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 21 THEN dbo.Transactions.TransactionID END) AS [9:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 22 THEN dbo.Transactions.TransactionID END) AS [10:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 23 THEN dbo.Transactions.TransactionID END) AS [11:00 PM]
FROM dbo.Transactions
where TransactionDate >=(@TranDate+'12:00:00.000 am' )
AND TransactionDate <=(@TranDate+'11:59:59.999 pm')
Group By CONVERT(VARCHAR(25),TransactionDate,101)
This Query Returns the TransactionCount per hour within a specific day,(how many transactions being processed in each hour in a day), now i
also add the ChargedAmount collected / hr thing too, but dont understand how i add this, as if i add it in the after the count stmt it gives me error
"Incorrect Syntax near count", plz tell me how i get this column too with this. Plz help me and reply me asap.
Thanx in Advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Since your aggregating rows only for specific date, you don't need a GROUP BY clause.

Now for your query, you need to add sum aggregate to the query as

SUM(CASE WHEN datepart(hh,TransactionDate) = 0 THEN ChargedAmount END) AS [12:00 AM Charge]

--Ramesh

isa-369599 SSCommitted Points: 1602 More actions · Answer 2

i need both in 1 query as i make 2 queries 1 for count and 1 for Sum and then make a union that returns 2 tables, but is this possible with 1 query stmt?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

First, you must make the dates correct... the following code exemplifies the error... run it an see...

DECLARE @AnyDate DATETIME

SET @AnyDate = '12/8/2007'

SELECT @AnyDate + '11:59:59.999 pm'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)