Help with query please

[ideasworking]

Hello,

I would like some help with a query. I have a fairly simple table with three fields 1)DateAndTime 2)Total_A 3) Total_B.
Total_A and Total_B are incrementing values so at 9 AM the value of Total_A might be 1000 and at 11 PM the value might be 1234 and so on...

What I would like to retrieve with the query is a daily summary. To do this I need to subtract the beginning of day value from the end of day value.

The results of the query would look something like this.

SummaryDay Daily_Total_A Daily_Total_B
11/1/2006 1234 8763
11/2/2006 1982 7823

Can this be done with a single query and if so how?

Thanks,
Lou

 

[gmmastros]

This 'almost' works. It doesn't work because Summary day has a time component. Once you strip the time component from the day, then it will work.

Select SummaryDay,
       Max(Total_A) - Min(Total_A) As Daily_Total_A,
       Max(Total_B) - Min(Total_B) As Daily_Total_B
From   Table
Group By SummaryDay

In SQL Server, this query would actually be...

Select DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0) As SummaryDay,
       Max(Total_A) - Min(Total_A) As Daily_Total_A,
       Max(Total_B) - Min(Total_B) As Daily_Total_B
From   Table
Group By DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

Something like this ?
SELECT CAST(DateAndTime AS DATE) SummaryDay, MAX(Total_A)-MIN(Total_A) Daily_Total_A, MAX(Total_B)-MIN(Total_B) Daily_Total_B
FROM yourTable
GROUP BY CAST(DateAndTime AS DATE)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886