Help with code to calculate percentages by month

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources