I have a query which provides the total number of each of 2 invoice flags per month, starting 1st Feb this year. I also need a percentage calculated for each of the 2 flags by month. However, it is showing the totals correctly for each of the flags by month, but the percentage is using the total of both flags for all months, and so it is wrong if I only want to look at one month. The query is as follows along with a sample output including % which are incorrect.
How can I correct this so that it shows the right % for each of the flags for each month?
Select
sk.Period Period,
sk.[Invoice Flag],
count(*) * 100.0 / Sum(count(*)) Over () Percentage,
count(*) Total1
From
(Select wh.worknumber [Work Order],
Case wh.stage When 1 Then 'Created' When 2 Then 'Approved'
When 4 Then 'Awaiting Parts' When 8 Then 'In Progress'
When 12 Then 'Stock Available' When 16 Then 'Complete'
When 32 Then 'Closed' End Status,
Convert(nvarchar,DatePart(yyyy, wh.date_created)) + '-' + Right('0' +
Convert(nvarchar,DatePart(mm, wh.date_created)), 2) Period,
wh.cust_job_number [Damages PO],
wh.cust_ponum [Hire PO],
wt.name [Work Type],
wh.invoice Invoice,
st.spec_value [Inv 7 days],
sp.spec_value [Inv Approved],
Case When st.spec_value = 1 Then 'Invoiced after 7 days'
When sp.spec_value = 1 Then 'Invoiced with Customer Approval'
End [Invoice Flag]
From worksorderhdr wh
Left Outer Join worksordertypes wt On wt.id = wh.worktype
Left Outer Join (Select spec_value,
linked_to_worknumber
From spec_checklist_remind
Where spec_checklist_id = 53) st On st.linked_to_worknumber = wh.worknumber
Left Outer Join (Select spec_value,
linked_to_worknumber
From spec_checklist_remind
Where spec_checklist_id = 54) sp On sp.linked_to_worknumber = wh.worknumber
Where wh.invoice <> 0 And (st.spec_value = 1 Or sp.spec_value = 1) and wh.date_created >= '2015-02-01 00:00'
And wh.worktype In (3, 4, 5, 7, 23,
24, 25, 38, 39, 42, 48, 50, 60) And wh.rejected <> 1 And wh.stage = 16) sk
Group By
sk.Period,
sk.[Invoice Flag]
Output at present:
Period Invoice Flag Percentage Total1
--------------------------------- ------------------------------- --------------------------------------- -----------
2015-02 Invoiced after 7 days 5.704697986577 136
2015-03 Invoiced after 7 days 2.097315436241 50
2015-02 Invoiced with Customer Approval 59.563758389261 1420
2015-03 Invoiced with Customer Approval 32.634228187919 778
Many thanks
Martyn