Help with writing SQL logic

Question

Post reply

Help with writing SQL logic

vaibhavktiwari

SSC Enthusiast

Points: 126
More actions
June 3, 2021 at 10:43 pm
Go to Answer
#3891686

Hi Everyone,
I need a help in writing a bit tricky logic to write, I will explain the scenario with sample data.
I have two datasets (result set of two separate SQL Queries) -
Dataset 1: PolicyHeader
PolId PolHeader
D0A4EUX20A0Q EST
U00281620A0F EST
Dataset 2: PolicyAmount
PolId TrnType OrigPMAmt
D0A4EUX20A0Q FLAT 31250.00
U00281620A0F FLAT 699.75
U00281620A0F FLAT 949.29
U00281620A0F FLAT 36524.10
U00281620A0F FLAT 10462.05
U00281620A0F FLAT 140660.77
U00281620A0F FLAT 17430.43
U00281620A0F FLAT 68317.46
U00281620A0F EST 275043.85
Now following logic needs to be build to -
Select records from the Dataset PolicyAmount and condition is if PolHeader column value in for a PolId in PolicyHeader dataset is EST and in the PolicyAmount there is a corresponding records having TrnType = 'EST' then those records should be selected and if there is no EST row for the policy and from PolicyHeader it is coming as PolHeader = 'EST' then PolHeader value should be swiched to 'FLAT' and should select records from PolicyAmount table having TrnType = 'FLAT'
Expected output of the final SQL should be -
PolId TrnType OrigPMAmt
D0A4EUX20A0Q FLAT 31250.00
U00281620A0F EST 275043.85
Thanks in advance!

Steve Collins

SSCrazy

Points: 2069

June 4, 2021 at 6:05 pm

Answer

#3892129

You could try something like this

select ph.PolId, 
       case when isnull(pa_est.est_count, 0)>0 
            then ph.PolHeader else 'FLAT' end TrnType,
       case when isnull(pa_est.est_count, 0)>0 
            then pa_est.est_amnt 
            else isnull(pa_flat.flat_amnt, 0) end OrigPMAmt       
from PolicyHeader ph
     outer apply (select count(*), sum(OrigPMAmt) 
                  from PolicyAmount pa
                  where pa.PolId=ph.PolId
                        and pa.TrnType=ph.PolHeader) pa_est(est_count, est_amnt)
     outer apply (select sum(OrigPMAmt) 
                  from PolicyAmount pa
                  where pa.PolId=ph.PolId
                        and pa.TrnType='FLAT') pa_flat(flat_amnt)
where ph.PolHeader='EST';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

No directly usable data to test with, but I think this should at least be close:

SELECT PH.*, PA.*
FROM dbo.PolicyHeader PH
OUTER APPLY (
    SELECT TOP (1) PA.*
    FROM dbo.PolicyAmount PA
    WHERE PH.PolHeader = 'EST' AND PA.PolId = PH.PolId AND PA.TrnType = 'EST' 
) AS PA_EST
INNER JOIN dbo.PolicyAmount PA ON PA.PolId = PH.PolId AND 
    (PH.PolHeader <> 'EST' OR ((PA_EST.PolId IS NOT NULL AND PA.TrnType = 'EST') OR (PA_EST.PolId IS NULL AND PA.TrnType = 'FLAT')))

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

vaibhavktiwari SSC Enthusiast Points: 126 More actions · Answer 2

Thank you so much Scott, you saved my day!

Sorry for the late reply as this was the part of the main SQL and I was testing this with live data for accuracy and performance wise and logic works perfectly but bit expensive on performance side but will work for me.

P.S. I am not sure what do you mean by "no directly usable data to test"

Cheers.

vaibhavktiwari SSC Enthusiast Points: 126 More actions · Answer 3

Thank you Steve!

This query also works from logic perspective

Cheers!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

vaibhavktiwari wrote:

P.S. I am not sure what do you mean by "no directly usable data to test"

He means "Readily Consumable" data. Please see the article at the first link below for some tips on posting in the future. "Readily Consumable" data will help those that try to help you a whole lot. What you posted in your original post isn't "Readily Consumable".

--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)

vaibhavktiwari SSC Enthusiast Points: 126 More actions · Answer 5

Jeff Moden wrote:

vaibhavktiwari wrote:
P.S. I am not sure what do you mean by "no directly usable data to test"
He means "Readily Consumable" data. Please see the article at the first link below for some tips on posting in the future. "Readily Consumable" data will help those that try to help you a whole lot. What you posted in your original post isn't "Readily Consumable".

Oh I see, my apologies!

That’s very helpful article, I will keep that mind for future posts and thanks again all for the help.

Cheers!

vaibhavktiwari SSC Enthusiast Points: 126 More actions · Answer 6

Steve Collins wrote:

You could try something like this

select ph.PolId, 
       case when isnull(pa_est.est_count, 0)>0 
            then ph.PolHeader else 'FLAT' end TrnType,
       case when isnull(pa_est.est_count, 0)>0 
            then pa_est.est_amnt 
            else isnull(pa_flat.flat_amnt, 0) end OrigPMAmt       
from PolicyHeader ph
     outer apply (select count(*), sum(OrigPMAmt) 
                  from PolicyAmount pa
                  where pa.PolId=ph.PolId
                        and pa.TrnType=ph.PolHeader) pa_est(est_count, est_amnt)
     outer apply (select sum(OrigPMAmt) 
                  from PolicyAmount pa
                  where pa.PolId=ph.PolId
                        and pa.TrnType='FLAT') pa_flat(flat_amnt)
where ph.PolHeader='EST';

Hi Steve,

I realized, your query gives correct result for the policy header 'FLAT' and 'EST ' both however Scott's query was not giving correct result for Policy header 'Flat' somehow.

Thanks.