November 5, 2014 at 10:05 am
Hi Guys. I need your help to do a query.
I have a table with the following values:
TimeStamp Energy_KWPowerMeter
01/10/2014 00:00:001 CJ201
01/10/2014 00:00:005 CJ202
01/10/2014 00:15:002 CJ201
01/10/2014 00:15:006 CJ202
01/10/2014 18:00:005 CJ201
01/10/2014 18:00:008 CJ202
01/10/2014 21:00:008 CJ201
01/10/2014 21:00:0012 CJ202
02/10/2014 00:00:0010 CJ201
02/10/2014 00:00:0013 CJ202
02/10/2024 00:15:0010 CJ201
02/10/2024 00:15:0013 CJ202
02/10/2024 18:00:0015 CJ201
02/10/2024 18:00:0016 CJ202
02/10/2024 21:00:0020 CJ201
02/10/2024 21:00:0021 CJ202
03/10/2014 00:00:0025 CJ201
03/10/2014 00:00:0026 CJ202
03/10/2024 00:15:0025 CJ201
03/10/2024 00:15:0026 CJ202
03/10/2024 18:00:0030 CJ201
03/10/2024 18:00:0031 CJ202
03/10/2024 21:00:0035 CJ201
03/10/2024 21:00:0036 CJ202
04/10/2014 00:00:0040 CJ201
04/10/2014 00:00:0041 CJ202
31/10/2014 00:00:0090 CJ201
31/10/2014 00:00:0091 CJ202
31/10/2024 00:15:0090 CJ201
31/10/2024 00:15:0091 CJ202
31/10/2024 18:00:0095 CJ201
31/10/2024 18:00:0096 CJ202
31/10/2024 21:00:00100 CJ201
31/10/2024 21:00:00110 CJ202
01/11/2014 00:00:00150 CJ201
01/11/2014 00:00:00160 CJ202
I need to make a report with subtotals for an interval of one month of consumption.
In the report should be grouped for a subtotal consumption per day and the end of the report the sum of each day.
So far so good.
My doubt is that during the day from 00:00 until 06:00 pm the tariff rates is "X". From 06:00 pm until 9:00 pm the tariff rates is "Z" and from 09:00 pm to 00:00 the value back to "X".
I mean, the subtotal per day should actually be two subtotals. One being for the consumption of tariff rates "X" and one for the tariff rates "Z".
And the sum at the end of the month should also be the total daily rate "X" and rate "Z".
I put a file in excel attachment to facilitate understanding.
Can anyone help me?
Sincerely
Andre Paschoal
November 5, 2014 at 10:28 am
I'd use a computed column (or if you dont want to use a temp table, you can just select the expression) to define whether the tariff rate is "X" or "Z" using a CASE statement.
Then, if you want to have multiple aggregation groups, I'd look into GROUPING SETS
if object_id('tempdb.dbo.#dat') is not null drop table #dat
create table #dat
(
TheDate datetime,
KW int,
PowerMeter char(5),
TariffGroup as case when datepart(hour, TheDate) between 21 and 24 then 'Z' else 'X' end
)
;with t (TheDate, KW, PowerMeter) as
(
select '01/10/2014 00:00:00', 1, 'CJ201'
union all select '01/10/2014 00:00:00', 5, 'CJ202'
union all select '01/10/2014 00:15:00', 2, 'CJ201'
union all select '01/10/2014 00:15:00', 6, 'CJ202'
union all select '01/10/2014 18:00:00', 5, 'CJ201'
union all select '01/10/2014 18:00:00', 8, 'CJ202'
union all select '01/10/2014 21:00:00', 8, 'CJ201'
union all select '01/10/2014 21:00:00', 12, 'CJ202'
union all select '02/10/2014 00:00:00', 10, 'CJ201'
union all select '02/10/2014 00:00:00', 13, 'CJ202'
union all select '02/10/2024 00:15:00', 10, 'CJ201'
union all select '02/10/2024 00:15:00', 13, 'CJ202'
union all select '02/10/2024 18:00:00', 15, 'CJ201'
union all select '02/10/2024 18:00:00', 16, 'CJ202'
union all select '02/10/2024 21:00:00', 20, 'CJ201'
union all select '02/10/2024 21:00:00', 21, 'CJ202'
union all select '03/10/2014 00:00:00', 25, 'CJ201'
union all select '03/10/2014 00:00:00', 26, 'CJ202'
union all select '03/10/2024 00:15:00', 25, 'CJ201'
union all select '03/10/2024 00:15:00', 26, 'CJ202'
union all select '03/10/2024 18:00:00', 30, 'CJ201'
union all select '03/10/2024 18:00:00', 31, 'CJ202'
union all select '03/10/2024 21:00:00', 35, 'CJ201'
union all select '03/10/2024 21:00:00', 36, 'CJ202'
union all select '04/10/2014 00:00:00', 40, 'CJ201'
union all select '04/10/2014 00:00:00', 41, 'CJ202'
union all select '31/10/2014 00:00:00', 90, 'CJ201'
union all select '31/10/2014 00:00:00', 91, 'CJ202'
union all select '31/10/2024 00:15:00', 90, 'CJ201'
union all select '31/10/2024 00:15:00', 91, 'CJ202'
union all select '31/10/2024 18:00:00', 95, 'CJ201'
union all select '31/10/2024 18:00:00', 96, 'CJ202'
union all select '31/10/2024 21:00:00', 100, 'CJ201'
union all select '31/10/2024 21:00:00', 110, 'CJ202'
union all select '01/11/2014 00:00:00', 150, 'CJ201'
union all select '01/11/2014 00:00:00', 160, 'CJ202'
)
insert into #dat
(
TheDate,
KW,
PowerMeter
)
select
TheDate = convert(datetime, TheDate, 103),
KW,
PowerMeter
from t
select
TheDate,
TariffGroup,
PowerMeter,
sum(KW)
from #dat
group by grouping sets
(
(PowerMeter, TheDate),
(PowerMeter, TheDate, TariffGroup)
)
November 5, 2014 at 11:02 am
Hi Jee.
I'll try it.
But and the summary? If you see the attachment, I need to calculate the consumption by day of the energy rate "X" and "Z" and after that, sum the consumption at end of month.
November 5, 2014 at 11:06 am
Sorry, I missed that part. But similarly to how you can group on the derived column for the Tariff Group, you can group on a derived column for the month. It depends on how complicated your report needs to be (for instance if you need to restrict months that are incomplete or something) but at its most simple, something like this might work
group by grouping sets (-- ....
(datepart(month, TheDate), PowerMeter
)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply