Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQl query to build a list of interval days (11.2.0.4)
PL/SQl query to build a list of interval days [message #665696] Fri, 15 September 2017 11:41 Go to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
Working Oracle version 11.2.0.4.

This query is listing out interval data for a single oper@ting day. In this case - April 1st.

How would I make the query to build this list of interval data for entire quarter (Apr 1 thru Jun 30)?


WITH opdays as
(
  select to_date('4/1/2010', 'mm/dd/yyyy') as opdaystart, to_date('4/1/2010, 23:59:59', 'mm/dd/yyyy hh24:mi:ss') as opdaystop
  from dual
)
select d.uidchannelcut, h.recorder, h.starttime, h.stoptime, d.valuecodes, h.spi, o.opdaystart, o.opdaystop, T.interval_number, T.interval_value
  from resourceid r, channel c, billdeterminant b, lschannelcutheader h, lschannelcutdata d, opdays o,
  table(unpack_blob_subset(d.valuecodes, h.starttime, opdaystart)) T
  where r.uidbilldeterminant = b.uidbilldeterminant
  and r.uidchannel = c.uidchannel
  and r.resourceid=h.recorder
  and h.uidchannel=c.uidchannel
  and d.uidchannelcut=h.uidchannelcut
  and h.starttime <= '30-Jun-2010'
  and h.stoptime > '01-Apr-2010'
  and r.starttime <= h.starttime
  and nvl(r.stoptime,sysdate)>=h.stoptime
  and o.opdaystart >= h.starttime
  and o.opdaystop <= nvl(h.stoptime, o.opdaystop)
  order by 2,3;


Re: PL/SQl query to build a list of interval days [message #665697 is a reply to message #665696] Fri, 15 September 2017 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 25752
Registered: January 2009
Location: SoCal
Senior Member
We don't have your tables.
We don't have your data.
We don't have your requirements.

We can't run post SQL to see what results.


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
Re: PL/SQl query to build a list of interval days [message #665701 is a reply to message #665697] Fri, 15 September 2017 12:53 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
Requirement:-The requirement is to retrieve interval data for the entire quarter. The SQL below is for 1 day of interval data in a quarter. I would need to generalize that SQL (not sure how, but an iterative way) to include entire quarter information.

Tables:- There are 5 tables with structure below. The 6th table is a Select from DUAL.


Data : Sample data is attached in a csv file showing result of the query and expected result of the final query.

1. Resource :
CREATE TABLE "RESOURCEID" 
   (	"RESOURCEID" VARCHAR2(64) NOT NULL ENABLE, 
	"GENSITECODE" VARCHAR2(64), 
	"NOIECODE" VARCHAR2(64), 
	"DCTIECODE" VARCHAR2(64), 
	"ACTIECODE" VARCHAR2(64), 
	"LCFACTOR" FLOAT(52), 
	"STARTTIME" DATE NOT NULL ENABLE, 
	"STOPTIME" DATE, 
	"ADDTIME" DATE, 
	"PGCCODE" VARCHAR2(64), 
	"UIDBILLDETERMINANT" NUMBER(10,0) NOT NULL ENABLE, 
	"UIDCHANNEL" NUMBER(19,0) NOT NULL ENABLE, 
	"SUBGENCODE" VARCHAR2(64), 
	"MRECODE" VARCHAR2(64), 
	"TDSPCODE" VARCHAR2(64), 
	"NOIETIECODE" VARCHAR2(64), 
	"LAARSITECODE" VARCHAR2(64), 
	"LSUSER" VARCHAR2(64) DEFAULT USER, 
	"LSTIME" DATE DEFAULT SYSDATE, 
	"DISPATCHASSETCODE" VARCHAR2(64), 
	"METERTYPECODE" VARCHAR2(64), 
	"METERGROUP" CHAR(1), 
	 CONSTRAINT "CK_RESRCEID_TIME" CHECK (STOPTIME >= STARTTIME) ENABLE NOVALIDATE, 
	 CONSTRAINT "PK_RESRCEID" PRIMARY KEY ("UIDCHANNEL", "RESOURCEID", "STARTTIME"),
         CONSTRAINT "FK_RESRCEID_BILLD" FOREIGN KEY ("UIDBILLDETERMINANT")
	  REFERENCES "BILLDETERMINANT" ("UIDBILLDETERMINANT") ENABLE NOVALIDATE, 
         CONSTRAINT "FK_RESRCEID_CHAN" FOREIGN KEY ("UIDCHANNEL")
	  REFERENCES "CHANNEL" ("UIDCHANNEL") ENABLE NOVALIDATE;

2. Channel
CREATE TABLE "CHANNEL" 
   (	"UIDCHANNEL" NUMBER(19,0) NOT NULL ENABLE, 
	"UIDRECORDER" NUMBER(19,0) NOT NULL ENABLE, 
	"CHANNELNUM" NUMBER(5,0) NOT NULL ENABLE, 
	"ADDTIME" DATE, 
	"LSUSER" VARCHAR2(64) DEFAULT USER, 
	"LSTIME" DATE DEFAULT SYSDATE, 
	 CONSTRAINT "CK_CHAN_UIDCHANNEL" CHECK (UIDCHANNEL > 0 and UIDCHANNEL < 9223372036854775808) ENABLE NOVALIDATE, 
	 CONSTRAINT "CK_CHAN_UIDRECORDE" CHECK (UIDRECORDER > 0 and UIDRECORDER < 9223372036854775808) ENABLE NOVALIDATE, 
	 CONSTRAINT "PK_CHAN" PRIMARY KEY ("UIDCHANNEL")
         CONSTRAINT "FK_CHAN_RCDR" FOREIGN KEY ("UIDRECORDER")
	  REFERENCES "RECORDER" ("UIDRECORDER") ON DELETE CASCADE ENABLE NOVALIDATE)


3. billdeterminant
CREATE TABLE "BILLDETERMINANT" 
   (	"UIDBILLDETERMINANT" NUMBER(5,0) NOT NULL ENABLE, 
	"BILLDETERMCODE" VARCHAR2(64) NOT NULL ENABLE, 
	"IDENTIFIER" VARCHAR2(32) NOT NULL ENABLE, 
	"BILLDETERMNAME" VARCHAR2(64) NOT NULL ENABLE, 
	"UOMCODE" VARCHAR2(64) NOT NULL ENABLE, 
	"BILLHISTCOLNAME" VARCHAR2(32), 
	"AGGREGATE" CHAR(1), 
	"DATATYPE" VARCHAR2(64), 
	"STARTTIME" DATE, 
	"STOPTIME" DATE, 
	"ADDTIME" DATE, 
	"DATAREQUIREMENT" VARCHAR2(3), 
	"SEVERITY" VARCHAR2(4), 
	"CMZONEFLAG" CHAR(1), 
	"PUBLICEXTRACT" CHAR(1), 
	"BILLDETERMTYPECODE" VARCHAR2(64), 
	"LSUSER" VARCHAR2(64) DEFAULT USER, 
	"LSTIME" DATE DEFAULT SYSDATE, 
	 CONSTRAINT "CK_BILLDET_AGGR" CHECK (AGGREGATE in ('T', 'A', 'M')) ENABLE NOVALIDATE, 
	 CONSTRAINT "CK_BILLDET_TIME" CHECK (STOPTIME is null or STOPTIME > STARTTIME) ENABLE NOVALIDATE, 
	 CONSTRAINT "CK_BILLDET_CMZFL" CHECK (CMZONEFLAG is null or CMZONEFLAG in ('Y', 'N')) ENABLE NOVALIDATE, 
	 CONSTRAINT "CK_BILLDET_DATATYP" CHECK (DATATYPE is null or DATATYPE in ('ESIID', 'TRANS', 'CNTR', 'STL', 'LOAD', 'GEN', 'CSC', 'FCTR')) ENABLE NOVALIDATE, 
	 CONSTRAINT "CK_BILLDET_PUBLICEXTRACT" CHECK (PUBLICEXTRACT in('Y','N','I')) ENABLE NOVALIDATE, 
	 CONSTRAINT "PK_BILLDET" PRIMARY KEY ("UIDBILLDETERMINANT")


4. Table lschannelcutheader

CREATE TABLE "LSCHANNELCUTHEADER" 
   (	"UIDCHANNELCUT" NUMBER(19,0) NOT NULL ENABLE, 
	"RECORDER" VARCHAR2(64) NOT NULL ENABLE, 
	"CHANNEL" NUMBER(5,0) NOT NULL ENABLE, 
	"STARTTIME" DATE NOT NULL ENABLE, 
	"STOPTIME" DATE NOT NULL ENABLE, 
	"UIDCHANNEL" NUMBER(19,0) NOT NULL ENABLE, 
	"SPI" NUMBER(10,0) NOT NULL ENABLE, 
	"UOMCODE" VARCHAR2(64) NOT NULL ENABLE, 
	"DSTPARTICIPANT" CHAR(1), 
	"TIMEZONE" NUMBER(5,0), 
	"ORIGIN" CHAR(1), 
	"STARTREADING" FLOAT(52), 
	"STOPREADING" FLOAT(52), 
	"METERMULTIPLIER" FLOAT(52), 
	"METEROFFSET" FLOAT(52), 
	"PULSEMULTIPLIER" FLOAT(52), 
	"PULSEOFFSET" FLOAT(52), 
	"EDITED" CHAR(1), 
	"INTERNALVALIDATION" CHAR(1), 
	"EXTERNALVALIDATION" CHAR(1), 
	"MERGEFLAG" CHAR(1), 
	"DELETEFLAG" CHAR(1), 
	"VALFLAGE" CHAR(1), 
	"VALFLAGI" CHAR(1), 
	"VALFLAGO" CHAR(1), 
	"VALFLAGN" CHAR(1), 
	"TKWRITTENFLAG" CHAR(1), 
	"DCFLOW" CHAR(1), 
	"ACCEPTREJECTSTATUS" CHAR(2), 
	"TRANSLATIONTIME" DATE, 
	"DESCRIPTOR" VARCHAR2(254), 
	"ADDTIME" DATE, 
	"INTERVALCOUNT" NUMBER(10,0), 
	"CHNLCUTTIMESTAMP" DATE, 
	"EDITED_BY_RULE_SCH" CHAR(1), 
	"TZSTDNAME" VARCHAR2(32), 
	"POPULATION" FLOAT(52), 
	"WEIGHT" FLOAT(52), 
	"LSUSER" VARCHAR2(64) DEFAULT USER, 
	"LSTIME" DATE DEFAULT SYSDATE, 
	"VERSIONSEQ" NUMBER(19,0), 
	 CONSTRAINT "CK_LSCHCUTH_UDCHNC" CHECK (UIDCHANNELCUT > 0 and UIDCHANNELCUT < 9223372036854775808) ENABLE NOVALIDATE, 
	 CONSTRAINT "CK_LSCHCUTH_UDCHNL" CHECK (UIDCHANNEL > 0 and UIDCHANNEL < 9223372036854775808) ENABLE NOVALIDATE, 
	 CONSTRAINT "CK_LSCHCUTH_RULSCH" CHECK (EDITED_BY_RULE_SCH IN('Y','N')) ENABLE NOVALIDATE, 
	 CONSTRAINT "PK_LSCHCUTH" PRIMARY KEY ("UIDCHANNELCUT")
	 CONSTRAINT "FK_LSCHCUTH_CHAN" FOREIGN KEY ("UIDCHANNEL")
	  REFERENCES "CHANNEL" ("UIDCHANNEL") ON DELETE CASCADE ENABLE NOVALIDATE)


5. Table lschannelcutdata

CREATE TABLE "LSCHANNELCUTDATA" 
   (	"UIDCHANNELCUT" NUMBER(19,0) NOT NULL ENABLE, 
	"VALUECODES" BLOB, 
	"ADDTIME" DATE, 
	 CONSTRAINT "CK_LSCHCDTA_UIDCHA" CHECK (UIDCHANNELCUT > 0 and UIDCHANNELCUT < 9223372036854775808) ENABLE NOVALIDATE, 
	 CONSTRAINT "PK_LSCCDATA_UIDCCUT" PRIMARY KEY ("UIDCHANNELCUT")


Thanks,
Re: PL/SQl query to build a list of interval days [message #665702 is a reply to message #665701] Fri, 15 September 2017 13:03 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
EXISTING RESULT										
1 day intervals	UIDCHANNELCUT	RECORDER	STARTTIME	STOPTIME	VALUECODES	SPI	OPDAYSTART	OPDAYSTOP	INTERVAL_NUMBER	INTERVAL_VALUE
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	1	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	2	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	3	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	4	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	5	0
										
EXPECTED RESULTS	 Multiple day intervals from 4/1 thru 6/30									
										
	UIDCHANNELCUT	RECORDER	STARTTIME	STOPTIME	VALUECODES	SPI	OPDAYSTART	OPDAYSTOP	INTERVAL_NUMBER	INTERVAL_VALUE
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	1	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	2	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	3	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	4	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/1/2010	4/1/2010 23:59	5	0
										
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/2/2010	4/2/2010 23:59	1	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/2/2010	4/2/2010 23:59	2	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/2/2010	4/2/2010 23:59	3	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/2/2010	4/2/2010 23:59	4	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/2/2010	4/2/2010 23:59	5	0
										
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/3/2010	4/3/2010 23:59	1	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/3/2010	4/3/2010 23:59	2	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/3/2010	4/3/2010 23:59	3	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/3/2010	4/3/2010 23:59	4	0
	2030158107	1.0204E+16	4/1/2010	4/30/2010 23:59	<BLOB>	900	4/3/2010	4/3/2010 23:59	5	0
Re: PL/SQl query to build a list of interval days [message #665716 is a reply to message #665702] Sun, 17 September 2017 16:42 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
bump
Re: PL/SQl query to build a list of interval days [message #665742 is a reply to message #665716] Tue, 19 September 2017 06:50 Go to previous messageGo to next message
quirks
Messages: 60
Registered: October 2014
Member
@veepee:
you're not quite helpful. Please provide some test data (for each table defined by you) that are covering the whole time interval (Apr 1 to Jun 30). 2-5 rows per table should be enough. Please make sure the values of each table can be joined. I still don't get what you want from us?

Just a first guess. Do you want to know how to create a list of days that you can use to join your data to?
WITH
    DATA_SET(STARTTIME, STOPTIME)
    AS
        (SELECT TO_DATE('04.01.2010', 'MM.DD.YYYY'), TO_DATE('04.30.2010 23:59', 'MM.DD.YYYY HH24:MI') FROM DUAL),
    DATES
    AS
        (SELECT DAY
           FROM (SELECT     START_TIME - 1 + LEVEL DAY
                       FROM (SELECT MIN(STARTTIME) AS START_TIME, MAX(STOPTIME) AS END_TIME FROM DATA_SET)
                 CONNECT BY LEVEL <= END_TIME + 1 - START_TIME))
SELECT   DATES.DAY
        ,DATA_SET.STARTTIME
        ,DATA_SET.STOPTIME
        ,DATES.DAY                                                AS OPDAYSTART
        ,DATES.DAY + (DATA_SET.STOPTIME - TRUNC(DATA_SET.STOPTIME)) AS OPDAYSTOP
    FROM DATA_SET, DATES
   WHERE DATES.DAY BETWEEN DATA_SET.STARTTIME AND DATA_SET.STOPTIME
ORDER BY DATES.DAY
Re: PL/SQl query to build a list of interval days [message #665747 is a reply to message #665742] Tue, 19 September 2017 10:43 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
Resource test data:


insert into resourceid (RESOURCEID, GENSITECODE, NOIECODE, DCTIECODE, ACTIECODE, LCFACTOR, STARTTIME, STOPTIME, ADDTIME, PGCCODE, UIDBILLDETERMINANT, UIDCHANNEL, SUBGENCODE, MRECODE, TDSPCODE, NOIETIECODE, LAARSITECODE, DISPATCHASSETCODE, METERTYPECODE, METERGROUP, LSUSER, LSTIME)
values ('FLUMGCRGP0200', 'MGSES', null, null, null, 0, to_date('01-01-2000', 'dd-mm-yyyy'), to_date('15-07-2004 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), to_date('14-07-2004 14:51:27', 'dd-mm-yyyy hh24:mi:ss'), null, 292, 23025, null, '65', null, null, null, null, null, null, 'LC37', to_date('19-03-2005 00:01:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into resourceid (RESOURCEID, GENSITECODE, NOIECODE, DCTIECODE, ACTIECODE, LCFACTOR, STARTTIME, STOPTIME, ADDTIME, PGCCODE, UIDBILLDETERMINANT, UIDCHANNEL, SUBGENCODE, MRECODE, TDSPCODE, NOIETIECODE, LAARSITECODE, DISPATCHASSETCODE, METERTYPECODE, METERGROUP, LSUSER, LSTIME)
values ('FLUMGCRGP0400', 'MGSES', null, null, null, 0, to_date('01-01-2000', 'dd-mm-yyyy'), to_date('15-07-2004 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), to_date('14-07-2004 14:51:43', 'dd-mm-yyyy hh24:mi:ss'), null, 292, 23026, null, '65', null, null, null, null, null, null, 'LC37', to_date('19-03-2005 00:01:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into resourceid (RESOURCEID, GENSITECODE, NOIECODE, DCTIECODE, ACTIECODE, LCFACTOR, STARTTIME, STOPTIME, ADDTIME, PGCCODE, UIDBILLDETERMINANT, UIDCHANNEL, SUBGENCODE, MRECODE, TDSPCODE, NOIETIECODE, LAARSITECODE, DISPATCHASSETCODE, METERTYPECODE, METERGROUP, LSUSER, LSTIME)
values ('FLUMGCRGP0500', 'MGSES', null, null, null, 0, to_date('01-01-2000', 'dd-mm-yyyy'), to_date('14-07-2009 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-07-2009 10:52:07', 'dd-mm-yyyy hh24:mi:ss'), null, 296, 23027, null, '65', null, null, null, null, null, null, 'bburr', to_date('08-07-2009 10:52:07', 'dd-mm-yyyy hh24:mi:ss'));

insert into resourceid (RESOURCEID, GENSITECODE, NOIECODE, DCTIECODE, ACTIECODE, LCFACTOR, STARTTIME, STOPTIME, ADDTIME, PGCCODE, UIDBILLDETERMINANT, UIDCHANNEL, SUBGENCODE, MRECODE, TDSPCODE, NOIETIECODE, LAARSITECODE, DISPATCHASSETCODE, METERTYPECODE, METERGROUP, LSUSER, LSTIME)
values ('FLUMGCRGP0600', 'MGSES', null, null, null, 0, to_date('01-01-2000', 'dd-mm-yyyy'), to_date('14-07-2009 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-07-2009 10:54:37', 'dd-mm-yyyy hh24:mi:ss'), null, 297, 23028, null, '65', null, null, null, null, null, null, 'bburr', to_date('08-07-2009 10:54:37', 'dd-mm-yyyy hh24:mi:ss'));

insert into resourceid (RESOURCEID, GENSITECODE, NOIECODE, DCTIECODE, ACTIECODE, LCFACTOR, STARTTIME, STOPTIME, ADDTIME, PGCCODE, UIDBILLDETERMINANT, UIDCHANNEL, SUBGENCODE, MRECODE, TDSPCODE, NOIETIECODE, LAARSITECODE, DISPATCHASSETCODE, METERTYPECODE, METERGROUP, LSUSER, LSTIME)
values ('FLUMGCRGPMU01', 'MGSES', null, null, null, 0, to_date('01-01-2000', 'dd-mm-yyyy'), null, to_date('14-06-2001 15:09:17', 'dd-mm-yyyy hh24:mi:ss'), null, 292, 23029, null, '65', null, null, null, null, null, null, 'LC37', to_date('19-03-2005 00:01:00', 'dd-mm-yyyy hh24:mi:ss'));

Channel test data:


insert into channel (UIDCHANNEL, UIDRECORDER, CHANNELNUM, ADDTIME, LSUSER, LSTIME)
values (23025, 1, 1, to_date('04-05-2001 12:11:46', 'dd-mm-yyyy hh24:mi:ss'), null, null);

insert into channel (UIDCHANNEL, UIDRECORDER, CHANNELNUM, ADDTIME, LSUSER, LSTIME)
values (23026, 2, 1, to_date('04-05-2001 12:11:56', 'dd-mm-yyyy hh24:mi:ss'), null, null);

insert into channel (UIDCHANNEL, UIDRECORDER, CHANNELNUM, ADDTIME, LSUSER, LSTIME)
values (23027, 4, 1, to_date('04-05-2001 12:11:59', 'dd-mm-yyyy hh24:mi:ss'), null, null);

insert into channel (UIDCHANNEL, UIDRECORDER, CHANNELNUM, ADDTIME, LSUSER, LSTIME)
values (23028, 5, 1, to_date('04-05-2001 12:12:01', 'dd-mm-yyyy hh24:mi:ss'), null, null);

insert into channel (UIDCHANNEL, UIDRECORDER, CHANNELNUM, ADDTIME, LSUSER, LSTIME)
values (23029, 6, 1, to_date('04-05-2001 12:12:03', 'dd-mm-yyyy hh24:mi:ss'), null, null);

Billdeterminant test data:

insert into billdeterminant (UIDBILLDETERMINANT, BILLDETERMCODE, IDENTIFIER, BILLDETERMNAME, UOMCODE, BILLHISTCOLNAME, AGGREGATE, DATATYPE, STARTTIME, STOPTIME, ADDTIME, DATAREQUIREMENT, SEVERITY, CMZONEFLAG, PUBLICEXTRACT, BILLDETERMTYPECODE, LSUSER, LSTIME)
values (292, 'GSSPLITSCA', '501', 'GENERATION SPLITTING  SCADA', '95', null, null, 'STL', to_date('01-01-2001', 'dd-mm-yyyy'), null, to_date('24-09-2010 11:46:49', 'dd-mm-yyyy hh24:mi:ss'), 'DNV', null, null, 'N', 'MKTINT', 'rrobert', to_date('24-09-2010 11:46:49', 'dd-mm-yyyy hh24:mi:ss'));

insert into billdeterminant (UIDBILLDETERMINANT, BILLDETERMCODE, IDENTIFIER, BILLDETERMNAME, UOMCODE, BILLHISTCOLNAME, AGGREGATE, DATATYPE, STARTTIME, STOPTIME, ADDTIME, DATAREQUIREMENT, SEVERITY, CMZONEFLAG, PUBLICEXTRACT, BILLDETERMTYPECODE, LSUSER, LSTIME)
values (297, 'GTOTDC', '114', 'DC TIE GENERATION TOTALS BY QSE', '95', null, null, 'GEN', to_date('01-01-2001', 'dd-mm-yyyy'), null, to_date('24-09-2010 11:47:54', 'dd-mm-yyyy hh24:mi:ss'), 'DNV', 'INFM', null, 'N', 'DAIINT', 'rrobert', to_date('24-09-2010 11:47:54', 'dd-mm-yyyy hh24:mi:ss'));

insert into billdeterminant (UIDBILLDETERMINANT, BILLDETERMCODE, IDENTIFIER, BILLDETERMNAME, UOMCODE, BILLHISTCOLNAME, AGGREGATE, DATATYPE, STARTTIME, STOPTIME, ADDTIME, DATAREQUIREMENT, SEVERITY, CMZONEFLAG, PUBLICEXTRACT, BILLDETERMTYPECODE, LSUSER, LSTIME)
values (298, 'GTOTSUBUFE', 'GTOTSUBUFE', 'GENERATION TOTALS BY SUBUFEZONE', '95', null, null, null, to_date('01-01-2001', 'dd-mm-yyyy'), null, to_date('10-08-2001 14:53:04', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, 'LC37', to_date('19-03-2005 00:01:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into billdeterminant (UIDBILLDETERMINANT, BILLDETERMCODE, IDENTIFIER, BILLDETERMNAME, UOMCODE, BILLHISTCOLNAME, AGGREGATE, DATATYPE, STARTTIME, STOPTIME, ADDTIME, DATAREQUIREMENT, SEVERITY, CMZONEFLAG, PUBLICEXTRACT, BILLDETERMTYPECODE, LSUSER, LSTIME)
values (299, 'GTOTUFE', '512', 'TOTAL GENERATION BY UFE ZONE', '95', null, null, 'GEN', to_date('01-01-2001', 'dd-mm-yyyy'), null, to_date('24-09-2010 11:48:51', 'dd-mm-yyyy hh24:mi:ss'), 'DNV', null, null, 'Y', 'DAIINT', 'rrobert', to_date('24-09-2010 11:48:51', 'dd-mm-yyyy hh24:mi:ss'));

insert into billdeterminant (UIDBILLDETERMINANT, BILLDETERMCODE, IDENTIFIER, BILLDETERMNAME, UOMCODE, BILLHISTCOLNAME, AGGREGATE, DATATYPE, STARTTIME, STOPTIME, ADDTIME, DATAREQUIREMENT, SEVERITY, CMZONEFLAG, PUBLICEXTRACT, BILLDETERMTYPECODE, LSUSER, LSTIME)
values (300, 'HR', '8', 'HEAT RATE', '79', null, null, 'FCTR', to_date('01-01-2001', 'dd-mm-yyyy'), null, to_date('21-09-2006 09:44:11', 'dd-mm-yyyy hh24:mi:ss'), 'ALO', 'ABRT', 'N', 'Y', null, 'CMILLIKE', to_date('21-09-2006 09:44:11', 'dd-mm-yyyy hh24:mi:ss'));


lschannelcutheader test data:


insert into lschannelcutheader (UIDCHANNELCUT, RECORDER, CHANNEL, STARTTIME, STOPTIME, UIDCHANNEL, SPI, UOMCODE, DSTPARTICIPANT, TIMEZONE, ORIGIN, STARTREADING, STOPREADING, METERMULTIPLIER, METEROFFSET, PULSEMULTIPLIER, PULSEOFFSET, EDITED, INTERNALVALIDATION, EXTERNALVALIDATION, MERGEFLAG, DELETEFLAG, VALFLAGE, VALFLAGI, VALFLAGO, VALFLAGN, TKWRITTENFLAG, DCFLOW, ACCEPTREJECTSTATUS, TRANSLATIONTIME, DESCRIPTOR, ADDTIME, INTERVALCOUNT, CHNLCUTTIMESTAMP, EDITED_BY_RULE_SCH, TZSTDNAME, POPULATION, WEIGHT, LSUSER, LSTIME, VERSIONSEQ)
values (1306797423, 'LMTRUNADJ_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2_SU1', 5, to_date('04-06-2007', 'dd-mm-yyyy'), to_date('04-06-2007 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 7289620, 900, '95', 'Y', 12, 'C', 0, 0, 0, 0, 1, 0, 'N', 'Y', 'Y', 'Y', 'Y', null, null, null, null, null, null, null, null, 'Computed', to_date('01-12-2007', 'dd-mm-yyyy'), 96, to_date('02-12-2007 22:39:01', 'dd-mm-yyyy hh24:mi:ss'), 'Y', null, 0, 0, 'maestro', to_date('02-12-2007 22:39:01', 'dd-mm-yyyy hh24:mi:ss'), null);

insert into lschannelcutheader (UIDCHANNELCUT, RECORDER, CHANNEL, STARTTIME, STOPTIME, UIDCHANNEL, SPI, UOMCODE, DSTPARTICIPANT, TIMEZONE, ORIGIN, STARTREADING, STOPREADING, METERMULTIPLIER, METEROFFSET, PULSEMULTIPLIER, PULSEOFFSET, EDITED, INTERNALVALIDATION, EXTERNALVALIDATION, MERGEFLAG, DELETEFLAG, VALFLAGE, VALFLAGI, VALFLAGO, VALFLAGN, TKWRITTENFLAG, DCFLOW, ACCEPTREJECTSTATUS, TRANSLATIONTIME, DESCRIPTOR, ADDTIME, INTERVALCOUNT, CHNLCUTTIMESTAMP, EDITED_BY_RULE_SCH, TZSTDNAME, POPULATION, WEIGHT, LSUSER, LSTIME, VERSIONSEQ)
values (2059181796, 'LMTRUNADJ_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5_SU1', 5, to_date('04-06-2007', 'dd-mm-yyyy'), to_date('04-06-2007 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 7291729, 900, '95', 'Y', 12, 'C', 0, 0, 0, 0, 1, 0, 'N', 'Y', 'Y', 'Y', 'Y', null, null, null, null, null, null, null, null, 'Computed', to_date('01-12-2007', 'dd-mm-yyyy'), 96, to_date('02-12-2007 22:39:01', 'dd-mm-yyyy hh24:mi:ss'), 'Y', null, 0, 0, 'maestro', to_date('02-12-2007 22:39:01', 'dd-mm-yyyy hh24:mi:ss'), null);

insert into lschannelcutheader (UIDCHANNELCUT, RECORDER, CHANNEL, STARTTIME, STOPTIME, UIDCHANNEL, SPI, UOMCODE, DSTPARTICIPANT, TIMEZONE, ORIGIN, STARTREADING, STOPREADING, METERMULTIPLIER, METEROFFSET, PULSEMULTIPLIER, PULSEOFFSET, EDITED, INTERNALVALIDATION, EXTERNALVALIDATION, MERGEFLAG, DELETEFLAG, VALFLAGE, VALFLAGI, VALFLAGO, VALFLAGN, TKWRITTENFLAG, DCFLOW, ACCEPTREJECTSTATUS, TRANSLATIONTIME, DESCRIPTOR, ADDTIME, INTERVALCOUNT, CHNLCUTTIMESTAMP, EDITED_BY_RULE_SCH, TZSTDNAME, POPULATION, WEIGHT, LSUSER, LSTIME, VERSIONSEQ)
values (1306797429, 'LSEGDL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2', 5, to_date('04-06-2007', 'dd-mm-yyyy'), to_date('04-06-2007 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 7307840, 900, '95', 'Y', 12, 'C', 0, 0, 0, 0, 1, 0, 'N', 'Y', 'Y', 'Y', 'Y', null, null, null, null, null, null, null, null, 'Computed', to_date('01-12-2007', 'dd-mm-yyyy'), 96, to_date('02-12-2007 22:39:02', 'dd-mm-yyyy hh24:mi:ss'), 'Y', null, 0, 0, 'maestro', to_date('02-12-2007 22:39:01', 'dd-mm-yyyy hh24:mi:ss'), null);

insert into lschannelcutheader (UIDCHANNELCUT, RECORDER, CHANNEL, STARTTIME, STOPTIME, UIDCHANNEL, SPI, UOMCODE, DSTPARTICIPANT, TIMEZONE, ORIGIN, STARTREADING, STOPREADING, METERMULTIPLIER, METEROFFSET, PULSEMULTIPLIER, PULSEOFFSET, EDITED, INTERNALVALIDATION, EXTERNALVALIDATION, MERGEFLAG, DELETEFLAG, VALFLAGE, VALFLAGI, VALFLAGO, VALFLAGN, TKWRITTENFLAG, DCFLOW, ACCEPTREJECTSTATUS, TRANSLATIONTIME, DESCRIPTOR, ADDTIME, INTERVALCOUNT, CHNLCUTTIMESTAMP, EDITED_BY_RULE_SCH, TZSTDNAME, POPULATION, WEIGHT, LSUSER, LSTIME, VERSIONSEQ)
values (2059181797, 'LSEGDL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5', 5, to_date('04-06-2007', 'dd-mm-yyyy'), to_date('04-06-2007 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 7307837, 900, '95', 'Y', 12, 'C', 0, 0, 0, 0, 1, 0, 'N', 'Y', 'Y', 'Y', 'Y', null, null, null, null, null, null, null, null, 'Computed', to_date('01-12-2007', 'dd-mm-yyyy'), 96, to_date('02-12-2007 22:39:02', 'dd-mm-yyyy hh24:mi:ss'), 'Y', null, 0, 0, 'maestro', to_date('02-12-2007 22:39:02', 'dd-mm-yyyy hh24:mi:ss'), null);

insert into lschannelcutheader (UIDCHANNELCUT, RECORDER, CHANNEL, STARTTIME, STOPTIME, UIDCHANNEL, SPI, UOMCODE, DSTPARTICIPANT, TIMEZONE, ORIGIN, STARTREADING, STOPREADING, METERMULTIPLIER, METEROFFSET, PULSEMULTIPLIER, PULSEOFFSET, EDITED, INTERNALVALIDATION, EXTERNALVALIDATION, MERGEFLAG, DELETEFLAG, VALFLAGE, VALFLAGI, VALFLAGO, VALFLAGN, TKWRITTENFLAG, DCFLOW, ACCEPTREJECTSTATUS, TRANSLATIONTIME, DESCRIPTOR, ADDTIME, INTERVALCOUNT, CHNLCUTTIMESTAMP, EDITED_BY_RULE_SCH, TZSTDNAME, POPULATION, WEIGHT, LSUSER, LSTIME, VERSIONSEQ)
values (2059181798, 'LSEGTL_170_16_BUSLOLF_NORTH_NIDR_NWS_NOTOU_A_U1_N07_2', 5, to_date('04-06-2007', 'dd-mm-yyyy'), to_date('04-06-2007 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 7307841, 900, '95', 'Y', 12, 'C', 0, 0, 0, 0, 1, 0, 'N', 'Y', 'Y', 'Y', 'Y', null, null, null, null, null, null, null, null, 'Computed', to_date('01-12-2007', 'dd-mm-yyyy'), 96, to_date('02-12-2007 22:39:02', 'dd-mm-yyyy hh24:mi:ss'), 'Y', null, 0, 0, 'maestro', to_date('02-12-2007 22:39:02', 'dd-mm-yyyy hh24:mi:ss'), null);

insert into lschannelcutheader (UIDCHANNELCUT, RECORDER, CHANNEL, STARTTIME, STOPTIME, UIDCHANNEL, SPI, UOMCODE, DSTPARTICIPANT, TIMEZONE, ORIGIN, STARTREADING, STOPREADING, METERMULTIPLIER, METEROFFSET, PULSEMULTIPLIER, PULSEOFFSET, EDITED, INTERNALVALIDATION, EXTERNALVALIDATION, MERGEFLAG, DELETEFLAG, VALFLAGE, VALFLAGI, VALFLAGO, VALFLAGN, TKWRITTENFLAG, DCFLOW, ACCEPTREJECTSTATUS, TRANSLATIONTIME, DESCRIPTOR, ADDTIME, INTERVALCOUNT, CHNLCUTTIMESTAMP, EDITED_BY_RULE_SCH, TZSTDNAME, POPULATION, WEIGHT, LSUSER, LSTIME, VERSIONSEQ)
values (2059181799, 'LSEGTL_156_72_BUSNODEM_SOUTH_NIDR_NWS_NOTOU_A_U1_S07_5', 5, to_date('04-06-2007', 'dd-mm-yyyy'), to_date('04-06-2007 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 7307839, 900, '95', 'Y', 12, 'C', 0, 0, 0, 0, 1, 0, 'N', 'Y', 'Y', 'Y', 'Y', null, null, null, null, null, null, null, null, 'Computed', to_date('01-12-2007', 'dd-mm-yyyy'), 96, to_date('02-12-2007 22:39:02', 'dd-mm-yyyy hh24:mi:ss'), 'Y', null, 0, 0, 'maestro', to_date('02-12-2007 22:39:02', 'dd-mm-yyyy hh24:mi:ss'), null);

lschannelcutdata test data:

insert into lschannelcutdata (UIDCHANNELCUT, VALUECODES, ADDTIME)
values (2059181795, '<BLOB>', to_date('16-09-2010 03:44:14', 'dd-mm-yyyy hh24:mi:ss'));

insert into lschannelcutdata (UIDCHANNELCUT, VALUECODES, ADDTIME)
values (2059181796, '<BLOB>', to_date('16-09-2010 03:44:14', 'dd-mm-yyyy hh24:mi:ss'));

insert into lschannelcutdata (UIDCHANNELCUT, VALUECODES, ADDTIME)
values (2059181797, '<BLOB>', to_date('16-09-2010 03:44:14', 'dd-mm-yyyy hh24:mi:ss'));

insert into lschannelcutdata (UIDCHANNELCUT, VALUECODES, ADDTIME)
values (2059181798, '<BLOB>', to_date('16-09-2010 03:44:14', 'dd-mm-yyyy hh24:mi:ss'));

insert into lschannelcutdata (UIDCHANNELCUT, VALUECODES, ADDTIME)
values (2059181799, '<BLOB>', to_date('16-09-2010 03:44:15', 'dd-mm-yyyy hh24:mi:ss'));

Here's the test data for all tables. Am looking for interval data for each resource (start and stop time for each day) from prior quarter.


Thanks once again!
Re: PL/SQl query to build a list of interval days [message #665761 is a reply to message #665747] Wed, 20 September 2017 03:45 Go to previous messageGo to next message
quirks
Messages: 60
Registered: October 2014
Member
@veepee:
with the data you gave us the statemnt in your first posting does not work. You can't do the join
  and r.resourceid=h.recorder
Please revise this statement.

In the end it seems to revolve around LSCHANNELCUTHEADER anyway. So you probably need something like this?
WITH
    OPDAYS
    AS
        (SELECT DAY
           FROM (SELECT     START_TIME - 1 + LEVEL DAY
                       FROM (SELECT TO_DATE('Apr 01 2017', 'MON DD YYYY') AS START_TIME, TO_DATE('Jun 30 2017', 'MON DD YYYY') AS END_TIME
                               FROM DUAL)
                 CONNECT BY LEVEL <= END_TIME + 1 - START_TIME))
SELECT   OPDAYS.DAY
        ,OPDAYS.DAY                                                                    AS OPDAYSTART
        ,OPDAYS.DAY + (LSCHANNELCUTHEADER.STOPTIME - TRUNC(LSCHANNELCUTHEADER.STOPTIME)) AS OPDAYSTOP
        ,LSCHANNELCUTHEADER.*
    FROM LSCHANNELCUTHEADER, OPDAYS
ORDER BY OPDAYS.DAY;
Re: PL/SQl query to build a list of interval days [message #665810 is a reply to message #665761] Thu, 21 September 2017 16:09 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
Thanks quirks!, the SQL query worked for me..

Re: PL/SQl query to build a list of interval days [message #665811 is a reply to message #665810] Fri, 22 September 2017 02:41 Go to previous message
quirks
Messages: 60
Registered: October 2014
Member
You're welcome Wink
Previous Topic: Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)
Next Topic: Adding column to the table which have the huge data
Goto Forum:
  


Current Time: Mon Dec 18 13:42:13 CST 2017

Total time taken to generate the page: 0.05170 seconds