Join Queries
-
Select *
From iw_patstrat_genpact_workdb.daysinarea;
Select curr_invo_num_1,
current_area_1,
(days_in_area)+1 As daysinarea,
Sum(
Case
When current_area_1=current_area_2
And run_date_1=run_date_2-1
Then 1
Else 0
End 
As days_in_areaFrom iw_patstrat_genpact_workdb.daysinarea
Group By 1,2;
OutPut:-
curr_invo_num_1 current_area_1 days_in_area
41593391 B15 0
41574352 AU 2
41574351 AU 2
41594993 B16 2
41593391 SP 2
Select curr_invo_num_2,
current_area_2,
Sum(
Case
When current_area_1<>current_area_2
And curr_invo_num_1=curr_invo_num_2
Then 1
Else 0
End 
As days_in_areaFrom iw_patstrat_genpact_workdb.daysinarea
Group By 1,2
OutPut:-
curr_invo_num_2 current_area_2 days_in_area
41574352 AU 0
41594993 B16 0
41593391 SP 1
41593391 B15 1
41574351 SP 1
41574351 AU 0
i want to join these two queris based on condtition
Select a.
curr_invo_num_1,
a.current_area_1,
Sum
(
Case
When a.current_area_1=a.current_area_2
And a.curr_invo_num_1=a.curr_invo_num_2
Then (a.run_date_2-a.run_date_1)+1
Else 0
End ) As days_in_area
From
iw_patstrat_genpact_workdb.daysinarea a
Left
Join
(
Select
curr_invo_num_2,
current_area_2,
Sum
(
Case
When current_area_1<>current_area_2
And curr_invo_num_1=curr_invo_num_2
Then 1
Else 0
End ) As days_in_area
From
iw_patstrat_genpact_workdb.daysinarea
)b
On a.curr_invo_num_1=b.curr_invo_num_2
Where
b.days_in_area<>0
Group By
1,2;
i geting problem.
can 1 help me.
thnaks
-
>>i geting problem.
Any chance of you describing what that problem is? Error message? ???? You might get people to help a bit quicker that way...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
i solved my problem myself.
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply