This is an existing SQL query that is fetching active account with all details (single row).Here we are joining TABLE_1 and TABLE_2 to get Account, Policy No, Total premium and then join TABLE_3 and TABLE_4 to get City and State value, after joining these 2 sets of tables.
Reqt: Without disturbing the existing extracted rows, the clients need Rate value also that is currently mapped under COVERAGE field value '100' in TABLE_1. Both Rate value and COVERAGE is in TABLE_1.
Code:
SELECT B1.ACCT,B1.PLCY,B1.PREMIUM,A1.CITY,A1.STATE
FROM
( SELECT TBL1.ACCT,
TBL1.PLCY,
TBL2.RISK,
TBL2.TERR,
SUM(TBL1.PREMIUM) AS PREMIUM
FROM TABLE_1 TBL1
INNER JOIN
TABLE_2 TBL2
ON TBL1.ACCT = TBL2.ACCT
AND TBL1.PLCY = TBL2.PLCY
AND TBL1.POL_EFF_DT < CURRENT_DATE
AND TBL1.POL_EXP_DT >= CURRENT_DATE - 1 DAY
GROUP BY TBL1.ACCT,TBL1.PLCY,TBL2.RISK,TBL2.TERR) AS B1
INNER JOIN
(SELECT TBL3.NAME,
TBL3.CITY,
TBL4.STATE
TBL4.ZIP_CD
FROM TABLE_3 TBL3
INNER JOIN
TABLE_4 TBL4
ON TBL3.ACCT = TBL4.ACCT
AND TBL3.PLCY = TBL4.PLCY
AND TBL3.POL_EFF_DT < CURRENT_DATE
AND TBL3.POL_EFF_DT >= CURRENT_DATE - 1 DAY
GROUP BY TBL3.NAME,TBL3.CITY,TBL4.STATE,TBL4.ZIP_CD) AS A1
ON B1.ACCT = A1.ACCT
AND B1.PLCY = A1.PLCY
AND B1.POL_EFF_DT = A1.POL_EFF_DT;
Concern: An account will have lot number of coverages. So if I mention COV check in WHERE clause under B1 select clause as mentioned below, it retrieves multiple rows for single account.
My intention here is to get the Rate value mapped under COVERAGE '100' and displayed across account (single row).
Code:
SELECT B1.ACCT,B1.PLCY,B1.PREMIUM,A1.CITY,A1.STATE,
CASE WHEN B1.COV = 100 THEN B1.RATE AS RATE
FROM
( SELECT TBL1.ACCT,
TBL1.PLCY,
TBL2.RISK,
TBL2.TERR,
SUM(TBL1.PREMIUM) AS PREMIUM
TBL1.COV
FROM TABLE 1 TBL1
INNER JOIN
TABLE 2 TBL2
ON TBL1.ACCT = TBL2.ACCT
AND TBL1.PLCY = TBL2.PLCY
AND TBL1.POL_EFF_DT < CURRENT_DATE
AND TBL1.POL_EFF_DT >= CURRENT_DATE - 1 DAY
GROUP BY TBL1.ACCT,TBL1.PLCY,TBL2.RISK,TBL2.TERR,TBL1.COV) AS B1
INNER JOIN
(SELECT TBL3.NAME,
TBL3.CITY,
TBL4.STATE
TBL4.ZIP_CD
FROM TABLE_3 TBL3
INNER JOIN
TABLE_4 TBL4
ON TBL3.ACCT = TBL4.ACCT
AND TBL3.PLCY = TBL4.PLCY
AND TBL3.POL_EFF_DT < CURRENT_DATE
AND TBL3.POL_EFF_DT >= CURRENT_DATE - 1 DAY
GROUP BY TBL3.NAME,TBL3.CITY,TBL4.STATE,TBL4.ZIP_CD) AS A1
ON B1.ACCT = A1.ACCT
AND B1.PLCY = A1.PLCY
AND B1.POL_EFF_DT = A1.POL_EFF_DT;
AND TBL1.POL_EFF_DT < CURRENT_DATE
AND TBL1.POL_EXP_DT >= CURRENT_DATE - 1 DAY
Currently the SQl query retrieves an Account number and corresponding City, State and Total premium associated with it (premium from all individual coverage of an account).
Now what we need is to get the Rate value associated with only Cov 100. If I am keeping COV 100 check in WHERE clause, I wont get the total premium instead I will get the premium of Cov 100. So I need total premium as well as the Rate value of Cov 100.
Now what we need is to get the Rate value associated with only Cov 100. If I am keeping COV 100 check in WHERE clause, I wont get the total premium instead I will get the premium of Cov 100. So I need total premium as well as the Rate value of Cov 100.
Think of Union as well (one with existing query and other with Cov 100 condition then you don't lose total premium) . But why are you afraid of using CURSOR though?
