Calculate daily usage by group using a query for export to excel

[capjlp]

Hi I have some data I need to calculate daily usage by customer.


I need this so I can export to excel for further work to be done on it.
My data in my table looks like this.


ID,CustomerNumber, Date,Reading
1,1,1/1/2010,57162.312
2,1,1/2/2010,57219.9984
3,1,1/3/2010,57269.304
4,1,1/4/2010,57358.8864
5,1,1/5/2010,0
6,1,1/6/2010,57542.9976
7,2,1/1/2010,3686.015
8,2,1/3/2010,3742.465
9,2,1/5/2010,0
10,2,1/6/2010,3846.456


Some days might be missing or 0 so that makes it a bit difficult for me. But I need to calculate the daily usage which would be the reading minus the day before's reading if its a negative number I need it to return 0 not a negative number. It would then need to calculate the usage on the last reading > 0 I have a few thousand records to calculate and several hundred customers.
In this example it should return


CustomerNumber,Date,Usage
1,1/2/2010,57.6864
1,1/3/2010,49.3056 ect....
When it hits a 0
1,1/5/2010,0
1,1/6/2010,184.1112
and so when it get to next customer it needs to then calculate the usage for them and so on.
Thanks for the help!

[June7]

You need each record to pull value from previous record. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

SELECT Table1.*, (SELECT TOP 1 Dupe.Reading FROM Table1 AS Dupe WHERE Dupe.CustomerNumber=Table1.CustomerNumber AND Dupe.ReadDate<Table1.ReadDate ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue FROM Table1;

Now build another query that does calcs with the two fields or export to Excel and do there.

Of course, this query can do nothing about dates that do not have a record. If you need a record for every date, that significantly complicates.

Why are there records with 0 reading? Perhaps those should be excluded from the inner and outer queries.

SELECT Table1.*, (SELECT TOP 1 Dupe.Reading FROM Table1 AS Dupe WHERE Dupe.CustomerNumber=Table1.CustomerNumber AND Dupe.ReadDate<Table1.ReadDate AND Dupe.Reading<>0 ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue FROM Table1 WHERE Reading<>0;


Date is a reserved word. Should not use reserved words as names.

[capjlp]

Sweet thank you for getting back with me. That works.

My date field is actually called READDATETIME but I simplified the field names for the example.

I just ran into an issue on some days we have one or more readings. In most cases I am seeing the same reading. I think I want to the last reading for the day to do the usage calculation.

Whats the best way to limit it to only show the last reading for each day for each customer?

Thank you

[June7]

Criminy! Never encountered that before. Why are you permitting multiple readings in a day? What do you mean 'same reading' - why would the reading be the same?

I think maybe if you add criteria to the ORDER BY will handle this.

ORDER BY Dupe.ReadDate DESC, Dupe.Reading DESC, Dupe.ID) AS PriorValue FROM Table1 WHERE Reading<>0;

[capjlp]

System does a Voltage Check on some customers every day so the second reading is just a result of that. Thanks for the help!