Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with code to calculate percentages by month

Author  Topic 

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-30 : 11:54:34
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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-30 : 12:00:05
You don't have any partitioning here:


count(*) * 100.0 / Sum(count(*)) Over () Percentage,


so the Sum(...) will be over all rows in the result set. Try adding a PARTITION BY clause to the OVER()

or remove the OVER() clause
Go to Top of Page

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-30 : 12:27:30
Brilliant, why didn't I spot that?! I added
 (partition by sk.period)
and it works fine now.

Many thanks for your help
Martyn
Go to Top of Page
   

- Advertisement -