January 31, 2007 at 2:35 am
here IS my previous query, i modified it :
SELECT distinct dbo.Buyers.CompanyName,count(dbo.TransactionLineItems.PinID)as TotalPins,sum(TotalAmount)as Amount,
dateadd(hh,Datediff(hh,0,TransactionDate),0)as TransactionDate
FROM dbo.Transactions INNER JOIN
dbo.Buyers ON dbo.Transactions.BuyerID = dbo.Buyers.BuyerID INNER JOIN
dbo.TransactionLineItems ON dbo.Transactions.TransactionID = dbo.TransactionLineItems.TransactionID INNER JOIN
dbo.Pins ON dbo.TransactionLineItems.PinID = dbo.Pins.PinID
group by dateadd(hh,datediff(hh,0,dbo.Transactions.Transactiondate),0),dbo.Buyers.CompanyName,dbo.Transactions.TransactionTypeID, dbo.Transactions.TransactionStatusCode
HAVING (dbo.Transactions.TransactionTypeID = 3) AND (dbo.Transactions.TransactionStatusCode = 4)
now after the modification my query IS ::
select cast(TransactionDate as varchar(25)) as 'date',
datepart(hh,TransactionDate)as 'hour',
COUNT(dbo.TransactionLineItems.PinID) AS TotalPins
from dbo.Transactions INNER JOIN
dbo.TransactionLineItems ON dbo.Transactions.TransactionID = dbo.TransactionLineItems.TransactionID INNER JOIN
dbo.Pins ON dbo.TransactionLineItems.PinID = dbo.Pins.PinID
where TransactionDate >= '12/21/2006' and TransactionDate < '12/22/2006'
Group By cast(TransactionDate as varchar(25)),
datepart(hh,TransactionDate)
the modified query that RETURNS the result IN the following order:
date Hour TotalPins
Dec 21 2006 10:29 AM 10 1
Dec 21 2006 10:53 AM 10 1
Dec 21 2006 11:12 AM 11 1
Dec 21 2006 12:36 AM 12 6
Dec 21 2006 5:15 PM 17 1
Dec 21 2006 5:19 PM 17 1
Dec 21 2006 5:21 PM 17 2
but i want TO get the result IN that format :::::::::::
date Hour TotalPins
Dec 21 2006 10:00 AM 10 2
Dec 21 2006 11:00 AM 11 1
Dec 21 2006 12:00 AM 12 6
Dec 21 2006 5:00 PM 17 4
kindly tell me how i do this?? AS i want TO make a chart , IN x-axis , it shows the hours OF a DAY
AND y-axis shows the TotalPins purchases FROM the SYSTEM through web interface.
January 31, 2007 at 4:24 am
i solved the problem that i posted above, now the issues come abt data format.
now i want to pivot the data, how can i this , as if i use the case stmts it gives me the error. There is any other way how i do that?
result IS now IN this format:
date hour TotalPins
12/21/2006 10 2
12/21/2006 11 2
12/21/2006 12 6
12/21/2006 17 4
i want this format:::
10 11 12 17
2 2 6 4
how can i get this format? is that possible date also comes with the hour, as new user sees the 10,11,12,17, it not understand what is this or hours of which date, current date hours or the previous ones??
do reply.
January 31, 2007 at 7:40 am
The problem is that you need to know the total # of columns to get the CASE to work properly. Or you have to cover all bases, like
select
CASE hour
WHEN 10 then Pins
ELSE 0
END '10'
, CASE hour
WHEN 11 then Pins
ELSE 0
END '11'
...
etc.
A better way is to handle this in the application. Let it read down the results and print them out wide.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply