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 |
|
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 #665701 is a reply to message #665697] |
Fri, 15 September 2017 12:53 |
|
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 #665742 is a reply to message #665716] |
Tue, 19 September 2017 06:50 |
|
quirks
Messages: 82 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 |
|
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!
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:53:46 CDT 2024
|