March 25, 2013 at 12:40 pm
I have data with attendance hours/dates for every day of the month/year. I need to create a query where I can select a range of dates, such as Jan. 1 2013 to March 31, 2013, but output should be a weekly total of attendance hours for each Saturday through Friday weekly range only within the specified beginning and end dates above. Can this be done?
March 25, 2013 at 12:43 pm
fergusoj (3/25/2013)
I have data with attendance hours/dates for every day of the month/year. I need to create a query where I can select a range of dates, such as Jan. 1 2013 to March 31, 2013, but output should be a weekly total of attendance hours for each Saturday through Friday weekly range only within the specified beginning and end dates above. Can this be done?
Yes. It will most likely require a calendar table. If you want specific coding help you need to post ddl, sample data and desired output. Please take a few minutes to read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2013 at 1:03 pm
Thank you for your information. I will try to put something together that is correct.
March 26, 2013 at 7:26 am
I have found a code that brings back a weekly range, but it is not picking up the correct week. Also my week range is Saturday through Friday.
My query:
SELECT PID, ATTEND_DAY,
DATEADD(DAY, - 1, DATEADD(wk,
DATEDIFF(wk, 0, DATEADD(week, DATEPART(week, GETDATE()) - 1, ATTENDANCE_DAY_DATE)), 0)) AS week_start,
DATEADD(DAY, - 2, DATEADD(wk,
DATEDIFF(wk, 0, DATEADD(week, DATEPART(week, GETDATE()), ATTENDANCE_DAY_DATE)), 0)) AS week_end
FROM ATTENDANCE_DETAIL
and my result is this:
PID ATTEND_DAY week_start week-end
999992013-02-272013-05-19 00:00:00.0002013-05-25 00:00:00.000
999992013-02-232013-05-12 00:00:00.0002013-05-18 00:00:00.000
999992013-03-112013-06-02 00:00:00.0002013-06-08 00:00:00.000
999992013-03-132013-06-02 00:00:00.0002013-06-08 00:00:00.000
Would appreciate any help. Haven't been doing datediff or datepart very long.
Thanks.
March 26, 2013 at 8:00 am
I will help you but first you have to help me. Please take a few minutes and read the first article in my signature for best practices when posting questions. It explains how to post your question in such a way that we can offer help.
In short what we need is ddl (create table statements), sample data (insert statements), desired output based on your sample data and an explanation of the business rules. Once we understand the problem we can find a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2013 at 8:16 am
fergusoj (3/26/2013)
I have found a code that brings back a weekly range, but it is not picking up the correct week. Also my week range is Saturday through Friday.My query:
SELECT PID, ATTEND_DAY,
DATEADD(DAY, - 1, DATEADD(wk,
DATEDIFF(wk, 0, DATEADD(week, DATEPART(week, GETDATE()) - 1, ATTENDANCE_DAY_DATE)), 0)) AS week_start,
DATEADD(DAY, - 2, DATEADD(wk,
DATEDIFF(wk, 0, DATEADD(week, DATEPART(week, GETDATE()), ATTENDANCE_DAY_DATE)), 0)) AS week_end
FROM ATTENDANCE_DETAIL
and my result is this:
PID ATTEND_DAY week_start week-end
999992013-02-272013-05-19 00:00:00.0002013-05-25 00:00:00.000
999992013-02-232013-05-12 00:00:00.0002013-05-18 00:00:00.000
999992013-03-112013-06-02 00:00:00.0002013-06-08 00:00:00.000
999992013-03-132013-06-02 00:00:00.0002013-06-08 00:00:00.000
Would appreciate any help. Haven't been doing datediff or datepart very long.
Thanks.
Try this bit of sample code.
-- Mark saturday through friday as a week
SELECT
[DayOfWeek] = DATENAME(dw,MyDate),
MyDate,
DayID = 1+DATEDIFF(DAY,-2,MyDate)%7,
WeekID = DATEDIFF(DAY,-2,MyDate)/7
FROM ( -- some sample data
SELECT MyDate = GETDATE()-5 UNION ALL
SELECT GETDATE()-4 UNION ALL
SELECT GETDATE()-3 UNION ALL
SELECT GETDATE()-2 UNION ALL
SELECT GETDATE()-1 UNION ALL
SELECT GETDATE()-0 UNION ALL
SELECT GETDATE()+1 UNION ALL
SELECT GETDATE()+2 UNION ALL
SELECT GETDATE()+3 UNION ALL
SELECT GETDATE()+4
) d
If you follow Sean's advice, he'll have you up and running in no time.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2013 at 9:54 am
Thank you for your help so far.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply