|
Guest
|
Hi Guys
I have Report which display in Below format
Project Name:XYZ
Descrption ChargeNumber Start
End Mon Tue Wed Thu Fri
Sat Sun Total
3141 01/05/2009
01/11/2009 5 2 2
3 3 0 0 15
3141 01/12/2009
01/18/2009 8 8 8
8 8 0 0 40
Explanation : Lets Say i want to display timecard for all users from
date 01/Jan/2009 To 15/JAN/2009
Based on this i got two timecard as per above for date 01/05/2009 TO
01/11/2009 and 01/12 To 01/18
now i want to get data in following format where 1----16 coloumn are
fixed
ChargeNumber Strt End 1 2 3 4
5 6 7 8 9 10 11 12 13 14 15
16
3141 01/05 01/18 0 0 0
0 5 2 2 3 3 0 0 8 8
8 8 0
i want 1--4 coloumn hours as 0 bcoz timecard start date is from 01/05
and coloumn 16 is zero bcoz report date is till 01/15
how can i achive this plz help me as i new at oracle
Anyhelp will be appreciate.
Here table information You need
CREATE TABLE TIMECARD.TC_CN_LN
(
TCCNL_SNBR NUMBER( NOT NULL,
D_STRT_DT DATE,
D_END_DT DATE,
TOT_HRS NUMBER(6,2),
MHRS NUMBER(6,2),
THRS NUMBER(6,2),
WHRS NUMBER(6,2),
HHRS NUMBER(6,2),
FHRS NUMBER(6,2),
SHRS NUMBER(6,2),
UHRS NUMBER(6,2),
FK_TCTC_ID NUMBER( NOT NULL,
FK_TC_CNBRTCCN_ID NUMBER( NOT NULL
)
Sample Data:
Insert into TC
(TC_ID,STRT_DT,END_DT,STAT_CD,FULL_WK_FL,VERIF_LST_DT,VERIF_WRN_CNT,VERIF_ERR_CNT,SUBL_DT,SUBL_LATE_FL,SUBL_LAST_DT,SUBR_E_SGNR,SUBR_AUD_STR,ORIG_LATE_FL,APRV_DT,APRV_LATE_FL,D_APVR_ID,D_APVR_TYP,APVR_E_SGNR,APVR_AUD_STR,REJ_DT,LST_MOD_USERID,LST_MOD_DT,LST_MOD_ACTN,TC_TOT_HRS,TC_MHRS,TC_THRS,TC_WHRS,TC_HHRS,TC_FHRS,TC_SHRS,TC_UHRS,FK_TC_WKRTCW_TYP,FK_TC_WKRTCW_ID,POST_ID,POST_DT,POST_TYP,POST_SIG)
values (75092,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),'Approved
','Y',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF AM'),
3,0,to_timestamp('13-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),'Y',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'marichli.gif ','A.S. Not
Available ',null,to_timestamp('28-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'Y',6800,'E','whenry.gif
','A.S. Not Available ',to_timestamp('17-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'6800 ',to_timestamp('28-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'Approved ',46.5,9.5,9.5,9,9,9.5,0,0,'E',
7710,null,null,null,null);
Insert into TC
(TC_ID,STRT_DT,END_DT,STAT_CD,FULL_WK_FL,VERIF_LST_DT,VERIF_WRN_CNT,VERIF_ERR_CNT,SUBL_DT,SUBL_LATE_FL,SUBL_LAST_DT,SUBR_E_SGNR,SUBR_AUD_STR,ORIG_LATE_FL,APRV_DT,APRV_LATE_FL,D_APVR_ID,D_APVR_TYP,APVR_E_SGNR,APVR_AUD_STR,REJ_DT,LST_MOD_USERID,LST_MOD_DT,LST_MOD_ACTN,TC_TOT_HRS,TC_MHRS,TC_THRS,TC_WHRS,TC_HHRS,TC_FHRS,TC_SHRS,TC_UHRS,FK_TC_WKRTCW_TYP,FK_TC_WKRTCW_ID,POST_ID,POST_DT,POST_TYP,POST_SIG)
values (75522,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),'Approved
','Y',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF AM'),
4,0,to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),'N',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'marichli.gif ','A.S. Not
Available ',null,to_timestamp('28-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'Y',6800,'E','whenry.gif
','A.S. Not Available ',to_timestamp('17-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'6800 ',to_timestamp('28-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'Approved ',44,10,8,8,8,8,0,2,'E',
7710,null,null,null,null);
CREATE TABLE TIMECARD.TC
(
TC_ID NUMBER( NOT NULL,
STRT_DT DATE,
END_DT DATE,
STAT_CD CHAR(10 BYTE),
FULL_WK_FL CHAR(1 BYTE),
VERIF_LST_DT DATE,
VERIF_WRN_CNT NUMBER(4),
VERIF_ERR_CNT NUMBER(4),
SUBL_DT DATE,
SUBL_LATE_FL CHAR(1 BYTE),
SUBL_LAST_DT DATE,
SUBR_E_SGNR CHAR(50 BYTE),
SUBR_AUD_STR CHAR(24 BYTE),
ORIG_LATE_FL CHAR(1 BYTE),
APRV_DT DATE,
APRV_LATE_FL CHAR(1 BYTE),
D_APVR_ID NUMBER(,
D_APVR_TYP CHAR(1 BYTE),
APVR_E_SGNR CHAR(50 BYTE),
APVR_AUD_STR CHAR(24 BYTE),
REJ_DT DATE,
LST_MOD_USERID CHAR(8 BYTE),
LST_MOD_DT DATE,
LST_MOD_ACTN CHAR(12 BYTE),
TC_TOT_HRS NUMBER(6,2),
TC_MHRS NUMBER(6,2),
TC_THRS NUMBER(6,2),
TC_WHRS NUMBER(6,2),
TC_HHRS NUMBER(6,2),
TC_FHRS NUMBER(6,2),
TC_SHRS NUMBER(6,2),
TC_UHRS NUMBER(6,2),
FK_TC_WKRTCW_TYP CHAR(1 BYTE) NOT NULL,
FK_TC_WKRTCW_ID NUMBER( NOT NULL,
POST_ID NUMBER(,
POST_DT DATE,
POST_TYP CHAR(4 BYTE),
POST_SIG VARCHAR2(50 BYTE)
)
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,1);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,29);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,310);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,740);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,1104);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,2617);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
4,0,1,1,1,1,0,0,75092,3115);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
7.5,0.5,1,2,2,2,0,0,75092,3141);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
7.5,0.5,1,2,2,2,0,0,75092,3147);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
27.5,8.5,6.5,4,4,4.5,0,0,75092,3149);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,3227);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,1);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,29);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,740);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,1104);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,2617);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
8,3,1,2,1,1,0,0,75522,3115);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
9,2,1,2,2,2,0,0,75522,3141);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
8,3,1,2,1,1,0,0,75522,3147);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
19,2,5,2,4,4,0,2,75522,3149); |
|
|
