phandlin
February 27th, 2008, 03:32 PM
With today's push to green, clients are trying to determine their utility consumption and any spikes in usage.
I'm trying to figure out the best query or formula to use with crystal report linked to an MS SQL database. I'm only looking at one table for each utility.
Fields are:
UW.STARTDATE - Start of Meter Reading
UW.ENDDATE - End of Meter Reading
UW.CONSUMP - Gallons used during Meter Reading
I want a CrossTab report that gives me a Years then Months across the top, and Gallons Used on the left, so that I can get a linear graph.
Note that sometimes there is up to 6 months between the start and end date of the bill.
*************************************
Cross tab
..................2006................................2007...............................2008
................|...1........2.......3......4....//.....1........2.......3......4...//.....1........2.......3
GALLONS..|
......start...|.............x
......end....|..............................x
......so span falls.............x
I'm trying to figure out the best query or formula to use with crystal report linked to an MS SQL database. I'm only looking at one table for each utility.
Fields are:
UW.STARTDATE - Start of Meter Reading
UW.ENDDATE - End of Meter Reading
UW.CONSUMP - Gallons used during Meter Reading
I want a CrossTab report that gives me a Years then Months across the top, and Gallons Used on the left, so that I can get a linear graph.
Note that sometimes there is up to 6 months between the start and end date of the bill.
*************************************
Cross tab
..................2006................................2007...............................2008
................|...1........2.......3......4....//.....1........2.......3......4...//.....1........2.......3
GALLONS..|
......start...|.............x
......end....|..............................x
......so span falls.............x