July 2, 2009 at 3:12 pm
I have a table that has a billing date field that is a date and time value. There is a record for every hour for every day. The billing day for the company I work for is 9am to 8:59 am the next day. I need to sum a couple of fields by the billing day for every day of the month.
For example:
Billing Date Field1 Field2
------------------ ------ ------
6/1/2009 08:00:00 4 4
6/1/2009 09:00:00 5 2
6/1/2009 10:00:00 3 7
.........
6/2/2009 09:00:00 4 1
6/2/2009 10:00:00 3 6
I need to sum field1 for 6/1 9am to 6/2 8:59am, 6/2 9am to 6/3 8:59am, etc. for the entire month.
What is the best way to do this?
Thank you for the help. I really appreciate it.
July 2, 2009 at 4:08 pm
Something in the lines of this should work...
select sum(field1),datepart(mm,billingdate) as month
from tablename
where billingdate between '2009-06-01 09:00:00' and '2009-12-01 08:59:00'
group by datepart(mm,billingdate)
July 2, 2009 at 4:21 pm
Sorry about that, I wasn't very clear before (in my mind I knew what I meant :-)). I need a sum per day for the whole month so the output would be something like:
6/1/2009 5.4
6/2/2009 2.5
6/3/2009 3.0
...
6/30/2009 4.6
July 2, 2009 at 5:32 pm
I've created a #Sales table and populated it with some test data - providing a TSQL script to generate the test data is the preferred method of presenting test data on this forum. I've taken the liberty of adding an identity column and defining a clustered primary key on the combination of the BillingDate column and the identity column to ensure uniqueness.
CREATE TABLE #Sales (
Id int IDENTITY(1,1) NOT NULL,
BillingDate datetime NOT NULL,
Field1 int NOT NULL,
Field2 int NOT NULL,
CONSTRAINT PK_Sales PRIMARY KEY CLUSTERED (BillingDate, Id)
)
INSERT #Sales (BillingDate, Field1, Field2)
SELECT '2009-06-01T08:00:00', 4, 4 UNION ALL
SELECT '2009-06-01T09:00:00', 5, 2 UNION ALL
SELECT '2009-06-01T10:00:00', 3, 7 UNION ALL
SELECT '2009-06-02T09:00:00', 4, 1 UNION ALL
SELECT '2009-06-02T10:00:00', 3, 6 UNION ALL
SELECT '2009-06-10T08:59:59', 6, 2 UNION ALL
SELECT '2009-06-10T09:00:00', 9, 7
My solution is to generate dynamically a "calendar table" using a CTE for the target month. Each row in this "calendar table" contains a StartInterval and EndInterval datetime column that will delimit the datetime range from 09:00 on a given day to 09:00 on the following day. The "calendar table" is generated by using a Tally table (of sequential integers from 1 to at least 31). This "calendar table" is then (left) joined with the #Sales table, using the StartInterval and EndInterval columns to join each row in the #Sales table with the correct billing day. This approach should allow SQL Server to make use of a clustered index on the #Sales.BillingDate column.
DECLARE @TargetMonth datetime
SELECT @TargetMonth = '20090601'
DECLARE @TargetMonthStart datetime
SELECT @TargetMonthStart = DATEADD(month, DATEDIFF(month, 0, @TargetMonth), '09:00:00')
DECLARE @DaysInMonth int
SELECT @DaysInMonth = DATEDIFF(day, @TargetMonthStart, DATEADD(month, 1, @TargetMonthStart))
;WITH cteMonth (DayNumber, Label, StartInterval, EndInterval) AS (
SELECT
T.N,
CONVERT(varchar(8), @TargetMonthStart + T.N - 1, 112),
@TargetMonthStart + T.N - 1,
@TargetMonthStart + T.N
FROM dbo.Tally T
WHERE (T.N BETWEEN 1 AND @DaysInMonth)
)
SELECT
M.DayNumber AS [DayOfMonth],
M.Label AS [Date],
COALESCE(SUM(S.Field1), 0) AS [SumOfField1]
FROM cteMonth M
LEFT OUTER JOIN #Sales S ON (S.BillingDate >= M.StartInterval AND S.BillingDate < M.EndInterval)
GROUP BY M.DayNumber, M.Label
ORDER BY M.DayNumber
July 3, 2009 at 5:35 am
I used to come across this problem, the company I work for use 08:00- 07:59, if you subtract 9 hours from the Date (I subtract 8) you will get the actual day:
SELECT DATEADD(D, DATEDIFF(D, 0, DATEADD(HH, - 9, BillingDate)), 0) AS 'BillingDate', SUM(Field1) AS 'Sum of Date'
FROM [Table]
WHERE BillingDate Between '2009-06-01 09:00:00' And '2009-06-30 08:59:59'
GROUP BY DATEADD(D, DATEDIFF(D, 0, DATEADD(HH, - 9, BillingDate)), 0)
If you want the month use DATEADD(M, DATEDIFF(M, 0... and extend the Between range.
Dave
July 3, 2009 at 6:24 am
Be careful when using the Between operator with dates.
In your requirements, you basically are saying that your billing date starts promptly at 9am; everything prior to that is the previous billing date.
So, something at 8:59:59.997 is the previous date.
This would require that your between clause be " between 09:00:00 and 08:59:59.997".
One post used "08:59:59". This could omit transactions between .003 and .997 seconds.
Now, add to this mix the new data types available in sql 2008. You can now have accuracy to the microsecond. So, even the .997 is no longer sufficient when you can go to .999999.
What to do, and make it where it will always work? Pretty simple actually... break it apart:
where [date field]>= "date value" "09:00:00" and [date field] < "date value + 1" "09:00:00"
Here, anything on or after 9am, and anything prior to 9am (8:59:59.997, 8:59:59.999999 etc.) of the next day, will be handled as part of the previous day.
HTH,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 6, 2009 at 3:18 pm
Thanks for the help everybody, I really appreciate it. You saved me a lot of frustration and time. I also had not heard of the CTE's in SQL 2005.
Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply