Need Help in Query!! - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Need Help in Query!!

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2003-10-12 : 17:33:47

Hi,

Here is my query and ....

SELECT violations.qte_id,
violations.drv_num,
( CASE WHEN violations.viol_cd = 'LSR'
THEN 'Y' ELSE 'N' END ) AS refusal_submit,
SUM ( CASE WHEN violations.viol_cd IN ( 'ACB', 'ACM', 'ACL', 'ACC', 'ACX' )
THEN 1 ELSE 0 END) AS num_af,
SUM ( CASE WHEN violations.viol_cd IN ( 'ACB', 'ACM', 'ACL', 'ACC', 'ACX' )
AND ( DATEDIFF ( MONTH, violations.viol_dt, quote.pol_eff_dt ) < 12 )
OR ( DATEDIFF ( MONTH, violations.viol_dt, quote.pol_eff_dt ) = 12
AND DATEPART ( DAY, violations.viol_dt ) >= DATEPART ( DAY, quote.pol_eff_dt ) )
THEN 1 ELSE 0 END) AS num_af_12mos,
SUM ( CASE WHEN violations.viol_cd = 'DWI' THEN 1 ELSE 0 END ) AS num_dwi,
SUM ( CASE WHEN violations.viol_cd IN ( 'ACB', 'ACM', 'ACL', 'ACC', 'ACX', 'AIC', 'ACN' )
THEN 1 ELSE 0 END ) AS num_acc
FROM violations
JOIN quote ON violations.qte_id = quote.qte_id
WHERE violations.qte_id = '0810101133247837'
AND ( violations.dupe != 'Y' OR violations.dupe IS NULL )
AND ( violations.not_at_fault != 'Y' OR violations.not_at_fault IS NULL )
AND ( violations.viol_cd IN ('ACB','ACM','ACL','ACC','ACX','DWI','AIC','ACN','LSR'))
GROUP BY violations.qte_id,
violations.drv_num,
violations.viol_cd

looks bulky.. and the output is

qte_id drv_num refusal_submit num_af num_af_12mos num_dwi num_acc
-------------------- ------- -------------- ----------- ------------ ----------- -----------
0810101133247837 1 N 1 1 0 1
0810101133247837 1 N 0 0 0 1
0810101133247837 1 N 0 0 1 0
0810101133247837 3 N 0 0 0 1

... i had been trying hard, can ne body click it for me to get below output...

qte_id drv_num refusal_submit num_af num_af_12mos num_dwi num_acc
-------------------- ------- -------------- ----------- ------------ ----------- -----------
0810101133247837 1 N 1 1 1 2
0810101133247837 3 N 1 0 0 1

Tried all the while I cd, now I think I need a break.

Thanks in advance.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-12 : 18:09:45

Without much thinking... Wrap up your nice query like this:

select qte_id, drv_num, refusal_submit,
sum(num_af), sum(num_af_12mos), sum(num_dwi), sum(num_acc)

from (... here your already written query ...) z

group by qte_id, drv_num, refusal_submit

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2003-10-13 : 08:58:45

Toad, Thanks for the tip and its working gr8 until now

.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources