SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to include non-existent rown in sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svani312
Starting Member

1 Posts

Posted - 02/02/2009 :  19:28:17  Show Profile  Reply with Quote

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.


Edited by - svani312 on 02/02/2009 19:55:58

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/02/2009 :  19:32:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000