Month & YTD query - Better way to write

[MattSmithProg]

Hi,

I have a query that I have written and it works fine but it seems very inefficient. I would like to know if there is a better way to write it.

My table consists of this

NAT Finalisation_Result Finalisation_Date
4224 Adjust 01-04-2002
3224 Referred 05-04-2002
4224 Adjust 15-03-2002

My query is this.

Select NAT,
SUM(Case When Finalisation_Result = 'Adjusted' AND Finalisation_Date BETWEEN '01-04-2002' AND '30-04-2002' Then 1 Else 0 End) As Adjusted,
SUM(Case When Finalisation_Result = 'NFA' AND Finalisation_Date BETWEEN '01-04-2002' AND '30-04-2002' Then 1 Else 0 End) As NFA,
SUM(Case When Finalisation_Result = 'Post Issue Audit' AND Finalisation_Date BETWEEN '01-04-2002' AND '30-04-2002' Then 1 Else 0 End) As "Post Issue Audit",
SUM(Case When Finalisation_Result = 'Referred' AND Finalisation_Date BETWEEN '01-04-2002' AND '30-04-2002' Then 1 Else 0 End) As "Referred to other BSL/Section",
SUM(Case When Finalisation_Result = 'Adjusted' AND Finalisation_Date BETWEEN '01-07-2001' AND '30-06-2002' Then 1 Else 0 End) As "YTD Adjusted",
SUM(Case When Finalisation_Result = 'NFA' AND Finalisation_Date BETWEEN '01-07-2001' AND '30-06-2002' Then 1 Else 0 End) As "YTD NFA",
SUM(Case When Finalisation_Result = 'Post Issue Audit' AND Finalisation_Date BETWEEN '01-07-2001' AND '30-06-2002' Then 1 Else 0 End) As "YTD Post Issue Audit",
SUM(Case When Finalisation_Result = 'Referred' AND Finalisation_Date BETWEEN '01-07-2001' AND '30-06-2002' Then 1 Else 0 End) As "YTD Referred BSL/Section",
Count(NAT) As Total
From PTAPP_ACR_HRR_Inactive
Group By NAT;

Sorry about the formatting.

It basically will display in the first 5 columns the NAT and the monthly data then in the next 5 columns is the YTD data and the total YTD.

Thanks for your help. Matt Smith

No two nulls are the same

 

[bperry]

I am not thinking that there's anything better you could do.


It wouldn't really change the execution plan, but you might like to strip the yearly dates from the second set of five columns, and put the test into a WHERE clause:

WHERE Finalisation_Date
BETWEEN '01-07-2001' AND '30-06-2002'

This might improve the readability, and it might run a little quicker (but maybe not so that you would notice.)

 

[MattSmithProg]

Thanks bperry.

I did think about it a little more and changed the query to this:

Select '01/04/2002 - 30/04/2002' As "Time Period", NAT,

SUM(Case When Finalisation_Result = 'Adjusted' Then 1 Else 0 End) As Adjusted,
SUM(Case When Finalisation_Result = 'NFA' Then 1 Else 0 End) As NFA,
SUM(Case When Finalisation_Result = 'Post Issue Audit' Then 1 Else 0 End) As "Post Issue Audit",
SUM(Case When Finalisation_Result = 'Referred' Then 1 Else 0 End) As "Referred to other BSL/Section",
SUM(Case When Finalisation_Result = 'On Hold' Then 1 Else 0 End) As "On Hold",
Count(NAT) As Total
From PTAPP_ACR_HRR_Inactive
WHERE Finalisation_Date BETWEEN '01/04/2002' AND '30/04/2002'
Group By NAT

Union ALL

Select 'YTD', NAT,
SUM(Case When Finalisation_Result = 'Adjusted' Then 1 Else 0 End) As Adjusted,
SUM(Case When Finalisation_Result = 'NFA' Then 1 Else 0 End) As NFA,
SUM(Case When Finalisation_Result = 'Post Issue Audit' Then 1 Else 0 End) As "Post Issue Audit",
SUM(Case When Finalisation_Result = 'Referred' Then 1 Else 0 End) As "Referred to other BSL/Section",
SUM(Case When Finalisation_Result = 'On Hold' Then 1 Else 0 End) As "On Hold",
Count(NAT) As Total
From PTAPP_ACR_HRR_Inactive
WHERE Finalisation_Date BETWEEN '01-07-2001' AND '30-06-2002'
Group By NAT;

Thanks again for your help and ideas. Matt Smith

No two nulls are the same

 

[bperry]

Hi Matt,
I'm thinking that the new approach should be less efficient than the first (i.e. can't be sure without looking at the execution plan.) Won't the table have to be scanned twice now - once for each side of the UNION - instead of just once as before?

rgrds, etc
bp

 

[MattSmithProg]

Yes you are right, it would scan twice, however I would prefer the layout of the Union join. It also makes it easier for me to program in VB for the dates as the user needs to select the month to report on and then it also displays the YTD figure.

Thanks for your thoughts Matt Smith

No two nulls are the same