December 11, 2007 at 10:06 pm
Hello everyone, here is my query:
Declare @TranDate datetime
set @TranDate ='12/8/2007'
SELECT DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0) TransactionDate,
COUNT(TransactionID)as VehiclesCount,
SUM(ChargedAmount) as ChargedAmount
FROM dbo.Transactions
WHERE TransactionDate >=(@TranDate+'12:00:00.000 am')
AND TransactionDate <=(@TranDate+'11:59:59.999 pm')
GROUP BY DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0)
Its retunrs the data of 24 hours of a given date and returns rows of that hours which contains the data, but now i want to give data+ time and this query returns the data of that date but the last 6 hours data from the time , let say if i give todays date 12-dec-2007 09:56:00 am, my query returns the data of last 6 hours from 9:56 am , it means return the data of hour 8 am,7 am,6 am,5 am,4 am,3 am of todays date 12-dec-07 from my table Transactions.
here i modify my query:
SELECT DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0) TransactionDate,
COUNT(TransactionID)as VehiclesCount,
SUM(ChargedAmount) as ChargedAmount
FROM Transactions
where TransactionDate between DATEADD(Hour, -6, '12/11/2007 4:00:00') and '12/11/2007 4:00:00'
GROUP BY DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0)
but it returns nothing, kindly check it whats wrong with this. i Requeted to all of the grouo memebers plz help me in this query.
i m very greatfull to all of you.
Thanx in Advance.
December 11, 2007 at 11:50 pm
do u want to have current date minus 6hrs data?
December 12, 2007 at 3:03 am
Hello here is my query:
SELECT DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0) TransactionDate,
COUNT(TransactionID)as VehiclesCount,
SUM(ChargedAmount) as ChargedAmount
FROM Transactions
where TransactionDate between DATEADD(Hour, -6,getdate())
and getdate()
GROUP BY DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0)
ORDER BYTransactionDate desc
It returns the data of last 6 hours from the current date time, it shows the data where that hour exists,
but i want zeros also ,i mean to say if my table has data of hour 12,13,18 not for 14 and 14 then shows zeros rows in my result, plz
tell me how i do thid as if i used simply without group by then it shows the data rows only which have data for that hour from that 6 hours
plz help me in this regard and reply me asap.
Thanx in Advance.
December 12, 2007 at 3:49 am
Easiest way to do that is with a numbers table. It's a very handly thing for a lot of problems. I'm creating a temp numbers table, but you can create a permanent one, with as many rows as you may need.
This is untested, but should give you an idea even if it doesn't work 100%
Select top 24 IDENTITY(INT,0,1) AS Number into #Numbers FROM syscolumns
SELECT
DATEADD(hh,#Numbers.Number, BaseDate) AS TransactionDate,
ISNULL(VehiclesCount,0) AS VehiclesCount,
ISNULL(ChargedAmount,0) AS ChargedAmount
FROM #Numbers LEFT OUTER JOIN
(SELECT DATEADD(dd,DATEDIFF(dd,0,TransactionDate),0) BaseDate,
DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0) TransactionDate,
COUNT(TransactionID)as VehiclesCount,
SUM(ChargedAmount) as ChargedAmount
FROM Transactions
where TransactionDate between DATEADD(Hour, -6,getdate()) and getdate()
GROUP BY DATEADD(dd,DATEDIFF(dd,0,TransactionDate),0), DATEADD(hh,DATEDIFF(hh,0,TransactionDate),0)
) ON #Numbers.Number = DATEPART(hh,TransactionDate)
ORDER BY TransactionDate desc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply