SUM function not working when using PIVOT

This is related to earlier question on "How-to-get-output-displaying-as-rows-to-columns".

The query below is using pivot function to display out columns and performing a SUM on interval data.

The pivot function works alright, but the SUMing appears to not work as the values are not adding up correctly.

Query:
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
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
                 select trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss'))  + (LEVEL - 1) as opday -- derive operating day where gv_firstdaystarttime = TRUNC(p_firstday) passed in by appworx                       
                   from dual
                 connect by level <= 100                     -- Get 100 dateoffset in case interval period is large over a year
               -- 
              ) Z
         where Z.opday <= trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400)                 -- as long as operating day is within the last day of quarter
        --
        )    
    
    SELECT *
    FROM
    (--   
           select  V.esiid as ESIID,
             V.repcode as REPCODE,
             V.repname as REPNAME,
             V.origin as ORIGIN,
             V.tradedate as tradedate,
            'CHANNEL' AS INTERVALDATASOURCE,                        
             V.interval_number as interval_number,
             V.interval_value as interval_value,
             sum(V.interval_value) AS DAILY_TOTALS
            from (--
              select 
               e.esiid as esiid,
               r.repcode as repcode,
               r.repname as repname,
               h.origin AS origin,
               h.starttime as blob_starttime,
               h.stoptime as blob_stoptime,
               O.FirstDayStarttime,
               O.FirstDayStoptime,
               O.LastDayStarttime,
               O.LastDayStoptime,
               O.opdaystart as tradedate,
               O.opdaystop,
               T.interval_value as interval_value,
               nvl(T.interval_number,0) as interval_number
              from esiid e
              join esiidservicehist esh ON (e.UIDESIID = esh.UIDESIID)
              join lschannelcutheader h ON (h.recorder = e.esiid)  -- to get esiid opt out information from channelcutheader
              join rep r ON (esh.REPCODE = r.repcode)
              join channel c ON (h.uidchannel = c.uidchannel AND c.channelnum = 4)  --  To Obtain channel 4 which is for REC Load process
              join lschannelcutdata d ON (h.uidchannelcut = d.UIDCHANNELCUT)  
              JOIN opdays O ON (1 = 1) -- Join for all operating days in the quarter
              join table(ndlstar.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,
              v.repcode,
              V.repname,
              V.origin,
              v.tradedate,
              v.interval_number,
              v.interval_value
     order by V.esiid,
              v.tradedate,
              v.repcode,
              v.interval_number
      --     
      ) X
      PIVOT
      (--
       MAX(INTERVAL_VALUE)
       FOR INTERVAL_NUMBER IN
          (--
             1 AS INTV1, 2 AS INTV2, 3 AS INTV3, 4 AS INTV4, 5 AS INTV5, 6 AS INTV6, 7 AS INTV7, 8 AS INTV8, 9 AS INTV9, 10 AS INTV10, 11 AS INTV11, 12 AS INTV12, 13 AS INTV13,
             14 AS INTV14, 15 AS INTV15, 16 AS INTV16, 17 AS INTV17, 18 AS INTV18, 19 AS INTV19, 20 AS INTV20, 21 AS INTV21, 22 AS INTV22, 23 AS INTV23, 24 AS INTV24, 25 AS INTV25,
             26 AS INTV26, 27 AS INTV27, 28 AS INTV28, 29 AS INTV29, 30 AS INTV30, 31 AS INTV31, 32 AS INTV32, 33 AS INTV33, 34 AS INTV34, 35 AS INTV35, 36 AS INTV36, 37 AS INTV37,
             38 AS INTV38, 39 AS INTV39, 40 AS INTV40, 41 AS INTV41, 42 AS INTV42, 43 AS INTV43, 44 AS INTV44, 45 AS INTV45, 46 AS INTV46, 47 AS INTV47, 48 AS INTV48, 49 AS INTV49,
             50 AS INTV50, 51 AS INTV51, 52 AS INTV52, 53 AS INTV53, 54 AS INTV54, 55 AS INTV55, 56 AS INTV56, 57 AS INTV57, 58 AS INTV58, 59 AS INTV59, 60 AS INTV60, 61 AS INTV61,
             62 AS INTV62, 63 AS INTV63, 64 AS INTV64, 65 AS INTV65, 66 AS INTV66, 67 AS INTV67, 68 AS INTV68, 69 AS INTV69, 70 AS INTV70, 71 AS INTV71, 72 AS INTV72, 73 AS INTV73,
             74 AS INTV74, 75 AS INTV75, 76 AS INTV76, 77 AS INTV77, 78 AS INTV78, 79 AS INTV79, 80 AS INTV80, 81 AS INTV81, 82 AS INTV82, 83 AS INTV83, 84 AS INTV84, 85 AS INTV85,
             86 AS INTV86, 87 AS INTV87, 88 AS INTV88, 89 AS INTV89, 90 AS INTV90, 91 AS INTV91, 92 AS INTV92, 93 AS INTV93, 94 AS INTV94, 95 AS INTV95, 96 AS INTV96, 97 AS INTV97,
             98 AS INTV98, 99 AS INTV99, 100 AS INTV100
           --
           )
       --     
       )       
       order by tradedate

Open in new window


Resulting Output
ESIID	REPCODE	REPNAME	                 ORIGIN	TRADEDATE	DATASOURCE	DAILY_TOTALS	INTV1	INTV2	INTV3
87400000003	198	    GDF 	        M	          1/1/2010	CHANNEL	        53.75      53.75	53.75	0
89500000001	198	     GDF 	        M	          1/1/2010	CHANNEL	        54.5	54.5	0              54.5
76400000000	198	     GDF 	        M	         1/1/2010	CHANNEL	        55.5	55.5	55.5        55.5	
98700000001	198	     GDF 	         M	         1/1/2010	CHANNEL	        55.75	 55.75     55.75	0	
87400000566	198	     GDF 	         M	         1/1/2010	CHANNEL	        56.25	 56.25	 56.25	56.25	
44000000005	198	     GDF 	         M	          1/1/2010	CHANNEL	        57.5	 57.5	  57.5	57.5

Open in new window


Expected Output
ESIID	 REPCODE	REPNAME	ORIGIN	TRADEDATE	DATASOURCE DAILY_TOTALS	INTV1	INTV2	INTV3
87400000003	198	   GDF 	     M	          1/1/2010	CHANNEL	       107.5 53.75 53.75 0
89500000001	198	    GDF 	    M	          1/1/2010	CHANNEL 109	 54.5 54.5 0
76400000000	198	    GDF 	    M	         1/1/2010	CHANNEL 166.5 55.5 55.5  55.5	
98700000001	198	    GDF        M	         1/1/2010	CHANNEL  111.5 55.75 55.75	0	
87400000566	198	    GDF 	    M	         1/1/2010	CHANNEL  168.75 56.25 56.25	56.25	
44000000005	198	    GDF 	     M	  1/1/2010	CHANNEL   172.5 57.5 57.5 57.5

Open in new window

steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I believe it is because you aren't summing what you think you are summing.  If you look at the SQL I provided in the other question, I manually added all the pivoted columns to compute the daily summary.

Just like the last question:
For me to provide copy/paste code, I'll need sample data to go with your expected results.

Referencing the previous related question:
https://www.experts-exchange.com/questions/29076837/How-to-get-output-displaying-as-rows-to-columns.html
steve2312Author Commented:
Here's sample data (relevant columns) of the tables.  


ESIID
UIDESIID  ESIID
6958397 10443720004952900
7933458 10443720006096300
6958399 10443720004153600
6958400 10443720009833800
16599511 10443720001325400
6958402 10443720002706200
6958403 10443720009955400
6958404 10443720004263200
7536106 10443720006426400
6958406 10443720003362000
6958407 10443720008160900
9681219 10443720004390900
7781577 10443720006588900
6958410 10443720001529900

Open in new window


 ESIIDservicehist table
UIDESIID  REPCODE
7535563   9
7359223   9
7832195   9
7598325   9
7359572   9
7897287   9
7890040   9
7368797   1
7536106   9
7598029   3
7896653   9
6958400   9
7888791   5
7370239   9
7599583   7
7376168   93
9681219   135
12513403  131
6941768   131
11889466  131
17317618  484
7650389   131
10000733  118
16397661  121
7791476   131
12616505  118
10000794  121
10575871  121
16423748  436
10626540  269
7667277   131
7781577   306
10564045  121
14891730  118
10052478  121
16599511  121
9413005   131
13662834  131

Open in new window


Rep table
REPCODE REPNAME
1 NAR
9 TXR
3 SWB
4 POO
5 PRD
93  LCRA
7  LSE

Open in new window



Sample lschannelcutheader table
UIDCHANNELCUT           RECORDER     UIDCHANNEL CHANNEL  SPI
1306797423  LMTRUNADJ_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1    7289620   5   900
1306797426  LMTRUNADJ_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5_SU1  7291729   5   900
1306797429  LSEGDL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2   7307840  5  900
1306797430  LSEGDL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5   7307837 4  900
1306797431  LSEGTL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2   7307841  4 900
1306797433  LSEGTL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5    7307839  4  900
1306797434  LMTRUNADJ_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1  7289622  4  900
1306797435  LMTRUNADJ_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5_SU1   7291733 4   900
1306797436  LSEGDL_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2  7307846  4    900
1306797438  LSEGDL_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5    7307842  4   900
1306797439  LSEGTL_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2   7307847 4    900
1306797440  LSEGTL_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5    7307843 4    900
1306797442  LMTRUNADJ_170_16_RESHIWR_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1  7289624  4 900

Open in new window



Channel table
UIDCHANNEL  UIDRECORDER CHANNELNUM
1           1           1
2           2           1
4           4           1
5           5           1
6           6           1
7           7           1
8           8           1
9           9           1
10          10          1
11          11          1
12          12          1
13          13          1
14          14          1

Open in new window


lschannelcutdata table
UIDCHANNELCUT VALUECODES
2197078522  <BLOB>
2197078523  <BLOB>
2197078524  <BLOB>
1306797429  <BLOB>
1306797442   <BLOB>
2197078527  <BLOB>
2197078686  <BLOB>
1306797426  <BLOB>
2197078580  <BLOB>
2197078581  <BLOB>
2197078668  <BLOB>
2197078669  <BLOB>
1306797440  <BLOB>

Open in new window

slightwv (䄆 Netminder) Commented:
Thanks for that but it is incomplete.

Can you post a select statement that only goes against those table and the current and expected results?

I will also need to know what ndlstar.unpack_blob_subset(d.valuecodes, h.starttime, O.opdaystart) does.

What I want to do is set up my own test case, run the query you provide to get your current results then modify the query you provide to get your expected results.  That way there is no guessing or confusion.

From what you provided, here is the test setup I created.  Add to it, take from it do anything you need to post me some working SQL based on the test setup.

Then from that, post what it returns and what you want the results to be.

/*
--drop table ESIID purge;
create table ESIID(UIDESIID number, ESIID number);
insert into esiid values(6958397,10443720004952900);
insert into esiid values(7933458,10443720006096300);
insert into esiid values(6958399,10443720004153600);
insert into esiid values(6958400,10443720009833800);
insert into esiid values(16599511,10443720001325400);
insert into esiid values(6958402,10443720002706200);
insert into esiid values(6958403,10443720009955400);
insert into esiid values(6958404,10443720004263200);
insert into esiid values(7536106,10443720006426400);
insert into esiid values(6958406,10443720003362000);
insert into esiid values(6958407,10443720008160900);
insert into esiid values(9681219,10443720004390900);
insert into esiid values(7781577,10443720006588900);
insert into esiid values(6958410,10443720001529900);
commit;

--drop table ESIIDservicehist purge;
create table ESIIDservicehist(UIDESIID number, REPCODE number);

insert into ESIIDservicehist values(7535563,9);
insert into ESIIDservicehist values(7359223,9);
insert into ESIIDservicehist values(7832195,9);
insert into ESIIDservicehist values(7598325,9);
insert into ESIIDservicehist values(7359572,9);
insert into ESIIDservicehist values(7897287,9);
insert into ESIIDservicehist values(7890040,9);
insert into ESIIDservicehist values(7368797,1);
insert into ESIIDservicehist values(7536106,9);
insert into ESIIDservicehist values(7598029,3);
insert into ESIIDservicehist values(7896653,9);
insert into ESIIDservicehist values(6958400,9);
insert into ESIIDservicehist values(7888791,5);
insert into ESIIDservicehist values(7370239,9);
insert into ESIIDservicehist values(7599583,7);
insert into ESIIDservicehist values(7376168,93);
insert into ESIIDservicehist values(9681219,135);
insert into ESIIDservicehist values(12513403,131);
insert into ESIIDservicehist values(6941768,131);
insert into ESIIDservicehist values(11889466,131);
insert into ESIIDservicehist values(17317618,484);
insert into ESIIDservicehist values(7650389,131);
insert into ESIIDservicehist values(10000733,118);
insert into ESIIDservicehist values(16397661,121);
insert into ESIIDservicehist values(7791476,131);
insert into ESIIDservicehist values(12616505,118);
insert into ESIIDservicehist values(10000794,121);
insert into ESIIDservicehist values(10575871,121);
insert into ESIIDservicehist values(16423748,436);
insert into ESIIDservicehist values(10626540,269);
insert into ESIIDservicehist values(7667277,131);
insert into ESIIDservicehist values(7781577,306);
insert into ESIIDservicehist values(10564045,121);
insert into ESIIDservicehist values(14891730,118);
insert into ESIIDservicehist values(10052478,121);
insert into ESIIDservicehist values(16599511,121);
insert into ESIIDservicehist values(9413005,131);
insert into ESIIDservicehist values(13662834,131);

--drop table rep purge;
create table rep(REPCODE number, REPNAME varchar2(4));
insert into rep values(1,'NAR');
insert into rep values(9,'TXR');
insert into rep values(3,'SWB');
insert into rep values(4,'POO');
insert into rep values(5,'PRD');
insert into rep values(93,'LCRA');
insert into rep values(7,'LSE');

--drop table lschannelcutheader purge;
create table lschannelcutheader(UIDCHANNELCUT number, RECORDER varchar2(500), UIDCHANNEL number, CHANNEL number, SPI number);
insert into lschannelcutheader values(1306797423,'LMTRUNADJ_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1',7289620,5,900);
insert into lschannelcutheader values(1306797426,'LMTRUNADJ_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5_SU1',7291729,5,900);
insert into lschannelcutheader values(1306797429,'LSEGDL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2',7307840,5,900);
insert into lschannelcutheader values(1306797430,'LSEGDL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5',7307837,4,900);
insert into lschannelcutheader values(1306797431,'LSEGTL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2',7307841,4,900);
insert into lschannelcutheader values(1306797433,'LSEGTL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5',7307839,4,900);
insert into lschannelcutheader values(1306797434,'LMTRUNADJ_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1',7289622,4,900);
insert into lschannelcutheader values(1306797435,'LMTRUNADJ_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5_SU1',7291733,4,900);
insert into lschannelcutheader values(1306797436,'LSEGDL_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2',7307846,4,900);
insert into lschannelcutheader values(1306797438,'LSEGDL_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5',7307842,4,900);
insert into lschannelcutheader values(1306797439,'LSEGTL_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2',7307847,4,900);
insert into lschannelcutheader values(1306797440,'LSEGTL_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5',7307843,4,900);
insert into lschannelcutheader values(1306797442,'LMTRUNADJ_170_16_RESHIWR_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1',7289624,4,900);


--drop table channel purge;
create table channel(UIDCHANNEL number, UIDRECORDER number, CHANNELNUM number);
insert into channel values(1,1,1);
insert into channel values(2,2,1);
insert into channel values(4,4,1);
insert into channel values(5,5,1);
insert into channel values(6,6,1);
insert into channel values(7,7,1);
insert into channel values(8,8,1);
insert into channel values(9,9,1);
insert into channel values(10,10,1);
insert into channel values(11,11,1);
insert into channel values(12,12,1);
insert into channel values(13,13,1);
insert into channel values(14,14,1);

--drop table lschannelcutdata purge;
create table lschannelcutdata (UIDCHANNELCUT number);
insert into lschannelcutdata values(2197078522);
insert into lschannelcutdata values(2197078523);
insert into lschannelcutdata values(2197078524);
insert into lschannelcutdata values(1306797429);
insert into lschannelcutdata values(1306797442);
insert into lschannelcutdata values(2197078527);
insert into lschannelcutdata values(2197078686);
insert into lschannelcutdata values(1306797426);
insert into lschannelcutdata values(2197078580);
insert into lschannelcutdata values(2197078581);
insert into lschannelcutdata values(2197078668);
insert into lschannelcutdata values(2197078669);
insert into lschannelcutdata values(1306797440);
commit;
*/

Open in new window

Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

steve2312Author Commented:
Here are selects for the first few tables and results from it.  Will send you the remaining tables shortly..

select * from esiid
where uidesiid in (6958397,7933458,6958399, 6958400, 16599511, 6958402, 6958403, 6958404,
7536106, 6958406 , 6958407, 9681219, 7781577, 6958410)

Open in new window


UIDESIID	ESIID	STARTTIME	STOPTIME	ADDTIME	LSUSER	LSTIME
6958397	10443720004952900	1/1/1971		4/2/2001 19:17		
6958399	10443720004153600	1/1/1971	6/4/2014 23:59	6/4/2014 18:17	MAESTRO	6/4/2014 18:17
6958400	10443720009833800	1/1/1971		4/2/2001 19:17		
6958402	10443720002706200	1/1/1971		4/2/2001 19:17		
6958403	10443720009955400	1/1/1971	12/3/2006 23:59	12/3/2006 11:53	STCADMIN	12/3/2006 11:53
6958404	10443720004263200	1/1/1971		4/2/2001 19:17		
6958406	10443720003362000	1/1/1971		4/2/2001 19:17		
6958407	10443720008160900	1/1/1971		4/2/2001 19:17		
6958410	10443720001529900	1/1/1971		4/2/2001 19:17		
7536106	1007806049024400	1/1/1971		4/3/2001 15:29		
7781577	10443720002135100	1/1/1971		4/3/2001 20:54		
7933458	100780606486901	1/1/1971		4/4/2001 5:53		
9681219	10032789443275300	1/1/1971		4/6/2001 4:25		
16599511	10443720009487400	2/26/2013		2/26/2013 6:44	MAESTRO	2/26/2013 6:44

Open in new window


select * from esiidservicehist
where uidesiid in (6958397,7933458,6958399, 6958400, 16599511, 6958402, 6958403, 6958404,
7536106, 6958406 , 6958407, 9681219, 7781577, 6958410)

Open in new window


UIDESIID	SERVICECODE	STARTTIME	STOPTIME	REPCODE
6958397	ELE	2/4/2011		131
6958399	ELE	2/18/2010	6/4/2014 23:59	
6958399	ELE	6/5/2014		
6958400	ELE	2/15/2012		180
6958402	ELE	11/12/2012	4/6/2016 23:59	131
6958402	ELE	4/7/2016		181
6958403	ELE	12/4/2006		
6958404	ELE	11/18/2010		131
6958406	ELE	12/18/2009		131
6958407	ELE	12/20/2012		266
6958410	ELE	8/24/2012	7/13/2015 23:59	131
6958410	ELE	7/14/2015		131
7536106	ELE	1/1/1971		9
7781577	ELE	7/26/2012	5/14/2015 23:59	131

Open in new window

select * from lschannelcutheader
[code]
UIDCHANNELCUT	RECORDER	CHANNEL	STARTTIME	STOPTIME	UIDCHANNEL	SPI	INTERVALCOUNT
1306797423	LMTRUNADJ_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1	5	6/4/2007	6/4/2007 23:59	7289620	900	96
1306797426	LMTRUNADJ_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5_SU1	5	6/4/2007	6/4/2007 23:59	7291729	900	96
1306797429	LSEGDL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2	5	6/4/2007	6/4/2007 23:59	7307840	900	96
1306797430	LSEGDL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5	5	6/4/2007	6/4/2007 23:59	7307837	900	96
1306797431	LSEGTL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2	5	6/4/2007	6/4/2007 23:59	7307841	900	96
1306797433	LSEGTL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5	5	6/4/2007	6/4/2007 23:59	7307839	900	96
1306797434	LMTRUNADJ_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1	5	6/4/2007	6/4/2007 23:59	7289622	900	96
1306797435	LMTRUNADJ_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5_SU1	5	6/4/2007	6/4/2007 23:59	7291733	900	96
1306797436	LSEGDL_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2	5	6/4/2007	6/4/2007 23:59	7307846	900	96
1306797438	LSEGDL_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5	5	6/4/2007	6/4/2007 23:59	7307842	900	96
1306797439	LSEGTL_170_16_NMLIGHT_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2	5	6/4/2007	6/4/2007 23:59	7307847	900	96
1306797440	LSEGTL_197_73_NMFLAT_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5	5	6/4/2007	6/4/2007 23:59	7307843	900	96
1306797442	LMTRUNADJ_170_16_RESHIWR_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1	5	6/4/2007	6/4/2007 23:59	7289624	900	96
1306797444	LSEGDL_170_16_RESHIWR_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2	5	6/4/2007	6/4/2007 23:59	7307851	900	96

Open in new window

slightwv (䄆 Netminder) Commented:
I'm not sure we are talking about the same thing.  I don't need the selects from the individual tables.

I want sample data, current results and expected results.  Then I can load that up on my database, execute the select you post to get the current results you post.  I can take that and modify the select to provide the expected results.

I provided the create table and insert statements from the original data you provided.

The issue is, I cannot run your original select you posted against it to retrieve the original results you posted.  Therefore, I have no way to create the SQL you want to give the expected results.


To say it another way:
In you original question you posted a "Query:".

You posted "Resulting Output".

I need EVERYTHING necessary to run your "Query" to generate what you posted as "Resulting Output".   Until I get that I cannot begin to generate what you want as "Expected Output".

Since you likely cannot post the actual package code, dummy up a test case that is really close to what you need.  Then when I post SQL, you can tweak it to your actual configuration.  The closer your sample is to your original, the close my SQL will be to what you need.
steve2312Author Commented:
Thanks for detailed explanation slightvw.   Using your test data I have modified the query to reflect below

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
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
                 select trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss'))  + (LEVEL - 1) as opday -- derive operating day where gv_firstdaystarttime = TRUNC(p_firstday) passed in by appworx                       
                   from dual
                 connect by level <= 1000                     -- Get 1000 dateoffset in case interval period is large over a year
               -- 
              ) Z
         where Z.opday <= trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400)                 -- as long as operating day is within the last day of quarter
        --
        )    
              select 
               e.esiid as esiid,
               r.repcode as repcode,
               r.repname as repname,
               h.origin AS origin,
               O.opdaystart as tradedate,
               T.interval_number as interval_number,
               T.interval_value as interval_value
              from esiid e
              join esiidservicehist esh ON (e.UIDESIID = esh.UIDESIID)
              join lschannelcutheader h ON (h.recorder = e.esiid)  -- to get esiid opt out information from channelcutheader
              join rep r ON (esh.REPCODE = r.repcode)
              join channel c ON (h.uidchannel = c.uidchannel AND c.channelnum = 4)  --  To Obtain channel 4 which is for REC Load process
              join lschannelcutdata d ON (h.uidchannelcut = d.UIDCHANNELCUT)  
              JOIN opdays O ON (1 = 1) -- Join for all operating days in the quarter
              join table(ndlstar.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   
             and e.ESIID in ('1008901000144570017100','10443720004952900',
                            '10443720006096300',
                            '10443720004153600',
                            '10443720009833800',
                            '10443720001325400',
                            '10443720002706200',
                            '10443720009955400',
                            '10443720004263200',
                            '10443720006426400',
                            '10443720003362000',
                            '10443720008160900',
                            '10443720004390900',
                            '10443720006588900',
                            '10443720001529900')    
               and r.repcode IN (1,5,7,9,93,118,121,131,135,269,306,436,485) and (interval_number between 1 and 100)

Open in new window


This gives the below result

ESIID	REPCODE	REPNAME	ORIGIN	TRADEDATE	INTERVAL_NUMBER	INTERVAL_VALUE
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	1	42.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	2	40.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	3	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	4	40.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	5	39
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	6	38.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	7	37.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	8	37
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	9	37
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	10	36
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	11	35.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	12	36
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	13	36.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	14	34
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	15	34.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	16	34
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	17	33
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	18	33.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	19	34
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	20	34
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	21	34
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	22	33.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	23	33.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	24	34.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	25	33.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	26	35
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	27	35
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	28	35.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	29	35
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	30	35.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	31	35.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	32	36.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	33	36
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	34	36.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	35	38
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	36	38.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	37	38.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	38	42.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	39	44.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	40	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	41	40.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	42	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	43	40.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	44	43.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	45	46
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	46	45.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	47	45.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	48	45
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	49	44.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	50	41.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	51	41.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	52	42.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	53	43.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	54	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	55	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	56	40.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	57	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	58	42.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	59	43.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	60	42.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	61	41.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	62	43.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	63	45.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	64	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	65	40.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	66	39.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	67	39.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	68	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	69	41.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	70	44.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	71	47.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	72	49
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	73	49
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	74	50
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	75	50.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	76	52.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	77	51.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	78	49.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	79	50
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	80	50
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	81	50.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	82	51.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	83	50.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	84	48.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	85	49
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	86	47.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	87	47.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	88	47
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	89	45.5
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	90	44.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	91	46
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	92	45
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	93	43.75
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	94	43.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	95	41.25
1008901000144570000000	485	SOURCE POWER AND GAS LLC DBA SOURCE POWER AND GAS LLC RT (LSE)	M	1/1/2010	96	40.75

Open in new window


Now, the expected results should be the same data set above and 2 additional points.

1. should be in pivot form- this is already done.
2. the Interval values for a given date should SUMmed up (there are 96 intervals and the totals for a given trade date should add). From the above results that would be say for given trade date 1/1/2010 it should be (42.75 + 40.75 + 41.25 + .... until last interval number (in this case the last interval number is 96)) . To state the above,  I have provided an illustration of expected results below where the interval data is showing up correctly for a set of esiid and reps but the summed value is adding up.

Expected results

DAILY_TOTALS	INTV1	INTV2	INTV3	INTV4	INTV5	INTV6	INTV7	INTV8	INTV9
1275	15.26	15.12	14.83	15.12	14.83	15.26	14.98	14.83	14.98
1275	57.5	55	55.5	57.5	71	59.75	55	77.25	62.75
1275	7.2	7.2	7.2	9	7.2	7.2	7.2	9	7.2
1275	0	0	0	0	0	0	0	0	0
1275	15.24	14.64	15.06	15	15.06	15	15	14.82	15.3
1275	15.24	14.64	15.06	15	15.06	15	15	14.82	15.3
1275	310.8	308.4	318	308.4	304.8	312.6	305.4	301.8	285
1275	588.6618	581.0636	583.0631	575.8648	571.4659	580.6637	581.0636	586.2624	577.4645
1275	1258.9666	1266.1117	1256.1086	1230.3862	1210.38	1251.8215	1290.4051	1308.9823	1303.2663
1275	1258.9666	1266.1117	1256.1086	1230.3862	1210.38	1251.8215	1290.4051	1308.9823	1303.2663
1275	1.512	1.524	1.5	1.5	1.512	1.5	1.5	1.488	1.5
1275	1.512	1.524	1.5	1.5	1.512	1.5	1.5	1.488	1.5
1275	39.45	39.75	38.7	38.4	39.6	37.2	37.5	37.5	36.9
1275	1.8	1.8	1.8	1.8	1.8	1.8	1.8	1.8	1.8
1275	1.8	1.8	1.8	1.8	1.8	1.8	1.8	1.8	1.8
1275	453.6	453.6	449.28	449.28	449.28	444.96	453.6	449.28	462.24
1275	453.6	453.6	449.28	449.28	449.28	444.96	453.6	449.28	462.24
1275	455.04	457.92	452.16	466.56	478.08	466.56	463.68	475.2	463.68
1275	455.04	457.92	452.16	466.56	478.08	466.56	463.68	475.2	463.68
1275	1.35	1.8	1.35	1.35	1.35	1.35	1.35	1.35	1.35
1275	1.35	1.8	1.35	1.35	1.35	1.35	1.35	1.35	1.35
1275	125.325	131.175	124.2	136.575	135.675	130.5	130.275	129.6	134.325
1275	1620.341	1655.062	1686.89	1666.636	1643.488	1643.488	1646.382	1655.062	1640.595
1275	728.775	731.7	721.35	720.9	707.85	704.025	692.775	701.325	692.55
1275	42.75	40.75	41.25	40.25	39	38.5	37.5	37	37

Open in new window

slightwv (䄆 Netminder) Commented:
Your new expected results show daily totals all 1275.  I'm guessing that is a typo and I should focus on the last line (line 26 in the expected results)?
steve2312Author Commented:
Thats correct, its a typo. The daily_totals value should be a sum of INTV1 + INTV2+......+INTV96
slightwv (䄆 Netminder) Commented:
Using only my test case setup, I still cannot run your query.

Your query still uses:
table(ndlstar.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
steve2312Author Commented:
I played around the query a bit more, this time with the PIVOT to get the same output. The line 29 where the sum happens is the problem and does not add up the values of interval. From the look of data, it seems to list out the max value of the interval instead of adding them all up?

Here's the modified query ..

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
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                trunc(to_date('1/10/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                trunc(to_date('1/10/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
                 select trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss'))  + (LEVEL - 1) as opday -- derive operating day where gv_firstdaystarttime = TRUNC(p_firstday) passed in by appworx                       
                   from dual
                 connect by level <= 1000                     -- Get 1000 dateoffset in case interval period is large over a year
               -- 
              ) Z
         where Z.opday <= trunc(to_date('1/10/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400)                 -- as long as operating day is within the last day of quarter
        --
        )  
           SELECT *
            FROM
            (--   
                 select  V.esiid as ESIID,
                 V.repcode as REPCODE,
                 V.repname as REPNAME,
                 V.origin as ORIGIN,
                 V.tradedate as tradedate,
                'LSCHANNEL' AS INTERVALDATASOURCE,                        
                 V.interval_number as interval_number,
                 V.interval_value as interval_value
                 sum(interval_value) as daily_total  
                 from (--   
                        select 
                         e.esiid as esiid,
                         r.repcode as repcode,
                         r.repname as repname,
                         h.origin AS origin,
                         O.opdaystart as tradedate,
                         T.interval_number as interval_number,
                         T.interval_value as interval_value
                        from esiid e
                        join esiidservicehist esh ON (e.UIDESIID = esh.UIDESIID)
                        join lschannelcutheader h ON (h.recorder = e.esiid)  -- to get esiid opt out information from channelcutheader
                        join rep r ON (esh.REPCODE = r.repcode)
                        join channel c ON (h.uidchannel = c.uidchannel AND c.channelnum = 4)  --  To Obtain channel 4 which is for REC Load process
                        join lschannelcutdata d ON (h.uidchannelcut = d.UIDCHANNELCUT)  
                        JOIN opdays O ON (1 = 1) -- Join for all operating days in the quarter
                        join table(ndlstar.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   
                        and e.ESIID in ('1008901000144570017100','10443720004952900',
                                      '10443720006096300',
                                      '10443720004153600',
                                      '10443720009833800',
                                      '10443720001325400',
                                      '10443720002706200',
                                      '10443720009955400',
                                      '10443720004263200',
                                      '10443720006426400',
                                      '10443720003362000',
                                      '10443720008160900',
                                      '10443720004390900',
                                      '10443720006588900',
                                      '10443720001529900')    
                        and r.repcode IN (1,5,7,9,93,118,121,131,135,269,306,436,485) and (interval_number between 1 and 100)
                      --
                      ) V   
               group by V.esiid,
                        v.repcode,
                        V.repname,
                        V.origin,
                        v.tradedate,
                        v.interval_number,
                        v.interval_value
               order by V.esiid,
                        v.tradedate,
                        v.repcode,
                        v.interval_number 
                --     
                )x
                PIVOT
                (--
                 MAX(INTERVAL_VALUE)
                 FOR INTERVAL_NUMBER IN
                    (--
                       1 AS "INTV1", 2 AS "INTV2", 3 AS "INTV3", 4 AS "INTV4", 5 AS "INTV5", 6 AS "INTV6", 7 AS "INTV7", 8 AS "INTV8", 9 AS "INTV9", 10 AS "INTV10", 11 AS "INTV11",  12 AS "INTV12", 13 AS "INTV13",
                       14 AS "INTV14", 15 AS "INTV15", 16 AS "INTV16", 17 AS "INTV17", 18 AS "INTV18", 19 AS "INTV19", 20 AS "INTV20", 21 AS "INTV21", 22 AS "INTV22", 23 AS "INTV23", 24 AS "INTV24", 25 AS "INTV25",
                       26 AS "INTV26", 27 AS "INTV27", 28 AS "INTV28", 29 AS "INTV29", 30 AS "INTV30", 31 AS "INTV31", 32 AS "INTV32", 33 AS "INTV33", 34 AS "INTV34", 35 AS "INTV35", 36 AS "INTV36", 37 AS "INTV37",
                       38 AS "INTV38", 39 AS "INTV39", 40 AS "INTV40", 41 AS "INTV41", 42 AS "INTV42", 43 AS "INTV43", 44 AS "INTV44", 45 AS "INTV45", 46 AS "INTV46", 47 AS "INTV47", 48 AS "INTV48", 49 AS "INTV49",
                       50 AS "INTV50", 51 AS "INTV51", 52 AS "INTV52", 53 AS "INTV53", 54 AS "INTV54", 55 AS "INTV55", 56 AS "INTV56", 57 AS "INTV57", 58 AS "INTV58", 59 AS "INTV59", 60 AS "INTV60", 61 AS "INTV61",
                       62 AS "INTV62", 63 AS "INTV63", 64 AS "INTV64", 65 AS "INTV65", 66 AS "INTV66", 67 AS "INTV67", 68 AS "INTV68", 69 AS "INTV69", 70 AS "INTV70", 71 AS "INTV71", 72 AS "INTV72", 73 AS "INTV73",
                       74 AS "INTV74", 75 AS "INTV75", 76 AS "INTV76", 77 AS "INTV77", 78 AS "INTV78", 79 AS "INTV79", 80 AS "INTV80", 81 AS "INTV81", 82 AS "INTV82", 83 AS "INTV83", 84 AS "INTV84", 85 AS "INTV85",
                       86 AS "INTV86", 87 AS "INTV87", 88 AS "INTV88", 89 AS "INTV89", 90 AS "INTV90", 91 AS "INTV91", 92 AS "INTV92", 93 AS "INTV93", 94 AS "INTV94", 95 AS "INTV95", 96 AS "INTV96", 97 AS "INTV97",
                       98 AS "INTV98", 99 AS "INTV99", 100 AS "INTV100"
                     --
                     )
                    
                 --     
                 )       
                 order by tradedate;
          

Open in new window

slightwv (䄆 Netminder) Commented:
>> it seems to list out the max value of the interval instead of adding them all up?

That is where I believe the problem is but wanted to confirm before posting.  I mentioned it above that you likely don't use SUM any more.  If you go back to your previous question, I replace it with manually adding them.

I believe that is what you need to do here:
INTV1+INTV2+INTV3 daily_total  

Using ALL the pivoted columns of course...
steve2312Author Commented:
Tried that before reverting to the SUM option.  

I get an error ORA-00904 invalid identifier (I only used INTV1+INTV2+INTV3+  ... to test it and it gives ORA-00904: "INTV3": invalid identifier. It does not recognise the INTV sets probably due to the way the selects are nested??
slightwv (䄆 Netminder) Commented:
>>reverting to the SUM option.  

There was a reason I changed SUM for adding:  SUM no longer did what you wanted.

>> It does not recognise the INTV sets probably due to the way the selects are nested??

As long as the alias in the PIVOT matches the column in the outer query and doesn't have any typos, it should work.

I cannot say "why" it doesn't work.  It did with the test case I set up in the other question.

For me to say with 100% accuracy, I need to run the query on my end.  To do that, I need a test case I can set up.  Only then can I post 100% tested code.
steve2312Author Commented:
Could any modification be done to the below (like change MAX(interval_value) to a SUM or any other aggregate function)?


PIVOT
                (--
                 MAX(INTERVAL_VALUE)
                 FOR INTERVAL_NUMBER IN
                    (--
                       1 AS "INTV1", 2 AS "INTV2", 3 AS "INTV3", 4 AS "INTV4", 5 AS "INTV5", 6 AS "INTV6", 7 AS "INTV7", 8 AS "INTV8", 9 AS "INTV9", 10 AS "INTV10", 11 AS "INTV11",  12 AS "INTV12", 13 AS "INTV13",
                       14 AS "INTV14", 15 AS "INTV15", 16 AS "INTV16", 17 AS "INTV17", 18 AS "INTV18", 19 AS "INTV19", 20 AS "INTV20", 21 AS "INTV21", 22 AS "INTV22", 23 AS "INTV23", 24 AS "INTV24", 25 AS "INTV25",
                       26 AS "INTV26", 27 AS "INTV27", 28 AS "INTV28", 29 AS "INTV29", 30 AS "INTV30", 31 AS "INTV31", 32 AS "INTV32", 33 AS "INTV33", 34 AS "INTV34", 35 AS "INTV35", 36 AS "INTV36", 37 AS "INTV37",
                       38 AS "INTV38", 39 AS "INTV39", 40 AS "INTV40", 41 AS "INTV41", 42 AS "INTV42", 43 AS "INTV43", 44 AS "INTV44", 45 AS "INTV45", 46 AS "INTV46", 47 AS "INTV47", 48 AS "INTV48", 49 AS "INTV49",
                       50 AS "INTV50", 51 AS "INTV51", 52 AS "INTV52", 53 AS "INTV53", 54 AS "INTV54", 55 AS "INTV55", 56 AS "INTV56", 57 AS "INTV57", 58 AS "INTV58", 59 AS "INTV59", 60 AS "INTV60", 61 AS "INTV61",
                       62 AS "INTV62", 63 AS "INTV63", 64 AS "INTV64", 65 AS "INTV65", 66 AS "INTV66", 67 AS "INTV67", 68 AS "INTV68", 69 AS "INTV69", 70 AS "INTV70", 71 AS "INTV71", 72 AS "INTV72", 73 AS "INTV73",
                       74 AS "INTV74", 75 AS "INTV75", 76 AS "INTV76", 77 AS "INTV77", 78 AS "INTV78", 79 AS "INTV79", 80 AS "INTV80", 81 AS "INTV81", 82 AS "INTV82", 83 AS "INTV83", 84 AS "INTV84", 85 AS "INTV85",
                       86 AS "INTV86", 87 AS "INTV87", 88 AS "INTV88", 89 AS "INTV89", 90 AS "INTV90", 91 AS "INTV91", 92 AS "INTV92", 93 AS "INTV93", 94 AS "INTV94", 95 AS "INTV95", 96 AS "INTV96", 97 AS "INTV97",
                       98 AS "INTV98", 99 AS "INTV99", 100 AS "INTV100"
                     --
                     )

Open in new window



Also, can we use a sub query instead of the IN (1 as intv1 , 2 as intv2....)?
slightwv (䄆 Netminder) Commented:
>>Could any modification be done to the below (like change MAX(interval_value) to a SUM or any other aggregate function)?

You can try anything.  I don't think it will work with SUM but I'm wrong a LOT these days...

>>Also, can we use a sub query instead of the IN (1 as intv1 , 2 as intv2....)?

Not for what you want.  Subquery with PIVOT is only valid with the XML generation syntax.
steve2312Author Commented:
In your earlier  (reference to "https://www.experts-exchange.com/questions/29076837/How-to-get-output-displaying-as-rows-to-columns.html"), you  created a test table with a number data type and adding the values in your illustration and the sum worked.

Here, I think the calculation is not working as this would be a  non- numeric computation (INTV1+INTV2+...) instead of adding numerical values associated to that interval.  Its a hunch at this point, I need to run some tests.
slightwv (䄆 Netminder) Commented:
>>would be a  non- numeric computation

If INTERVAL_VALUE isn't numbers, what is it?
steve2312Author Commented:
Yes, when we use sum(t.interval_value) as daily_total it is numeric as the  interval_value is numeric.

My statement for non-numeric was for the the concatenation  "INTV1+ INTV2+INTV3 dailytotal, instead of adding the actual numeric values
slightwv (䄆 Netminder) Commented:
+ in Oracle isn't a concatenation.  It is addition.  I hope you didn't add double quotes to it, then it becomes an Object Name.

Try it with:
select 1+2+3+4+5 from dual;

SQL> select 1+2+3+4+5 from dual;

 1+2+3+4+5
----------
        15

Open in new window


Did you not actually run my test case in the previous question to see it in action?
Mark WillsTopic AdvisorCommented:
I think you need to exclude v.interval_value from your (inner) group by

In fact, I would be inclined to remove that group by entirely.

Pivot is going to do the aggregations for you... Basically

select columns to display,  e.g. esiid , repcode, repname, origin, tradedate,
       column you want aggregated, e.g interval_value
       column you want to transform into horizontal list of columns e.g. interval_number
PIVOT
      aggregate column, e.g MAX(internal_value)       -- or SUM() -- whatever aggregate you want
      for column to transform e.g  interval_number
      in a list of columns e.g  IN (1 as intv1 , 2 as intv2....)

Then you can select from that, and the problem is you are doing select * from (...). Now, if the above is 'the pivot' then what you want to do is

select esiid , repcode, repname, origin, tradedate,
           intv1 + intv2 + intv3 + .... + intv99 as total_hours,
          intv1,intv2,intv3,.....intv99
from
      ('the pivot')
order by esiid , repcode, repname, origin, tradedate

which is basically what slightwv has been suggesting. so maybe a broader overview might assist the explanations.

does that help ?
steve2312Author Commented:
Thanks Mark!   Here's what I tried but it fails with a ORA-00904 error ON LINE 39.

Select columns to display, interval data et al then PIVOT with aggregation and removing group by clause. Finally selecting from this list.  

I get INTV100 invalid identifier ORA-00904 error. Somehow it does not recognize INTV obtained from the PIVOT.

 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
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                trunc(to_date('1/31/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                trunc(to_date('1/31/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
                 select trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss'))  + (LEVEL - 1) as opday -- derive operating day where gv_firstdaystarttime = TRUNC(p_firstday) passed in by appworx                       
                   from dual
                 connect by level <= 1000                     -- Get 1000 dateoffset in case interval period is large over a year
               -- 
              ) Z
         where Z.opday <= trunc(to_date('1/31/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400)                 -- as long as operating day is within the last day of quarter
        --
        )  
           SELECT *
            FROM
            (--   
                        (select 
                         e.esiid as esiid,
                         r.repcode as repcode,
                         r.repname as repname,
                         h.origin AS origin,
                         O.opdaystart as tradedate,
                         T.interval_number as interval_number,
                         T.interval_value as interval_value,
                         INTV1+INTV2+INTV3+INTV4+INTV5+INTV6+INTV7+INTV8+INTV9+INTV10+
                         INTV11+INTV12+INTV13+INTV14+INTV15+INTV16+INTV17+INTV18+INTV19+
                         INTV20+INTV21+INTV22+INTV23+INTV24+INTV25+INTV26+INTV27+INTV28+
                         INTV29+INTV30+INTV31+INTV32+INTV33+INTV34+INTV35+INTV36+INTV37+
                         INTV38+INTV39+INTV40+INTV41+INTV42+INTV43+INTV44+INTV45+INTV46+
                         INTV47+INTV48+INTV49+INTV50+INTV51+INTV52+INTV53+INTV54+INTV55+
                         INTV56+INTV57+INTV58+INTV59+INTV60+INTV61+INTV62+INTV63+INTV64+
                         INTV65+INTV66+INTV67+INTV68+INTV69+INTV70+INTV71+INTV72+INTV73+
                         INTV74+INTV75+INTV76+INTV77+NTV78+INTV79+INTV80+INTV81+INTV82+
                         INTV83+INTV84+INTV85+INTV86+ INTV87+INTV88+INTV89+INTV90+INTV91+
                         INTV92+INTV93+INTV94+INTV95+INTV96+INTV97+INTV98+INTV99+INTV100 daily_total
                        from esiid e
                        join esiidservicehist esh ON (e.UIDESIID = esh.UIDESIID)
                        join lschannelcutheader h ON (h.recorder = e.esiid)  -- to get esiid opt out information from channelcutheader
                        join rep r ON (esh.REPCODE = r.repcode)
                        join channel c ON (h.uidchannel = c.uidchannel AND c.channelnum = 4)  --  To Obtain channel 4 which is for REC Load process
                        join lschannelcutdata d ON (h.uidchannelcut = d.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 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 contains at least 1
                        and (e.starttime <= h.starttime and nvl(e.stoptime, h.stoptime) >= h.stoptime) -- Interval 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   
                 )
                 PIVOT
                (-- pivot starts
                 MAX(INTERVAL_VALUE)
                 FOR INTERVAL_NUMBER IN    
                    (-- interval list starts 
                       1 AS INTV1,  2 AS INTV2,  3 AS INTV3, 4 AS INTV4, 5 AS INTV5, 6 AS INTV6, 7 AS  INTV7 , 8 AS INTV8, 9 AS INTV9, 10 AS INTV10, 11 AS  INTV11 ,  12 AS  INTV12 , 13 AS  INTV13 ,
                       14 AS INTV14, 15 AS  INTV15 , 16 AS  INTV16 , 17 AS  INTV17 , 18 AS  INTV18 , 19 AS  INTV19 , 20 AS  INTV20 , 21 AS  INTV21 , 22 AS  INTV22 , 23 AS  INTV23 , 24 AS  INTV24 , 25 AS  INTV25 ,
                       26 AS INTV26, 27 AS  INTV27 , 28 AS  INTV28 , 29 AS  INTV29 , 30 AS  INTV30 , 31 AS  INTV31 , 32 AS  INTV32 , 33 AS  INTV33 , 34 AS  INTV34 , 35 AS  INTV35 , 36 AS  INTV36 , 37 AS  INTV37 ,
                       38 AS INTV38, 39 AS  INTV39 , 40 AS  INTV40 , 41 AS  INTV41 , 42 AS  INTV42 , 43 AS  INTV43 , 44 AS  INTV44 , 45 AS  INTV45 , 46 AS  INTV46 , 47 AS  INTV47 , 48 AS  INTV48 , 49 AS  INTV49 ,
                       50 AS INTV50, 51 AS  INTV51 , 52 AS  INTV52 , 53 AS  INTV53 , 54 AS  INTV54 , 55 AS  INTV55 , 56 AS  INTV56 , 57 AS  INTV57 , 58 AS  INTV58 , 59 AS  INTV59 , 60 AS  INTV60 , 61 AS  INTV61 ,
                       62 AS INTV62, 63 AS  INTV63 , 64 AS  INTV64 , 65 AS  INTV65 , 66 AS  INTV66 , 67 AS  INTV67 , 68 AS  INTV68 , 69 AS  INTV69 , 70 AS  INTV70 , 71 AS  INTV71 , 72 AS  INTV72 , 73 AS  INTV73 ,
                       74 AS INTV74, 75 AS  INTV75 , 76 AS  INTV76 , 77 AS  INTV77 , 78 AS  INTV78 , 79 AS  INTV79 , 80 AS  INTV80 , 81 AS  INTV81 , 82 AS  INTV82 , 83 AS  INTV83 , 84 AS  INTV84 , 85 AS  INTV85 ,
                       86 AS INTV86, 87 AS  INTV87 , 88 AS  INTV88 , 89 AS  INTV89 , 90 AS  INTV90 , 91 AS  INTV91 , 92 AS  INTV92 , 93 AS  INTV93 , 94 AS  INTV94 , 95 AS  INTV95 , 96 AS  INTV96 , 97 AS  INTV97 ,
                       98 AS INTV98, 99 AS  INTV99 , 100 AS  INTV100
                      --
                      )--end of interval list
                  ) --end of pivot
                )X -- outer select

Open in new window

Mark WillsTopic AdvisorCommented:
you have put it in the wrong place. You are still doing SELECT *


select             ---- should be here   line 18 in code snippet and MUST be only those columns returned

from
           (
            select       --- you added the columns here, BUT we dont know what they are until AFTER the Pivot
            from
            where
           )
pivot
           (
             aggregate
             for column
             in (column list)
           )

order by


In the above scenario, the "gotcha" is you must know the result names. In that regard, it is sometimes easier to encapsulate the above within a query ie

SELECT x.*, INTV1+INTV2+...+INTV99 as TOTALS
FROM
(  
    select *
    from
           (
            select      
            from
            where
           )
   pivot
           (
             aggregate
             for column
             in (column list)
           )
) x
order by

But the secret is knowing the resulting column names. And if you want to control the sequence of columns, you have to spell them out.

Also, you might need to check for NULL values when adding the columns together

So, get your pivot query working first as a basic PIVOT, then we can add in the outer select columns afterwards.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
To ad to what Mark posted:
Please follow the straw-man query I posted in your original question.  It has the syntax you need.  You just need to plug your actual tables and values around the PIVOT and daily_total syntax.
Mark WillsTopic AdvisorCommented:
Just had a look at the previous question and slightwv has provided a good basis, just dont do the create table stuff and follow the query.
https://www.experts-exchange.com/questions/29076837/How-to-get-output-displaying-as-rows-to-columns.html#a42425427
steve2312Author Commented:
Thanks for the pointers. I moved the select as below and the values are adding up.  

SELECT x.*, INTV1+INTV2+...+INTV99 as TOTALS
FROM
(  
    select *
    from
           ( 
            select      
            from
            where
           )
   pivot
           ( 
             aggregate
             for column
             in (column list)
           )
) x
order by

Open in new window


There were null values so add the NVL function to the outer select and testing it

  SELECT X.esiid, X.REPCODE, X.REPNAME, X.ORIGIN, X.TRADEDATE, 
                  NVL(INTV1,0)+NVL(INTV2,0)+ NVL(INTV3,0)+ NVL(INTV4,0)+NVL(INTV5,0)+NVL(INTV6,0)+NVL(INTV7,0)+NVL(INTV8,0)+NVL(INTV9,0)+NVL(INTV10,0)+....

Open in new window

steve2312Author Commented:
Thank you for help and being patient with your suggestions to getting this resolved.
Mark WillsTopic AdvisorCommented:
An absolute pleasure and so very pleased you were able to get your Pivot working.

Always enjoy slightwv's code, he is a remarkable expert, and I for one, am always learning from slightwv...

Regards,
Mark Wills
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.