Sql Value Validation Logic......

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

Transact-SQL (2012)

Sql Value Validation Logic......

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2015-05-04 : 10:49:54

Hi
i had created below querySELECT md.POLICYNO,'0-18' 'AGE WISE',CASE WHEN rs.id=2 THEN '01.Self' WHEN rs.id in(18,21) THEN '02.Spouse'
WHEN rs.id in(4,5) THEN '03.Child' WHEN rs.id in(20,12,3,13) THEN '04.Parents'
when rs.id not in (2,3,4,5,11,12,13,18,20,21) then '05.Others' end Relationship,
case when COUNT(CASE WHEN m.age BETWEEN 0 AND 18 THEN md.id END)<0 then 0
else COUNT(CASE WHEN m.age BETWEEN 0 AND 18 THEN md.id END) end Count
into #temp
FROM member m inner join memberdetails md on M.ID=MD.MemberID
inner join RelationShip rs on RS.Id=MD.RelationShipID
where m.deleted=0 and md.deleted=0 and rs.deleted=0
GROUP BY md.POLICYNO,rs.id
output of the query is :
Policyno agewise relation count
GHS/Q0000004 0-18 03.Child 13
GHS/Q0000004 0-18 02.Spouse 0
GHS/Q0000004 0-18 02.Spouse 2
GHS/Q0000004 0-18 03.Child 30
GHS/Q0000004 0-18 01.Self 0

i had created Above query to analyse the Age wise report.output is correct. we need to validate the below case to above query.
if values are not Exist it should show '0' to repective columns

in the above output Relation column not displaying Parent,others.my need is should show the all cases and count values needs to show 0

If anyone knows pls guide the above validattion

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-04 : 11:14:37

Check out poorsql.com for formatting your code, and remember to use

tags. Here's your query reformatted (more readable):


SELECT md.POLICYNO
	,'0-18' 'AGE WISE'
	,CASE 
		WHEN rs.id = 2
			THEN '01.Self'
		WHEN rs.id IN (18, 21)
			THEN '02.Spouse'
		WHEN rs.id IN (4, 5)
			THEN '03.Child'
		WHEN rs.id IN (20, 12, 3, 13)
			THEN '04.Parents'
		WHEN rs.id NOT IN (2, 3, 4, 5, 11, 12, 13, 18, 20, 21)
			THEN '05.Others'
		END Relationship
	,CASE 
		WHEN COUNT(CASE 
					WHEN m.age BETWEEN 0
							AND 18
						THEN md.id
					END) < 0
			THEN 0
		ELSE COUNT(CASE 
					WHEN m.age BETWEEN 0
							AND 18
						THEN md.id
					END)
		END Count
INTO #temp
FROM member m
INNER JOIN memberdetails md ON M.ID = MD.MemberID
INNER JOIN RelationShip rs ON RS.Id = MD.RelationShipID
WHERE m.deleted = 0
	AND md.deleted = 0
	AND rs.deleted = 0
GROUP BY md.POLICYNO
	,rs.id

Also, post some sample data and expected results. Its not really clear to me what you are trying to do.

Gerald Britton, MCSA
Toronto PASS Chapter

kond.mohan
Posting Yak Master

213 Posts

Posted - 2015-05-04 : 11:39:39

Thank you gbitton
We have 2 tables one is relationship table(relationshipid,name)
Sample
id name
1 Un known
2 Self
3 Mother
4 Son
5 Daughter
6 Sister in law
7 Brother in law
8 Grandmother
9 Grandfather
10 None
11 Son in law
member details table has below format
policyno relationshipid
GHS/Q0000004 2
GHS/Q0000004 4
GHS/Q0000004 5
GHS/Q0000004 18
GHS/Q0000004 21

as per the above data
Policyno does not contain the data fro relationship 3.
but i have written my case logic for id 3(mohter). but data is not there.

my need is if data is not there for those case need to show the values as 0 and Relationship should show as respective one

Pls guide on the Same

mohan

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-04 : 12:40:44

Ok, but along with the explanation, please post the desired results from the query, using the sample data you provided.

Gerald Britton, MCSA
Toronto PASS Chapter

kond.mohan
Posting Yak Master

213 Posts

Posted - 2015-05-05 : 00:03:22

hi,

Here below policy no does not have data for mother ,others...
All the Relationship(column) should show the Result irrespective of the Data.
here 0-18 between all the relationships has listed below

Policyno agewise relation count
GHS/Q0000004 0-18 03.Child 13
GHS/Q0000004 0-18 02.Spouse 0
GHS/Q0000004 0-18 02.Spouse 2
GHS/Q0000004 0-18 03.Child 30
GHS/Q0000004 0-18 01.Self 0
GHS/Q0000004 0-18 04.Parents 0
GHS/Q0000004 0-18 05.others 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources