January 25, 2007 at 10:13 pm
Hello everyone,i need a little bit help, here is my query that returns the record but based on Hours of each day,(e.g in todays date how many pin sales at different hours).
Now i want to make a new SP for Chart, in this SP i give the Date parameter, it returns how many pins sales in different hours. Output shoule be come in this format:
26/1/2007 11:00:00.000 26/1/2007 12:00:00.000 26/1/2007 .......................
2 10 30
or
11 hours 12 hours 13 hours 14 hours 15 hours 16 hours .......................
2 10 30 0 57 1 .......................
plz check my query and format this with according to my output.
Thanx in Advance.
January 25, 2007 at 11:19 pm
select cast(salesdate as varchar(10)) as 'date',
datepart(hh,salesdate)as 'hour',
count(1)as PR
from TableName
where salesdate >= '' and salesdate < ''
Group By cast(salesdate as varchar(10)),
datepart(hh,salesdate)
Order By cast(salesdate as varchar(10)),
datepart(hh,salesdate)
MohammedU
Microsoft SQL Server MVP
January 26, 2007 at 12:35 am
here is my query, sorry i forgot to write this in my post, now i want to make a SP , check my previous post and then kindly mtell how to make a SP for this ,and it returs the output in the actual format that i write in my actual post.
SELECT DISTINCT dbo.Buyers.CompanyName, dbo.PinPackages.PackageName, COUNT(dbo.TransactionLineItems.PinID) AS TotalPins,
SUM(dbo.Transactions.TotalAmount) AS TotalAmount, dateadd(hh,Datediff(hh,0,TransactionDate),0) as TransactionDate
FROM dbo.Buyers INNER JOIN
dbo.Transactions ON dbo.Buyers.BuyerID = dbo.Transactions.BuyerID INNER JOIN
dbo.TransactionLineItems ON dbo.Transactions.TransactionID = dbo.TransactionLineItems.TransactionID INNER JOIN
dbo.Pins ON dbo.TransactionLineItems.PinID = dbo.Pins.PinID INNER JOIN
dbo.PinPackages ON dbo.Pins.PinPackageCode = dbo.PinPackages.PinPackageCode
GROUP BY dateadd(hh,Datediff(hh,0,TransactionDate),0), dbo.Buyers.CompanyName, dbo.Transactions.TransactionTypeID, dbo.Transactions.TransactionStatusCode,
dbo.PinPackages.PackageName
HAVING (dbo.Transactions.TransactionTypeID = 3) AND (dbo.Transactions.TransactionStatusCode = 4)
January 26, 2007 at 3:04 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply