Help with Aggregating based on Date 1

[ehenry]

I have a query that outputs three columns, [Date], [Type], and [Volume] over the past seven days. I need to SUM [Volume] with [Type] = I or [Type] = W for each date, so I would have 7 dates rather than 14. each of the 7 with two entries, one for I and W.

Rather than this:
|Date |Type|Volume|
7/23/2010 I 200
7/23/2010 W -100

I Need:

|Date |Type|Volume|
7/23/2010 G 100

 

[RiverGuy]

If you are ONLY querying for Type I and W, just do this:

SELECT 
[Date]
,'G' AS [Type]
,SUM(Volumne) AS Volume
FROM YourTable
GROUP BY [Date]

If you are querying for other Types, but want to make I and W into G, then do this:

SELECT 
[Date]
,[Type] =
CASE 
  WHEN [Type] IN ('I', 'W') THEN 'G'
  ELSE [Type]
END
,SUM(Volume) AS Volume
FROM YourTable
GROUP BY
CASE 
  WHEN [Type] IN ('I', 'W') THEN 'G'
  ELSE [Type]
END

 

[r937]

SELECT [Date]
     , 'G' AS Type
     , SUM(Volume) AS Volume
  FROM (
       [red][i]put your query here[/i][/red]
       ) AS q
 WHERE Type IN ('I','W')
GROUP
    BY [Date]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ehenry]

I have attached an excel file so you can see the data I am working with. I have 4 rows highlighted, the ones in yellow would need to be combined into one row, and the same for the red. This would go for all rows in the table. As you can see Entries with Location type 'G' have 8 rows each, however if they have 'I' and 'W' there would be 16. I need to combine those 16 into 8 so I have a single date and the sum of the volume. for the two

I should have showed this before rather than trying to explain an easier version of it. I think this is more complex than grouping and cases, hopefully I am wrong. Thank you for all the help!

 

[r937]

i've got ms excel but apparently not a late enough version, as it cannot open your file

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ehenry]

Try this its in 97-2003 format now.

http://rms.sascoenergy.com/Flow.xls

 

[r937]

okay, i can read that just fine

however, i'm having a lot of trouble associating what i see with your question

if the question is "more complex than grouping and cases" then you haven't explained that complexity

did you try any of the queries suggested yet?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ehenry]

I have tried using what you suggested and it doesn't seem to be working. Below is the query I have that created the table I sent you:

SELECT S.FieldName, F.PointID, F.MeasurementDate, F.NominationCycle, F.LocationType,
CASE WHEN F.LocationType = 'W' THEN -F.ScheduledVolume ELSE F.ScheduledVolume END AS ScheduledVolume
FROM tblFlowData F INNER JOIN
SascoPoints S ON F.PointID = S.PointID
WHERE
(F.MeasurementDate BETWEEN DATEADD(DAY,-8,GETDATE()) AND GETDATE() AND F.NominationCycle = 'I2') OR
(F.MeasurementDate = CONVERT(VARCHAR(8), GETDATE(), 112))

This is my implementation of what you suggested:

SELECT FieldName, PointID, MeasurementDate, NominationCycle, 'G' as LocationType, SUM(ScheduledVolume) AS Volume
FROM (

SELECT S.FieldName, F.PointID, F.MeasurementDate, F.NominationCycle, F.LocationType,
CASE WHEN F.LocationType = 'W' THEN -F.ScheduledVolume ELSE F.ScheduledVolume END AS ScheduledVolume
FROM tblFlowData F INNER JOIN
SascoPoints S ON F.PointID = S.PointID
WHERE
(F.MeasurementDate BETWEEN DATEADD(DAY,-8,GETDATE()) AND GETDATE() AND F.NominationCycle = 'I2') OR
(F.MeasurementDate = CONVERT(VARCHAR(8), GETDATE(), 112))
) as Q
WHERE LocationType IN ('I','W')
Group By MeasurementDate, FieldName, PointID, NominationCycle
ORDER BY FieldName, PointID, MeasurementDate

 

[ehenry]

Sorry, There is something in the data I was not taking into consideration. If I remove the PointID your Query works great. Thank you.