| Author |
 Topic  |
|
|
robnjay
Starting Member
14 Posts |
 Posted - 09/14/2012 : 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 |
Edited by - robnjay on 09/14/2012 11:41:43
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 09/14/2012 : 11:30:36
|
SELECT MIN(datefield),AVG(datafield*1.0) AS AvgVal
FROM Table
GROUP BY DATEADD(hh,DATEDIFF(hh,0,datefield)-6,0)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/14/2012 : 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!! |
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/14/2012 : 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!! |
Edited by - robnjay on 09/14/2012 16:17:25 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 09/14/2012 : 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/
|
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/14/2012 : 16:29:33
|
| Thank you again very much. I will play with this Monday - get back to you... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 09/14/2012 : 16:32:14
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/17/2012 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 09/17/2012 : 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/
|
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/17/2012 : 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 09/17/2012 : 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/
|
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/17/2012 : 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). |
Edited by - robnjay on 09/17/2012 14:58:04 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/17/2012 : 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 |
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/20/2012 : 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 |
Edited by - robnjay on 09/20/2012 14:20:20 |
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/20/2012 : 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 |
|
|
|
robnjay
Starting Member
14 Posts |
Posted - 09/20/2012 : 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
|
|
|
| |
Topic |
|
|
|
Roll up Daily Sums with Start of day 0600
