Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Roll up Daily Sums with Start of day 0600

Author  Topic 

robnjay
Starting Member

14 Posts
Posted - 2012-09-14 : 11:12:42
I am trying to roll up daily Sums for some data that comes in (with timestamp) at various times during the day.

The problem is that I need to have the "Day" run from 0600 to 0600 the next Calendar day to coincide with shift changes.

So a Daily average would have a timestamp of DD/MM/YYYY 06:00:00 and would be for the following 24 Hrs - i.e average timestamped 09-14-2012 06:00:00 would have an average for all data between 09-14-2012 06:00:00 and 09-15-2012 05:59:59...

This is a query which sums this data between 2 Date/Times:

SELECT
  1 as Bin_No,
  MAX([ReportDate]) as ReportDate,
  Sum([T15E_14_1]) AS T15E141,
  Sum([T15W_14_1]) AS T15W141,
  Sum([T16E_14_1]) AS T16E141,
  Sum([T16W_14_1]) AS T16W141,
  Sum([TOS1_14_1]) AS TOS1141,
  Sum([TOS2_14_1]) AS TOS2141,
  Sum([TOS3_14_1]) AS TOS3141,
  Sum([TOS4_14_1]) AS TOS4141,
  Sum([TOS5_14_1]) AS TOS5141,
  Sum([TOS6_14_1]) AS TOS6141,
  Sum([TOS7_14_1]) AS TOS7141,
  Sum([TOS8_14_1]) AS TOS8141,
  Sum([TOS9_14_1]) AS TOS9141,
  Sum([TOS10_14_1]) AS TOS10141,
  Sum([TOS11_14_1]) AS TOS11141,
  MAX([Bin1BXType]) AS Bin1BXType,
  Sum([T15E_15_1]) AS T15E151,
  Sum([T15W_15_1]) AS T15W151,
  Sum([T16E_15_1]) AS T16E151,
  Sum([T16W_15_1]) AS T16W151,
  Sum([TOS1_15_1]) AS TOS1151,
  Sum([TOS2_15_1]) AS TOS2151,
  Sum([TOS3_15_1]) AS TOS3151,
  Sum([TOS4_15_1]) AS TOS4151,
  Sum([TOS5_15_1]) AS TOS5151,
  Sum([TOS6_15_1]) AS TOS6151,
  Sum([TOS7_15_1]) AS TOS7151,
  Sum([TOS8_15_1]) AS TOS8151,
  Sum([TOS9_15_1]) AS TOS9151,
  Sum([TOS10_15_1]) AS TOS10151,
  Sum([TOS11_15_1]) AS TOS11151,
  Sum([T12B_14_1]) AS T12B141,
  Sum([T13B_15_1]) AS T13B151  
FROM
  [R21AWW].[ww].[tblR21ABin1Data]  
WHERE
  [R21AWW].[ww].[tblR21ABin1Data].[ReportDate] BETWEEN '5/31/2012' and '6/25/2012'
  GROUP BY
  Bin1BXType;


Obviously, this gives me one row of data with one [ReportDate] timestamp...

I need to return a sum for every day (0600 to 0600) between a start date and end date..


I would like to be able to define this in a view...

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-14 : 11:30:36
[code]
SELECT MIN(datefield),AVG(datafield*1.0) AS AvgVal
FROM Table
GROUP BY DATEADD(hh,DATEDIFF(hh,0,datefield)-6,0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-14 : 11:44:57
Thanks, Visakh16! I was editing this to make it more specific and correctly state that I want Sums instead of averages while you were replying...

Does my query give you some better ideas?

Thanks Again!!
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-14 : 16:16:18
Ok - I did a little playing with this and even though I really don't understand the "mad science" (I am in awe) I got to this:

SELECT MIN([ReportDate]) as TheDate,SUM(([T15E_14_1])*1.0) AS SumVal
FROM 
   [R21AWW].[ww].[tblR21ABin1Data]
WHERE
   [R21AWW].[ww].[tblR21ABin1Data].[ReportDate] BETWEEN '9/1/2012' and '9/14/2012'
GROUP BY DATEADD(hh,DATEDIFF(hh,0,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])-6,0)


Which returned this:


TheDate                   SumVal
-----------------------   -----------------
2012-09-01 00:00:00.000   0
2012-09-02 00:00:00.000   386.599990844727
2012-09-03 00:00:00.000   315.599990844727
2012-09-04 00:00:00.000   0
2012-09-05 00:00:00.000   0
2012-09-06 00:00:00.000   0
2012-09-07 00:00:00.000   0
2012-09-08 00:00:00.000   0
2012-09-09 00:00:00.000   0
2012-09-10 00:00:00.000   395.500005722046
2012-09-11 00:00:00.000   1715.09997558594
2012-09-12 00:00:00.000   340.600006103516
2012-09-13 00:00:00.000   0
2012-09-14 00:00:00.000   0


(Please note that I simplified matters by working with only one field)

So then I thought "this can't be this easy!". So I checked it out.
What I wanted was Sums for 9/1/2012 06:00:00 to 9/2/2012 06:00:00 (etc.) - so I ran a check:



SELECT MIN([ReportDate]),SUM(([T15E_14_1])*1.0) AS SumVal
FROM 
   [R21AWW].[ww].[tblR21ABin1Data]
WHERE
   [R21AWW].[ww].[tblR21ABin1Data].[ReportDate] BETWEEN '9/1/2012 06:00:00' and '9/2/2012 06:00:00'



I Got:


TheDate                  SumVal
-----------------------  ----------------
2012-09-02 00:00:00.000	 386.599990844727



Furthermore, if I run the 2nd query from 09-02 to 09-03 (0600), I get the value 315.599990844727, TheDate reported as 2012-09-03 00:00:00.000.

So - Oh mighty oracle, how do I get the first query to run and report "TheDate" as the start of the period being summed? (In 2nd query, TheDate would be "2012-09-01 06:00:00"

Thanks Again VERY much!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-14 : 16:23:56
what about this?

SELECT DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])),0) as TheDate,SUM(([T15E_14_1])*1.0) AS SumVal
FROM 
   [R21AWW].[ww].[tblR21ABin1Data]
WHERE
   [R21AWW].[ww].[tblR21ABin1Data].[ReportDate] BETWEEN '9/1/2012' and '9/14/2012'
GROUP BY DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])),0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-14 : 16:29:33
Thank you again very much. I will play with this Monday - get back to you...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-14 : 16:32:14
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-17 : 12:10:49
Works Great!
Still shows TheDate as YYYY-MM-DD 00:00:00.000 (not as 06:00:00.000), but this is not real important.

Here's where I've gotten to:



SELECT 
   DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])),0) as TheDate,
   SUM(([T15E_14_1])*1.0) AS T15E141,
   Sum(([T15W_14_1])*1.0) AS T15W141,
   Sum(([T16E_14_1])*1.0) AS T16E141,
   Sum(([T16W_14_1])*1.0) AS T16W141,
   Sum(([TOS1_14_1])*1.0) AS TOS1141,
   Sum(([TOS2_14_1])*1.0) AS TOS2141,
   Sum(([TOS3_14_1])*1.0) AS TOS3141,
   Sum(([TOS4_14_1])*1.0) AS TOS4141,
   Sum(([TOS5_14_1])*1.0) AS TOS5141,
   Sum(([TOS6_14_1])*1.0) AS TOS6141,
   Sum(([TOS7_14_1])*1.0) AS TOS7141,
   Sum(([TOS8_14_1])*1.0) AS TOS8141,
   Sum(([TOS9_14_1])*1.0) AS TOS9141,
   Sum(([TOS10_14_1])*1.0) AS TOS10141,
   Sum(([TOS11_14_1])*1.0) AS TOS11141,
   MAX([Bin1BXType]) AS Bin1BXType,
   Sum(([T15E_15_1])*1.0) AS T15E151,
   Sum(([T15W_15_1])*1.0) AS T15W151,
   Sum(([T16E_15_1])*1.0) AS T16E151,
   Sum(([T16W_15_1])*1.0) AS T16W151,
   Sum(([TOS1_15_1])*1.0) AS TOS1151,
   Sum(([TOS2_15_1])*1.0) AS TOS2151,
   Sum(([TOS3_15_1])*1.0) AS TOS3151,
   Sum(([TOS4_15_1])*1.0) AS TOS4151,
   Sum(([TOS5_15_1])*1.0) AS TOS5151,
   Sum(([TOS6_15_1])*1.0) AS TOS6151,
   Sum(([TOS7_15_1])*1.0) AS TOS7151,
   Sum(([TOS8_15_1])*1.0) AS TOS8151,
   Sum(([TOS9_15_1])*1.0) AS TOS9151,
   Sum(([TOS10_15_1])*1.0) AS TOS10151,
   Sum(([TOS11_15_1])*1.0) AS TOS11151,
   Sum(([T12B_14_1])*1.0) AS T12B141,
   Sum(([T13B_15_1])*1.0) AS T13B151  
FROM 
   [R21AWW].[ww].[tblR21ABin1Data]
WHERE
   [R21AWW].[ww].[tblR21ABin1Data].[ReportDate] BETWEEN '6/1/2012' and '6/30/2012'
GROUP BY 
   DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])),0),
   [R21AWW].[ww].[tblR21ABin1Data].[Bin1BXType]



You will notice that I am now summing up by Date and Bin1BXType, which seems also to be working fine..

Here's my last question. I need to pull this into an Excel Spreadsheet, and I want to have the dates in the BETWEEN clause be parameters. For some reason, Excel cannot seem to parameterize a query this complex. I tried to use parameters on the query in the first post (above), and could never get it to work.

I googled and googled this (at the time), and it looked as if I was going to have to define this query as a UDF on the server. Is this the only way? Do you know how to make Excel do this w/o the UDF? Or is there another even better solution for Excel?

Thanks again VERY much for your help!!

Rob
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-17 : 13:06:13
i would have done this as SSRS report with required parameters and rendered it in excel format using &rs:Format=Excel URL parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-17 : 14:10:15
Would this allow the user in Excel to put the start and end dates into the spreadsheet and then get his data for the interval he desires?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-17 : 14:36:03
you can ask users to access the report from report server by passing required date values and then use export option available in reporting services to export it to excel as they want it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-17 : 14:48:36
This will be part of a larger spreadsheet project. We need to be able to have the user set the start/end dates into cells on the spreadsheet, then click a "Go" button (also on the spreadsheet). The spreadsheet would then use these dates for other queries to other databases/tables and pull this data froom multiple sources, including process data, which is not SQL server...

So it needs to be something that, from inside Excel, looks like a parameter query (to a view? a UDF? something).

I don't want to try and automate a solution that requires the user to execute a report that would get dropped onto a disk in a separate Excel file...

BTW - this is a monthly report.. (or at worst, Month to date).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-17 : 14:56:48
then best was to do it is to pass values via parameter to sql query which it passed to db and retrievs the appropriate result back in excel
see

http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-17 : 16:18:44
Excel will not allow me to pass parameters to SQL Server...

This is how I got to the idea that I might need a UDF.

I have built queries in VBA as strings and then passed them to a connection. I would like to simplify the VBA strings by creating views on the server. There are 8 tables like the one in the queries above, one for each of 8 bins.

So, I might create a view for "Bin 1" like this:


USE R21AWW

GO

CREATE VIEW vwBin1

As

SELECT 
   DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])),0) as "Date",
   1 as "Bin No.",
   SUM(([T15E_14_1])*1.0) AS T15E141,
   Sum(([T15W_14_1])*1.0) AS T15W141,
   Sum(([T16E_14_1])*1.0) AS T16E141,
   Sum(([T16W_14_1])*1.0) AS T16W141,
   Sum(([TOS1_14_1])*1.0) AS TOS1141,
   Sum(([TOS2_14_1])*1.0) AS TOS2141,
   Sum(([TOS3_14_1])*1.0) AS TOS3141,
   Sum(([TOS4_14_1])*1.0) AS TOS4141,
   Sum(([TOS5_14_1])*1.0) AS TOS5141,
   Sum(([TOS6_14_1])*1.0) AS TOS6141,
   Sum(([TOS7_14_1])*1.0) AS TOS7141,
   Sum(([TOS8_14_1])*1.0) AS TOS8141,
   Sum(([TOS9_14_1])*1.0) AS TOS9141,
   Sum(([TOS10_14_1])*1.0) AS TOS10141,
   Sum(([TOS11_14_1])*1.0) AS TOS11141,
   MAX([Bin1BXType]) AS Bin1BXType,
   Sum(([T15E_15_1])*1.0) AS T15E151,
   Sum(([T15W_15_1])*1.0) AS T15W151,
   Sum(([T16E_15_1])*1.0) AS T16E151,
   Sum(([T16W_15_1])*1.0) AS T16W151,
   Sum(([TOS1_15_1])*1.0) AS TOS1151,
   Sum(([TOS2_15_1])*1.0) AS TOS2151,
   Sum(([TOS3_15_1])*1.0) AS TOS3151,
   Sum(([TOS4_15_1])*1.0) AS TOS4151,
   Sum(([TOS5_15_1])*1.0) AS TOS5151,
   Sum(([TOS6_15_1])*1.0) AS TOS6151,
   Sum(([TOS7_15_1])*1.0) AS TOS7151,
   Sum(([TOS8_15_1])*1.0) AS TOS8151,
   Sum(([TOS9_15_1])*1.0) AS TOS9151,
   Sum(([TOS10_15_1])*1.0) AS TOS10151,
   Sum(([TOS11_15_1])*1.0) AS TOS11151,
   Sum(([T12B_14_1])*1.0) AS T12B141,
   Sum(([T13B_15_1])*1.0) AS T13B151  
FROM 
   [R21AWW].[ww].[tblR21ABin1Data]
GROUP BY 
   DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])),0),
   [R21AWW].[ww].[tblR21ABin1Data].[Bin1BXType]

GO



And then, in VBA I would have to pass the SQL String "Select * from vwBin1 Where [Date] BETWEEN '09-01-2012' and '09-15-2012'

What do you think of this?

Thanks

Rob
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-20 : 13:59:56
The offset for the rollup is not working.

For one thing, my "Date" field is Date only (no time), and there is a "Time" field. So I think this change would need to be made:

   DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate])),0),

Would have to become:

DATEADD(dd,DATEDIFF(dd,0,DATEADD(hh,-6,[R21AWW].[ww].[tblR21ABin1Data].[ReportDate] + CAST('00:00:00' AS DATETIME)),0),


Edit #2 Change above is not necessary. DateAdd works without adding time value to the field - don't know how I got to this conclusion.
However, what follows is still true

The thing is, no matter what I change the value "-6" to, I get the same sums - tried "-2", "-14", etc, etc.

Also, if I create views as proposed above, Excel allows me to use parameters for StartDate and EndDate in MS Query Wizard for queries (to the views), and then to make the Excel Queries get these parameters from spreadsheet cells. I'm just not rolling up the sums correctly in the views...


Thanks Again
Rob
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-20 : 16:26:52
Punting...
So this Query:

SELECT [ReportDate]
      ,[ReportTime]
      ,[ReportShift]
      ,DATEADD(hh,DATEDIFF(hh,0,[ReportDate])-6,0) AS TheTime
      ,DATEADD(hh,DATEDIFF(hh,0,DATEADD(hh,-6,[ReportDate] ))-2,0) AS ShiftTime
      ,[T15E_15_1]
  FROM [R21AWW].[ww].[tblR21ABin1Data]
Where [ReportDate] BETWEEN '08/31/2012' and '09/07/2012'
Order By [ReportDate], [ReportTime] ASC

Pulls some raw data and a couple of diagnostic time calcs (csv):

ReportDate,ReportTime,ReportShift,TheTime,ShiftTime,T15E_15_1 
2012-08-31 00:00:00.000,1900-01-01 06:05:59.000,A,2012-08-30 18:00:00.000,2012-08-30 16:00:00.000,81.4
2012-08-31 00:00:00.000,1900-01-01 14:06:00.000,B,2012-08-30 18:00:00.000,2012-08-30 16:00:00.000,75
2012-08-31 00:00:00.000,1900-01-01 22:05:59.000,C,2012-08-30 18:00:00.000,2012-08-30 16:00:00.000,0
2012-09-01 00:00:00.000,1900-01-01 06:05:59.000,A,2012-08-31 18:00:00.000,2012-08-31 16:00:00.000,0
2012-09-01 00:00:00.000,1900-01-01 14:06:00.000,B,2012-08-31 18:00:00.000,2012-08-31 16:00:00.000,107.5
2012-09-01 00:00:00.000,1900-01-01 22:05:59.000,C,2012-08-31 18:00:00.000,2012-08-31 16:00:00.000,370.9
2012-09-02 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-01 18:00:00.000,2012-09-01 16:00:00.000,0
2012-09-02 00:00:00.000,1900-01-01 14:05:58.000,B,2012-09-01 18:00:00.000,2012-09-01 16:00:00.000,0
2012-09-02 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-01 18:00:00.000,2012-09-01 16:00:00.000,0
2012-09-03 00:00:00.000,1900-01-01 06:06:00.000,A,2012-09-02 18:00:00.000,2012-09-02 16:00:00.000,0
2012-09-03 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-02 18:00:00.000,2012-09-02 16:00:00.000,0
2012-09-03 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-02 18:00:00.000,2012-09-02 16:00:00.000,20.8
2012-09-04 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-03 18:00:00.000,2012-09-03 16:00:00.000,21.6
2012-09-04 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-03 18:00:00.000,2012-09-03 16:00:00.000,24.9
2012-09-04 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-03 18:00:00.000,2012-09-03 16:00:00.000,0
2012-09-05 00:00:00.000,1900-01-01 06:06:00.000,A,2012-09-04 18:00:00.000,2012-09-04 16:00:00.000,23.1
2012-09-05 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-04 18:00:00.000,2012-09-04 16:00:00.000,0
2012-09-05 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-04 18:00:00.000,2012-09-04 16:00:00.000,0
2012-09-06 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-05 18:00:00.000,2012-09-05 16:00:00.000,613
2012-09-06 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-05 18:00:00.000,2012-09-05 16:00:00.000,401.4
2012-09-06 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-05 18:00:00.000,2012-09-05 16:00:00.000,628.2
2012-09-07 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-06 18:00:00.000,2012-09-06 16:00:00.000,57.8
2012-09-07 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-06 18:00:00.000,2012-09-06 16:00:00.000,0
2012-09-07 00:00:00.000,1900-01-01 22:06:00.000,C,2012-09-06 18:00:00.000,2012-09-06 16:00:00.000,0


You don't need "TheTime" or "ShiftTime"...

What I need is to roll up averages beginning at 14:00:00 one day until 14:00:00 the next.

So, using the data above, the average beginning 8/31/2012 14:00:00 would be 75.

I don't understand the math & science behind the group by DATEADD... thing.

Sorry to be so disorganised

Rob
Go to Top of Page

robnjay
Starting Member

14 Posts
Posted - 2012-09-20 : 16:28:17
quote:
Originally posted by robnjay

Punting...
So this Query:

SELECT [ReportDate]
      ,[ReportTime]
      ,[ReportShift]
      ,DATEADD(hh,DATEDIFF(hh,0,[ReportDate])-6,0) AS TheTime
      ,DATEADD(hh,DATEDIFF(hh,0,DATEADD(hh,-6,[ReportDate] ))-2,0) AS ShiftTime
      ,[T15E_15_1]
  FROM [R21AWW].[ww].[tblR21ABin1Data]
Where [ReportDate] BETWEEN '08/31/2012' and '09/07/2012'
Order By [ReportDate], [ReportTime] ASC

Pulls some raw data and a couple of diagnostic time calcs (csv):

ReportDate,ReportTime,ReportShift,TheTime,ShiftTime,T15E_15_1 
2012-08-31 00:00:00.000,1900-01-01 06:05:59.000,A,2012-08-30 18:00:00.000,2012-08-30 16:00:00.000,81.4
2012-08-31 00:00:00.000,1900-01-01 14:06:00.000,B,2012-08-30 18:00:00.000,2012-08-30 16:00:00.000,75
2012-08-31 00:00:00.000,1900-01-01 22:05:59.000,C,2012-08-30 18:00:00.000,2012-08-30 16:00:00.000,0
2012-09-01 00:00:00.000,1900-01-01 06:05:59.000,A,2012-08-31 18:00:00.000,2012-08-31 16:00:00.000,0
2012-09-01 00:00:00.000,1900-01-01 14:06:00.000,B,2012-08-31 18:00:00.000,2012-08-31 16:00:00.000,107.5
2012-09-01 00:00:00.000,1900-01-01 22:05:59.000,C,2012-08-31 18:00:00.000,2012-08-31 16:00:00.000,370.9
2012-09-02 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-01 18:00:00.000,2012-09-01 16:00:00.000,0
2012-09-02 00:00:00.000,1900-01-01 14:05:58.000,B,2012-09-01 18:00:00.000,2012-09-01 16:00:00.000,0
2012-09-02 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-01 18:00:00.000,2012-09-01 16:00:00.000,0
2012-09-03 00:00:00.000,1900-01-01 06:06:00.000,A,2012-09-02 18:00:00.000,2012-09-02 16:00:00.000,0
2012-09-03 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-02 18:00:00.000,2012-09-02 16:00:00.000,0
2012-09-03 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-02 18:00:00.000,2012-09-02 16:00:00.000,20.8
2012-09-04 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-03 18:00:00.000,2012-09-03 16:00:00.000,21.6
2012-09-04 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-03 18:00:00.000,2012-09-03 16:00:00.000,24.9
2012-09-04 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-03 18:00:00.000,2012-09-03 16:00:00.000,0
2012-09-05 00:00:00.000,1900-01-01 06:06:00.000,A,2012-09-04 18:00:00.000,2012-09-04 16:00:00.000,23.1
2012-09-05 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-04 18:00:00.000,2012-09-04 16:00:00.000,0
2012-09-05 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-04 18:00:00.000,2012-09-04 16:00:00.000,0
2012-09-06 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-05 18:00:00.000,2012-09-05 16:00:00.000,613
2012-09-06 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-05 18:00:00.000,2012-09-05 16:00:00.000,401.4
2012-09-06 00:00:00.000,1900-01-01 22:05:59.000,C,2012-09-05 18:00:00.000,2012-09-05 16:00:00.000,628.2
2012-09-07 00:00:00.000,1900-01-01 06:05:59.000,A,2012-09-06 18:00:00.000,2012-09-06 16:00:00.000,57.8
2012-09-07 00:00:00.000,1900-01-01 14:05:59.000,B,2012-09-06 18:00:00.000,2012-09-06 16:00:00.000,0
2012-09-07 00:00:00.000,1900-01-01 22:06:00.000,C,2012-09-06 18:00:00.000,2012-09-06 16:00:00.000,0


You don't need "TheTime" or "ShiftTime"...

What I need is to roll up averages beginning at 14:00:00 one day until 14:00:00 the next (I was wrong about the time).

So, using the data above, the average beginning 8/31/2012 14:00:00 would be 75.

I don't understand the math & science behind the group by DATEADD... thing.

Sorry to be so disorganised

Rob
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site