How to get a single total value for all ESIID from the query

The query gets ESIID interval data from lschannel cut datasource and has gives out kilo watt hour daily totals by esiid.  I am wanting to know if a single mega watt hour total for all ESIIDs can be achieved, using the below query?

The value for Mega Watt hour total = kilo watt hour total / 1000.

What I have in mind is to add a SUM(V.interval_value/1000) in the outer select after daily kwh totals are computed and call it MWH total?  Would that be correct assumption?

   WITH opdays as
       ( --
         select opday as opdaystart,                           -- opday is derived from the inner Select query
                (opday + 1) - (1 / 86400) as opdaystop,       -- opdaystop is derived by adding a day and subtract a second from that day
                gv_firstDaystarttime as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                gv_firstDaystoptime as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                gv_lastdaystarttime as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                gv_lastdaystoptime as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
                 select gv_firstdaystarttime + (LEVEL - 1) as opday -- derive operating day where gv_firstdaystarttime = TRUNC(p_firstday) passed in by appworx                       
                   from dual
                 connect by level <= 10000                    -- Get 10000 dateoffset in case interval period is large over a year
              ) Z
         where Z.opday <= gv_LastdayStoptime                  -- as long as operating day is within the last day of quarter
    select gv_execution_id AS EXECUTION_ID,                   -- global execution id system generated value from a database sequence
           gv_partition_key AS PARTITION_KEY,                 -- partition key generated by a routine outside the package
           V.esiid as ESIID,                                                     -- get ESIID value 
           V.FirstDayStarttime AS FIRSTDAY,
           V.LastDayStarttime AS LASTDAY,
           V.opdaystart AS STARTTIME,
           V.opdaystop AS STOPTTIME,
           'LSCHANNEL' AS INTERVALDATASOURCE,                        -- LSChannel as Interval Datasource
           SUM(V.interval_value) as TOTALS,                         -- daily kwh totals by esiid
           'N' AS STATUS                                             -- Inserted New records will always have a Status of 'N' (New) 
    from (--
              e.esiid as esiid,
              h.starttime as blob_starttime,
              h.stoptime as blob_stoptime,
              from esiid e
              join lschannelcutheader h ON (h.recorder = e.esiid)  -- to get esiid opt out information 
              join channel c ON (h.uidchannel = c.uidchannel AND c.channelnum = 4)  --  To Obtain channel 4 for 
              join lschannelcutdata d ON (h.uidchannelcut = c.uidchannelcut)  
              join opdays O on (1 = 1) -- Join for all operating days in the quarter
              join table(unpack_blob_subset(d.valuecodes, h.starttime, O.opdaystart)) T ON (1 = 1) -- Extract All Intervals for a Single OpDay from a Multi-Day Interval Blob
              where h.spi = 900 -- unpack_blob_subset function works only for 15-minute data i.e. spi 900
              and (h.starttime <= O.LastdayStoptime and h.stoptime > O.FirstDayStarttime) -- Interval Blob contains at least 1 Opday of the Quarter
              and (e.starttime <= h.starttime and nvl(e.stoptime, h.stoptime) >= h.stoptime) -- Interval Blob falls within (start, stop) for ESIID optout
              and (o.opdaystart >= h.starttime and O.opdaystop <= nvl(h.stoptime, O.opdaystop)) -- Interval Blob contains Opday being considered
              and (e.starttime <= O.opdaystart and nvl(e.stoptime, O.opdaystop) >= O.opdaystop) -- ESIID is active for the OpDay
         ) V   
     group by V.esiid,
     order by V.esiid,

Open in new window

Who is Participating?
NerdsOfTechTechnology ScientistCommented:

Open in new window

is MWh's then

Open in new window

would give you kWh's; just make sure the math is done outside of the paratheses of SUM()

Thus, line 26 of your query could become:
SUM(V.interval_value)/1000 as TOTALS,                         -- daily MWh totals by esiid

Open in new window

slightwv (䄆 Netminder) Commented:
I really don't have the time to walk through your SQL and understand the tables and columns then try to understand what it is doing.

From the description, a SUM seems like the right thing.  I'm just not sure it needs to be wrapped in an outer query.  If it works for you and provides the results you want, then use it.

If you can provide a simplified test case with sample data, a simplified version of the query you have and the desired results you want, we can probably help you out.
steve2312Author Commented:
I ran a test with interval dates and the SUM (interval_value)/1000 worked.  Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.