Values not present Should show the Row Values

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 2008 Forums

Transact-SQL (2008)

Values not present Should show the Row Values

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2015-04-06 : 02:40:45

Hi ,

I have Created query in sql server

declare @policyno varchar(100)
set @policyno='550300/46/12/8500000138'

select @policyno Policyno,

a.id relationship ,
sum(number)Number,SUM(Amount)Amount from (
select CASE WHEN cs.id =2 THEN '01.Self'
WHEN cs.id=18 THEN '02.Wife'
WHEN cs.id=21 THEN '03.Husband'
WHEN cs.id=5 THEN '04.Daughter'
WHEN cs.id=4 THEN '05.Son'
WHEN cs.id in(20,12) THEN '06.Father/Father in law'
WHEN cs.id in(3,13) THEN '07.Mother/Mother in law'
when cs.id in (1,6,7,8,9,10,11,14,15,16,17,19,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41)
then '08.Others' else '08.Others' end id ,
COUNT(case when a.SlNo=1 and PreAndPost IS null and ClaimStatus not in('Closed','rejected') then a.claimid end) Number,
isnull(SUM(case when a.SlNo=1 then (case when a.INCURRED<0 then 0 else a.INCURRED end) end),0) Amount
FROM relationship cs left outer join dba_reports..mcareProduct_db_claims A
on cs.name=a.relationship where PolicyNo=@policyno
and CS.ID IN (2,18,21,4,5,3,20,12,13)
and cs.deleted=0
group by PolicyNo,PolicyStartDate,PolicyExpiryDate,Organisationname,cs.id)a group by a.id

output
550300/46/12/8500000138 01.Self 24 999169.00
550300/46/12/8500000138 02.Wife 36 828860.00
550300/46/12/8500000138 04.Daughter 5 126496.00
550300/46/12/8500000138 05.Son 13 245455.00
here the case policyno available for those cases showing the values

but i need the output
if values are not present those rows also need to display in the report
i.e
550300/46/12/8500000138 01.Self 24 999169.00
550300/46/12/8500000138 02.Wife 36 828860.00
550300/46/12/8500000138 03.Husband 0 0
550300/46/12/8500000138 04.Daughter 5 126496.00
550300/46/12/8500000138 05.Son 13 245455.00
550300/46/12/8500000138 06.Father/Father in law 0 0
550300/46/12/8500000138 07.Mother/Mother in law' 0 0
550300/46/12/8500000138 08.Others 0 0

if anyone knows pls guide for the same

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-06 : 09:30:40

You could do something like this:


with cte as
(
-- your subquery that you aliased "a"
)


SELECT a.id relationship
     , SUM(number)Number
     , SUM(Amount)Amount
FROM CTE
UNION ALL
SELECT id, 0, 0 
FROM VALUES (('01.Self'), ('02.Wife'), ..., ('08.Others')) missing(id)
WHERE missing.id not in (select id from cte)

kond.mohan
Posting Yak Master

213 Posts

Posted - 2015-04-13 : 01:06:59

Hi ,

thanks for the guidance.when i executed same logic we are getting syntax error of the query at the VALUES Position.
below one is the query used for the Executions

DECLARE @POLICYNO VARCHAR(4000)
SET @POLICYNO ='XXXXXXX'

;with cte as
(
select CASE WHEN cs.id =2 THEN '01.Self'
WHEN cs.id=18 THEN '02.Wife'
WHEN cs.id=21 THEN '03.Husband'
WHEN cs.id=5 THEN '04.Daughter'
WHEN cs.id=4 THEN '05.Son'
WHEN cs.id in(20,12) THEN '06.Father/Father in law'
WHEN cs.id in(3,13) THEN '07.Mother/Mother in law'
when cs.id in (1,6,7,8,9,10,11,14,15,16,17,19,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41)
then '08.Others' else '08.Others' end id ,
COUNT(case when a.SlNo=1 and PreAndPost IS null and ClaimStatus not in('Closed','rejected') then a.claimid end) Number,
isnull(SUM(case when a.SlNo=1 then (case when a.INCURRED<0 then 0 else a.INCURRED end) end),0) Amount
FROM relationship cs left outer join dba_reports..mcareProduct_db_claims A
on cs.name=a.relationship where PolicyNo=@policyno
and CS.ID IN (2,18,21,4,5,3,20,12,13)
and cs.deleted=0
group by PolicyNo,PolicyStartDate,PolicyExpiryDate,Organisationname,cs.id
) SELECT a.id relationship
, SUM(number)Number
, SUM(Amount)Amount
FROM CTE
UNION ALL
SELECT id, 0, 0
FROM VALUES (('01.Self'), ('02.Wife'), ('08.Others')) missing(id)
WHERE missing.id not in (select id from cte)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-13 : 08:03:59

[code]
FROM relationship cs left outer join dba_reports..mcareProduct_db_claims A
on cs.name=a.relationship where PolicyNo=@policyno
-- get rid of this line
-- and CS.ID IN (2,18,21,4,5,3,20,12,13)
and cs.deleted=0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources