My first post here, so please bear with me.
My Dataset
t1.c1 t1.c2 t2.c1 t2.c2 t2.c3 t3.c1
2551770 87772 87772 82272 TEST P
2551770 87772 87772 K0328 TEST P
2551770 99396 99396 99396 PREV P
2809700 93000 93000 93000 ELEC M
2809700 99397 99397 99397 PREV M
2809700 99397 99397 G0439 PREV M
Desired Output
t1.C1 t1.C2 t2.C1 t2.C2 t2.C3 t3.C1 t1.C3
2551770 87772 82272 82272 TEST P 82272 (from t1.C2)
2551770 99396 99396 99396 PREV P 99396 (from t1.C2)
2809700 93000 93000 93000 ELEC M 93000 (from t1.C2)
2809700 99397 99397 G0439 PREV M G0439 (from t2.C2)
I know this must use some sort of Case in the Where clause, but I am having a difficult time with the syntax. I need some help to get the desired output! I listed the logic below.
The logic is as follows:
1. If t3.C1 != 'M'
a. If t1.C2 = t2.C2 then select t1.C2
b. If t1.C2 != t2.C2 then do not retrieve this row
2. If t3.C1 = 'M'
a. If t1.C2 = t2.C2 And t2.C2 is not like 'PREV' then select t1.C2
b. If t1.C2 = t2.C2 And t2.C2 is like 'PREV' then do not retrieve this row
c. If t1.C2 != t2.C2 And t2.C2 is like 'PREV' then select t2.C2
>
And, I'm thinking of code like this, but I am getting lost with my cases:
Select Distinct*
From t1 Inner Join t2 On t1.C2 = t2.C1
Where
Case When t3.C1 != 'M' Then
Case When t1.C2 = t2.C2 Then t1.C2
When t1.C2 != t2.C2 then /* do not retreive this row */
Case When t3.C1 = 'M' Then
Case When t1.C2 = t2.C2 And t2.C2 is not like 'PREV' Then t1.C2
When t1.C2 = t2.C2 And t2.C2 is like 'PREV' then /* do not retreive this row */
When t1.C2 != t2.C2 And t2.C2 is like 'PREV' Then t2.C2
End
Aucun commentaire:
Enregistrer un commentaire