July 24, 2008 at 3:27 pm
Hello,
I need to aggregate data for a report on a weekly basis within the month. So if the week exceeds the month I need just those days. Basically 1st of the month to the last of the month. I am rather stumped at this point. I can figure my weekstart and weekend, not sure how get data at the end of the month when the week exceeds the month.
Hope this makes sense.
July 24, 2008 at 3:46 pm
Without the code you've got so far, it's hard to tell you what to fix, so I'm kind of guessing here.
Can't you modify the query to exclude data after the end of the month?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 3:53 pm
DECLARE @WeekStart smalldatetime
DECLARE @WeekEnd smalldatetime
DECLARE @WeekStart_Name varchar(10)
DECLARE @WeekEnd_Name varchar(10)
DECLARE @MonthYear varchar(12)
SET @WeekStart = (DATEADD(day, DATEDIFF(day, 0, getdate()) / 7 * 7, - 1))
SET @WeekEnd = (DATEADD(day, DATEDIFF(day, 6, getdate() - 1) / 7 * 7 + 7, 5))
SET @WeekStart_Name = (DATENAME(dw, @WeekStart))
SET @WeekEnd_Name= (DATENAME(dw, @WeekEnd))
SET @MonthYear = (DATENAME(mm, getdate()) + ' ' + RIGHT(CAST(DATEPART(yyyy, getdate()) AS char(4)), 2))
-----------------------------------------------------------------------------------------
--SELECT @WeekStart, @WeekStart_Name, @WeekEnd, @WeekEnd_Name
SELECT @MonthYear AS MonthYear,
CONVERT(varchar(8), @WeekEnd, 112) AS WeekEndDate,
OperatorID AS OperatorID
FROM DynamicReports.dbo.FirstTrack_FraudDetailSummary
WHERE DateClosed BETWEEN @WeekStart AND @WeekEnd
GROUP BY OperatorID
July 24, 2008 at 3:54 pm
Thank you for your assistance.:)
July 25, 2008 at 9:29 am
After you set the value for @WeekEnd, do something like this:
if datepart(month, @WeekStart) < datepart(month, @WeekEnd)
select @WeekEnd = dateadd(day, -1 * datepart(day, @WeekEnd), @WeekEnd) + 1
That'll give you midnight at the end of the last day of the month.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 4:00 pm
Thank you again.:)
July 27, 2008 at 7:04 pm
this will group anything to Sun-Sat weeks for each month:
select
year([your_date_column])*100 + month([your_date_column]) as year_month,
(day( [your_date_column] ) + 6 - datepart(weekday,[your_date_column])) / 7 + 1 as week_number,
min(day( [your_date_column] )) as first_date, max(day( [your_date_column] )) as last_date,
count(*)
from [your_table(s)]
where ...
group by
year([your_date_column])*100 + month([your_date_column]),
(day( [your_date_column] ) + 6 - datepart(weekday,[your_date_column])) / 7
order by
year([your_date_column])*100 + month([your_date_column]),
(day( [your_date_column] ) + 6 - datepart(weekday,[your_date_column])) / 7
here's the results using a dataset i have for last july and auguest:
[font="Courier New"]
year_mon week first last count
200707 1 1 7 85
200707 2 8 14 96
200707 3 15 21 73
200707 4 22 28 83
200707 5 29 31 32
200708 1 1 4 48
200708 2 5 11 84
200708 3 12 18 89
200708 4 19 25 87
200708 5 26 31 75
200709 1 1 1 12
200709 2 2 8 81
[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply