Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
svani312
Starting Member
1 Post |
Posted - 2009-02-02 : 19:28:17
|
Hi Sodeep,Thx for your response, I am supposed to write query using oracle. I am not using SQL Server. Let me explain in detail for better understanding.I have a table DMTB_DRIVING_RECORD with fieldsDRV_IDVEH_ENTITY_CDRIVING_MODE_CHIRER_DRV_IDSTART_DTWhere each hirer_drv_id has 0 or 1 or multiple reliefs. sample data is as follows.DRV_ID VEH_ENTITY_C DRIVING_MODE_C HIRER_DRV_ID START_DT======================================================================414.00 CCPL HR 102 11/7/2008 4:01413.00 CCPL RE 102 11/7/2008 4:01As above hirer 102 is having two reliefs [414, 413] under VEH_ENTITY_C ccpl.I need to display number of [drv_id's]as reliefs for a particular hirer_drv_id under each veh_entity_c.My current query returns the number of existent rows, the non-existent rows cannot be listed out. suppose there are no hirers with 2reliefs under entity VEH_ENTITY_C ccpl it is not shown in the output. output is supposed to be in the following form:relief ccpl ctpl------------ ---------------------0 relief 2 01 relief 3 22 relief 0 13 relief 4 0 4 relief 0 35 relief 2 1> 5 relief 1 0My query:=========SELECT reliefs, SUM(DECODE(VEH_ENTITY_C, 'CCPL',1, 0 )) AS CCPL, SUM(DECODE(VEH_ENTITY_C, 'CTPL',1, 0 )) AS CTPLFROM( SELECT COUNT(drv_id) reliefs , VEH_ENTITY_C, hirer_drv_id FROM dmtb_driving_record WHERE end_dt IS NULL AND DRIVING_MODE_C = 'RE' AND TO_CHAR(START_DT, 'MM/DD/YY') < '12/12/08' GROUP BY hirer_drv_id, VEH_ENTITY_C UNION ALL SELECT 0 reliefs, VEH_ENTITY_C, hirer_drv_id FROM dmtb_driving_record WHERE end_dt IS NULL AND DRIVING_MODE_C = 'RE' AND TO_CHAR(START_DT, 'MM/DD/YY') < '12/12/08' GROUP BY hirer_drv_id, VEH_ENTITY_C ORDER BY reliefs )GROUP BY reliefs ORDER BY reliefso/p====reliefs ccpl ctpl-----------------------0 2 0 1 3 2 --if we don't have any record for 2reliefs need to display as per requirement.3 4 04 0 3 5 2 1>5 1 0Hope you understand my requirement.Thanks,vani. |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-02 : 19:32:34
|
Are you using SQL Server? Decode keywork looks like Oracle stuff. Also your Explanation is really confusing.Explain with Sample data and Expected output. |
|
|
|
|
|
|
|