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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to include non-existent rown in sql query

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 fields

DRV_ID
VEH_ENTITY_C
DRIVING_MODE_C
HIRER_DRV_ID
START_DT

Where 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:01
413.00 CCPL RE 102 11/7/2008 4:01

As 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 0

1 relief 3 2

2 relief 0 1

3 relief 4 0

4 relief 0 3

5 relief 2 1

> 5 relief 1 0

My query:
=========

SELECT reliefs,
SUM(DECODE(VEH_ENTITY_C, 'CCPL',1, 0 )) AS CCPL,
SUM(DECODE(VEH_ENTITY_C, 'CTPL',1, 0 )) AS CTPL
FROM
(
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 reliefs

o/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 0

4 0 3

5 2 1

>5 1 0

Hope 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.
Go to Top of Page
   

- Advertisement -