CODEWITHSUNDEEP
sqldatetime
Si8
Si8

Reputation: 9225

How to calculate amount based on the number of working days from the month

I have the following query which displays a table with date (month) and an amount:

SELECT TOP 1000 [date]
      ,[amount]
  FROM [database].[dbo].[table]

Which displays the following:

date    amount
201304  1750359.95
201305  1853203.29
201306  1741522.66
201307  1655812.14

I have the following query which gives how many workings (business) days are in this month:

DECLARE @theDate DATETIME

SET @theDate = GETDATE()
SELECT 20 + COUNT(*) FROM 
   ( SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 28) AS theDate 
     UNION 
     SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 29) 
     UNION 
     SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 30) ) AS d 
WHERE DATEPART(DAY, theDate) > 28 AND DATEDIFF(DAY, 0, theDate) % 7 < 5

How can I combine my first query and the second query (get the working days from the month) to display my table as following:

date    amount      average
201304  1750359.95  1750359.95/22 = 79561.81
201305  1853203.29  1853203.29/23 = 80574.05
201306  1741522.66  …
201307  1655812.14  …

The average should be the the amount divided by the number of working days for that month.

How can I get the average?

Upvotes: 0

Views: 777

Answers (2)

rhealitycheck
rhealitycheck

Reputation: 660

You could try this sqlfiddle demo of what I did to figure out the number of week days for each month. Obviously this won't work if you include holidays, if you need to have holidays included I would suggest creating a calendar lookup table to have it not get overly complex.

Code for your example below:

select top 1000 [date], [amount]
datediff(dd, DATEADD(mm, DATEDIFF(m,0,[date]), 0), dateadd(d, -1, DATEADD(m, 1,DATEADD(mm, DATEDIFF(m,0,[date]), 0)))) 
  - (datediff(wk, DATEADD(mm, DATEDIFF(m,0,[date]), 0), dateadd(d, -1, DATEADD(m, 1,DATEADD(mm, DATEDIFF(m,0,[date]), 0)))) * 2) -
    case when datepart(dw, DATEADD(mm, DATEDIFF(m,0,[date]), 0)) = 1 
         then 1 else 0 end +
    case when datepart(dw, dateadd(d, -1, DATEADD(m, 1,DATEADD(mm, DATEDIFF(m,0,[date]), 0)))) = 1 
         then 1 else 0 end + 1
from [database].[dbo].[table]

Upvotes: 1

Guneli
Guneli

Reputation: 1731

Pls, try this: 

   SELECT TOP 1000 [date]
          ,[amount], [amount]/(SELECT 20 + COUNT(*) FROM 
         (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 28) AS theDate 
         UNION 
         SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 29) 
         UNION 
         SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 30) ) AS d 
    WHERE DATEPART(DAY, [date]) > 28 AND DATEDIFF(DAY, 0, [date]) % 7 < 5)
    FROM [database].[dbo].[table]

Upvotes: 1

Related Questions