SQLTeam.com | Weblogs | Forums

Get the exceptance data

sql2012
#1

i have a table in that table 4 column Id , FirmId, OfficeId, PlanID
Data Like Below
Id FirmId OfficeId PlanID
------------------------------
1 54 0 4
2 54 3 9
3 54 5 8
4 54 6 4
5 54 7 9
6 54 8 8

FirmID 54 has 10 Office
if office does not conains plan then it will refer a firmid and officeid Null planid
like Office 1,2,9,10 does not contains plan so it will refer a FirmId = 54 And Office id = 0 Plan
Now i want result Like Below

Id	FirmId	OfficeId	PlanID
-----------------------------------------
1	54		1		4
1	54		2		4
2	54		3		9
3	54		5		8
4	54		6		4
5	54		7		9
6	54		8		8
1	54		9		4
1	54		10		4
#2

what have you tried? Share your query, even if it doesn't work

#3

i have 4 Table Firm, Office, Plan, FirmOfficePlan in FirmOfficePlan Contains Firm plan and Office Plan like firm 54 has a 10 office Firm 54 has a plan id = 4 and Office id = NULL

but in Some of Office of 54 has a different plan like Office 3 has a Plan id = 9 ,Office 5 has a plan id = 8, Office 6 has a planid = 6

so rest of office has a refer a plan = 4 (Which is configure for Firm)
Below query i had try but i am not satisfy

SELECT  A.Firm_Id,
        A.Office_id,
        PricingPlanId = COALESCE(B.PlanId,A.PlanId)
FROM    (SELECT A.Firm_Id,O.Office_id,A.PlanId  
        FROM    Firm F
                Inner Join Office O ON  O.Firm_Id = F.Firm_Id
                Left Join FirmOfficePlan A ON A.Firm_Id = F.Firm_ID 
                                                      AND A.Office_Id IS Null   
        WHERE   F.Firm_ID = 54)A
        LEFT JOIN FirmOfficePlan B ON A.Firm_ID  = B.Firm_ID 
                                               AND A.Office_id =  B.Office_Id
                                               AND B.Office_Id IS NOT NULL