Sql data sorted by calendar date
SQL data is sorted weekly for calendar days only. Ex February 1, 2013 - Friday, data for February 1 and 2 must be retrieved. It is now fetching data for the entire week 1-27 -2013 to 2-2-2013. My results are added to the weekend total. EX (1-5-13, 1-12-13). In late January and early February, I need the results to look like 1-27-13 to 1-31-13 = "total" with the end of the week the date will be 31st. For February 2-1-13 to 2-2-13 = "total"
I was able to do this on individual days, but it is not how Accounting wants.
Thanks in advance. Here is my code:
SELECT
DATEADD(week, DATEdiff(WK, 0, bd.[Service Date 1]) ,5) AS month,
bd.Chart,
bd.[Transaction Code],
SUM(bd.Units) AS [Total Billed]
FROM dbo.[Billing Detail] AS bd
INNER JOIN dbo.Patient AS p ON bd.Chart = p.[Chart Number]
WHERE
(bd.[Transaction Code] = 'H2016')
AND (bd.[Service Date 1] >= '01/01/2013')
AND (bd.[Service Date 1] < '12/31/2013')
GROUP BY
DATEADD(week, DATEdiff(WK, 0, bd.[Service Date 1]) ,5),
bd.Chart,
bd.[Transaction Code]
ORDER BY bd.Chart
source to share
I would write a stored procedure that takes a date variable as an input parameter. It will start like this:
declare @dateIn as date;
declare @EndOfWeek as date;
declare @StartOfWeek as date;
declare @WeekEndsOn as int;
set @WeekEndsOn = 6; -- Friday
if datepart(dw, @dateIn) <= @WeekEndsOn
set @EndOfWeek = dateadd(day, @WeekEndsOn - datepart(dw, @dateIn), @dateIn);
else
set @EndOfWeek = dateadd(day, (7 + @WeekEndsOn) - datepart(dw, @dateIn), @dateIn)
Then you start your request with @StartOfWeek and @EndOfWeek variables. You can use a combination of convert () and concatonation to get strings like this: "2-1-13 to 2-2-13".
You might actually want to have @WeekEndsOn as an input parameter, but it will change just in case.
source to share