help to build a sql query

[Crystalboy1]

Can anyone help me built a SQL query to to select this data and calculate the accrual according to the detials given below.

On a Friday the report will accrue for three days (ie for Fri, Sat and Sun) using the amount outstanding
For example : -
BusinessDay Amount Accrual

Thurs 40 2,739.72
Fri 40 8,219.18 *

Mon 60 3,424.66


* Fri 40 2,739.72
Sat 40 2,739.72
Sun 40 2,739.72

8,219.18



3.4.7 Where the monthend date occurs on a Saturday or Sunday, the accrual on Friday should include the accrual for the 30th and/or 31st , calculated on Fridays CoB position.
For example : -
Sun 31st
BusinesDay Amount DMO Accrual

Thurs 28th 40 2,739.72
Fri 29th 40 8,219.17*
Sat 30th
Sun 31st
Mon 1st 40 2,739.72


* Fri 29th 40 2,739.72
Sat 30th 40 2,739.72
Sun 31st 40 2,739.72
8,219.17



Sun 1st
BusinessDay Amount Accrual


Thurs 29th 40 2,739.72
Fri 30th 40 5,479.45*
Sat 31st
Sun 1st
Mon 2nd 40 5,479.45**

* Fri 30th 40 2,739.72
Sat 31st 40 2,739.72
5,479.45

** Sun 1st 40 2,739.72
Mon 2nd 40 2,739.72
5,479.45




Where the 1st of the month occurs on a Saturday or Sunday, the accrual on Monday should include the accrual for the 1st and/or 2nd, calculated on Fridays CoB position.

Sat 1st
Business Day Amount DMO
Thurs 30th 40 2,739.72
Fri 31st 40 2,739.72
Sat 1st
Sun 2nd
Mon 3rd 40 8,219.16*


* Sat 1st 40 2,739.72
Sun 2nd 40 2,739.72
Mon 3rd 40 2,739.72
8,219.16

 

[evaleah]

I am assuming you have a table with the day, date, etc. If so, I would use a case statement to get this. This is untested but something like below. Note, this assumes you have set Sunday as the first day or your week.

accrual = case datepart(weekday, colDate)
  when 6 then case datepart(day, colDate)
     when > 28 then {code to add Saturday and Sunday totals to Friday} else {code to set just Friday total} end
  when 2 then case datepart(day, colDate)
     when < 4 then {code to add Saturday and Sunday totals to Monday} else {code to set just Monday total} end
  end

HTH