0

I have two tables:

1. #Forecast_Premiums

Syndicate_Key Durg_Key  Currency_Key    Year_Of_Account Forecast_Premium  CUML_EPI_Amount
NULL            NULL      NULL          UNKNOWN              0              6
3                54        46            2000             109105            0
3                54        46            2001             128645           128646
5                47        80            2002             117829           6333
6                47        80            2002             125471           NULL
6                60        80            2003             82371            82371
10               98        215           2006             2093825          77888
10               98        215           2007             11111938         4523645

2.#Forecast_Claims

Syndicate_Key   Durg_Key    Currency_Key    Year_Of_Account Contract_Ref  Forecast_Claims   Ultimate_Profit_Comission
NULL              NULL          NULL           UNKNOWN  UNKNOWN             0                          -45
5                  47            80             2002    AB00ZZ021M12    -9991203                        NULL
5                  47            80             2002    AB00ZZ021M13    -4522                          -74412
9                  60            215            2006    AC04ZZ021M13    -2340299                       -895562
10                 98            46             2007    FAC0ZZ021M55    -2564123                       -851298

The task: Using #Forecast_Premiums and #Forecast_Claims tables write a query to find total amount of Pure Premium ,Cumulative EPI Amount, Forecast_Claims and Ultimate_Profit_Comissionreceived for each combination of Syndicate_Key, Durg_Key , Currency_key and Year_of_Account. Note: In case the Key is NULL set it as 'UNKNOWN' , In Case the Amount is NULL set it as 0.

My solution:

SELECT 
ISNULL(CAST(FP.Syndicate_key AS VARCHAR(20)), 'UNKNOWN') AS 'Syndicate_key', 
ISNULL(CAST(FP.Durg_Key AS VARCHAR(20)), 'UNKNOWN') AS 'Durg_Key',
ISNULL(CAST(FP.Currency_Key AS VARCHAR(20)), 'UNKNOWN') AS 'Currency_Key', 
fp.Year_Of_Account,
SUM(ISNULL(FP.Forecast_Premium,0)) AS 'Pure_Premium',
SUM(ISNULL(FP.CUML_EPI_Amount,0)) AS 'Cuml_Amount',
SUM(ISNULL(dc.Forecast_Claims,0)) AS 'Total_Claims',
SUM(ISNULL(dc.Ultimate_Profit_Comission,0)) AS 'Total_Comission'
FROM #FORECAST_PREMIUMS fp
left join #FORECAST_Claims dc 
ON 
(FP.Year_Of_Account = dc.Year_Of_Account AND
FP.Syndicate_Key = dc.Syndicate_Key AND
FP.Currency_Key = dc.Currency_Key AND
FP.Year_Of_Account = dc.Year_Of_Account)
GROUP BY fp.Syndicate_Key, fp.Durg_Key,fp.Currency_Key,fp.Year_Of_Account

Issue: It returns the Forecast_Claims SUM and Ultimate_Profit_Comission SUM only for one combination of keys and year: 5 47 80 2002.

Moreover it returns 8 rows when it should had return 10.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
jaji18
  • 55
  • 1
  • 7

1 Answers1

0

Eight result records is correct, for there are eight distinct combinations of Syndicate_Key, Durg_Key , Currency_key and Year_of_Account in FORECAST_PREMIUMS.

As to the Forecast_Claims SUM: This is also correct; 5 47 80 2002 is the only combination that has a match in Forecast_Claims.

Only: Are you supposed to match both NULL records? You don't do this, as NULL = NULL is never true (only NULL is NULL is true). You would have to do something like

(
  (FP.Year_Of_Account = dc.Year_Of_Account)
  OR
  (FP.Year_Of_Account is null AND dc.Year_Of_Account is null
) AND ...

to get these records match. Or:

ISNULL(FP.Year_Of_Account, -1) = ISNULL(dc.Year_Of_Account, -1) AND ...
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • The problem is I would like to include in the Result Set also those combinations that are not in Forecast_Premium, therefore I would show the SUM for them. And so I would have 10 rows. – jaji18 May 14 '14 at 07:36
  • 1
    That's a FULL OUTER JOIN. Something that MySQL doesn't support unfortunately. You would have to emulate it. See here: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – Thorsten Kettner May 14 '14 at 07:40
  • OP wrote that tsql is used. – StanislavL May 14 '14 at 07:41
  • @StanislavL: Ah, I didn't read that far, I stopped reading on the first or second tag ;-) – Thorsten Kettner May 14 '14 at 07:44
  • @jaji18: As you are not on MySQL, as your first tag suggested, but on T-SQL, you can use FULL OUTER JOIN alright. – Thorsten Kettner May 14 '14 at 07:46
  • It groups the 2 records from the Forecast_Claims INTO 1 record and sets all keys and year value to "UNKNOWN", though it adds up the SUMS of both records into 1. UNKNOWN UNKNOWN UNKNOWN UNKNOWN 0 0 -4904422 -1746905 – jaji18 May 14 '14 at 10:05
  • You group by the four columns, so of course you get one record per combination, e.g. one record for NULL NULL NULL NULL which you display as UNKNOWN UNKNOWN UNKNOWN UNKNOWN. What else do you expect? As to the numbers: I don't know how you get -4904422 and -1746905 - I don't see these in your example. – Thorsten Kettner May 14 '14 at 10:21
  • The records with Syndicate_key 9 and 10 from #Forecast_Claims add up as one and is displayed as unknown. (e.g. TotalClaims for that row is the sum of forecast_claims for these 2 rows.) I just need to split out that record and display it keys as they are. – jaji18 May 14 '14 at 11:02
  • Ah, this is because Forecast_Premiums.Syndicate_Key can be NULL or Forecast_Claims.Syndicate_Key can be NULL, depeding on which of the records is being outer-joined. Use `GROUP BY COALESCE(Forecast_Premiums.Syndicate_Key, Forecast_Claims.Syndicate_Key)` and so on to get the ID no matter from what table it came from. – Thorsten Kettner May 14 '14 at 11:07