Cumulative SQL Column Values ​​by Time Period

Question

I have numerical data that arrives every 5 minutes (i.e. 288 values ​​per day and data for several days). I need to write a query that can return the sum of all values ​​for each day. So currently the table looks like this:

03/30/2010 00:01:00  --   553   
03/30/2010 00:06:00  --   558   
03/30/2010 00:11:00  --   565  
03/30/2010 00:16:00  --   565  
03/30/2010 00:21:00  --   558   
03/30/2010 00:26:00  --   566  
03/30/2010 00:31:00  --   553   
...

And this goes on for “x” the number of days, I would like the query to return “x” the number of rows, each of which contains the sum of all the values ​​in each day. Something like that:

03/30/2010  --  <sum>
03/31/2010  --  <sum>
04/01/2010  --  <sum>

The request will go inside the Dundas web part, so unfortunately I cannot write custom functions to help it. All logic should be in one big request. Any help would be appreciated, thanks. I'm trying to get it to work with GROUP BY and DATEPART at the moment, not sure if this is the right thing to do.

Answer 1

Here is a good trick. If you point SQL DATETIMEto FLOAT, it will give you a date like days.fractionofday

Therefore, if you fill this word and return it back to DATETIME, it will give you the opportunity to put the appropriate date.

CAST(FLOOR(CAST(MyDateTime  AS FLOAT)) AS DATETIME)

Therefore, my favorite way to do this.

select
    CAST(FLOOR(CAST(OrderDate AS FLOAT)) AS DATETIME)
    , sum(taxamt) as Amount
from
    Sales.SalesOrderHeader
group by
        CAST(FLOOR(CAST(OrderDate AS FLOAT)) AS DATETIME)

I don't know if this was more / less effective than any previous correct answers.

Answer 2

U CAST

SELECT [ENTRY_DATE],CAST([ENTRY_DATE] AS date) AS 'date' 
FROM [PROFIT_LIST]

.

SELECT CAST([ENTRY_DATE] AS date) AS 'date',SUM(PROFIT) 
FROM [PROFIT_LIST]
GROUP BY CAST([ENTRY_DATE] AS date) AS 'date'

Answer 3

, DATEPART , DATETIME ( , ). , , DATEADD "" YYYY-MM-DD 00:00:00.000.

Adventure Works , ( SQL Server 2005). DATEADD @OMG Ponies:

select
    dateadd(dd, 0, datediff(dd, 0, OrderDate)) as SaleDay
    , sum(taxamt) as Amount
from
    Sales.SalesOrderHeader
group by
    dateadd(dd, 0, datediff(dd, 0, OrderDate))
order by
    SaleDay

The idea dateadd(dd, 0, datediff(dd, 0, OrderDate))is to first get the "number of days that have passed from the beginning of the time to your date" ( datediff-part), and then add this number of days to the "beginning of time". This gives you the "beginning of your day." I hope this is clear :)