Select Query by month

[MattSmithProg]

Hi,

I have a little problem trying to create a query so it returns in the manner I want.

I have some data that looks like this.

Type Due_Dt Fin_Dt Min_Sgn_Dt
MIN 22/12/2001 18/12/2001
MIN 18/01/2002 15/01/2002
MPC 25/03/2002 17/03/2002
MCO 18/02/2002 14/02/2002


What I know do to get the number of cases completed by the due date and the percentage of these cases is this.

SELECT
CASE WHEN Case_Type = 'MIN' THEN 'Ministerials'
WHEN Case_Type = 'MPC' THEN 'MP Corres'
WHEN Case_Type = 'MCO' THEN 'Misc Corres'
ELSE NULL END AS "Case Type",

("Within Ops"/NULLIFZERO( "Total Comp&quot)*100 AS "% IN OPS",
COUNT(CASE WHEN Case_Type <> 'MIN' AND Due_Dt >= Fin_Dt THEN Case_Type
WHEN Case_Type = 'MIN' AND Due_Dt >= Min_Sgn_Dt THEN Case_Type END) AS &quot;Within Ops&quot;,
COUNT(Case_Type) AS &quot;Total Comp&quot;

FROM PTAPP_YesMin_Main

WHERE Case_Type <> 'PH' AND (Fin_Dt BETWEEN '2001-07-01' AND '2002-06-30') OR (Min_Sgn_Dt BETWEEN '2001-07-01' AND '2002-06-30' AND Case_Type = 'MIN')

GROUP BY &quot;Case Type&quot;

Note: There is a difference between the Fin_Dt for Min and other Case Types.

What I know want is to have a breakup of months and achievement results. I would like the data to look like this.

Achievement Jul Aug Sep Oct ....
Ministerial % In Ops 25 50 75 30 ....
Ministerial Within Ops 12 16 3 3 ....
Ministerial Total Comp 4 8 4 10 ....
MP Corres % In Ops .....................
MP Corres Within Ops .....................
MP Corres Total Comp .....................
..
...
....

I hope this makes sense and someone can help me. I have an urgent deadline and need to get this done.


Matt Smith

No two nulls are the same

 

[MattSmithProg]

I have sort of managed to work it out. Here is the query that I used.

SELECT
&quot;Case Type&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 7 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Jul&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 8 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Aug&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 9 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Sep&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 10 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Oct&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 11 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Nov&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 12 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Dec&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 1 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Jan&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 2 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Feb&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 3 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Mar&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 4 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Apr&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 5 Then &quot;Stats&quot; ELSE NULL END) AS &quot;May&quot;,
SUM(CASE WHEN EXTRACT(MONTH FROM Final_Dt) = 6 Then &quot;Stats&quot; ELSE NULL END) AS &quot;Jun&quot;


FROM
(
SELECT
(CASE Case_Type WHEN 'MIN' THEN 'Ministerials % In Ops'
WHEN 'MPC' THEN 'MP Corres % In Ops'
WHEN 'MCO' THEN 'Misc Corres % In Ops' END) AS &quot;Case Type&quot;,

(CASE WHEN Case_Type = 'MIN' THEN Min_Sgn_Dt
ELSE Fin_Dt END) AS Final_Dt,

Count(Case WHEN Case_Type <> 'MIN' AND Due_Dt >= Fin_Dt Then Fin_Dt WHEN Case_Type = 'MIN' AND Due_Dt >=Min_Sgn_Dt Then Min_Sgn_Dt End)/NULLIFZERO(Count(CASE WHEN Case_Type <> 'MIN' THEN Fin_Dt WHEN Case_Type = 'MIN' THEN Min_Sgn_Dt END))*100 AS &quot;Stats&quot;

FROM PTAPP_YesMin_Main

WHERE Case_Type <> 'PH' AND (Fin_Dt BETWEEN '2001-07-01' AND '2002-06-30') OR (Min_Sgn_Dt BETWEEN '2001-07-01' AND
'2002-06-30' AND Case_Type = 'MIN' ) GROUP BY &quot;Case Type&quot;, Final_Dt


UNION
(
SELECT
(CASE Case_Type WHEN 'MIN' THEN 'Ministerials In Ops'
WHEN 'MPC' THEN 'MP Corres In Ops'
WHEN 'MCO' THEN 'Misc Corres In Ops' END) AS &quot;Case Type&quot;,

(CASE WHEN Case_Type = 'MIN' THEN Min_Sgn_Dt ELSE Fin_Dt END) AS Final_Dt,

COUNT(CASE WHEN Case_Type <> 'MIN' AND Due_Dt >= Fin_Dt THEN Case_Type
WHEN Case_Type = 'MIN' AND Due_Dt >= Min_Sgn_Dt THEN Case_Type END) AS &quot;Stats&quot;

FROM PTAPP_YesMin_Main

WHERE Case_Type <> 'PH' AND (Fin_Dt BETWEEN '2001-07-01' AND '2002-06-30') OR (Min_Sgn_Dt BETWEEN '2001-07-01' AND
'2002-06-30' AND Case_Type = 'MIN' ) GROUP BY &quot;Case Type&quot;, Final_Dt


UNION

SELECT
(CASE Case_Type WHEN 'MIN' THEN 'Ministerials Total'
WHEN 'MPC' THEN 'MP Corres Total'
WHEN 'MCO' THEN 'Misc Corres Total' END) AS &quot;Case Type&quot;,

(CASE WHEN Case_Type = 'MIN' THEN Min_Sgn_Dt
ELSE Fin_Dt END) AS Final_Dt,

COUNT(Case_Type) AS &quot;Stats&quot;

FROM PTAPP_YesMin_Main

WHERE Case_Type <> 'PH' AND (Fin_Dt BETWEEN '2001-07-01' AND '2002-06-30') OR (Min_Sgn_Dt BETWEEN '2001-07-01' AND
'2002-06-30' AND Case_Type = 'MIN' ) GROUP BY &quot;Case Type&quot;, Final_Dt
)
)
As QRY
GROUP BY &quot;Case Type&quot; ORDER BY &quot;Case Type&quot;

However, I can't get the % In ops section to work correctly. It is supposed to be an average but it won't work because of the Sum of the months. Could someone please help me with this, it seems, very complex query. Matt Smith

No two nulls are the same