Get data month to calander week

Question

Get data month to calander week

smer

SSCommitted

Points: 1684
More actions
August 23, 2013 at 2:08 pm

#281565

Hello all,
I need two sample sqlserver queries
1. query get the data 1st date of the month to calendar week like week 1 , week 2 , week 3 etc.
2. Second I need a query example the query runs always before Thursday get the previous sum of week data but end date always Thursday?
Thanks
fkh

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

farrukhhameed786 (8/23/2013)
Hello all,
I need two sample sqlserver queries
1. query get the data 1st date of the month to calendar week like week 1 , week 2 , week 3 etc.
2. Second I need a query example the query runs always before Thursday get the previous sum of week data but end date always Thursday?
Thanks
fkh

You might start here. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

For the first one you would need to use a combination of the techniques outlined above and a calendar table (a quick search on this site will provide you with plenty of examples).

The second query above I don't understand what you want.

_______________________________________________________________

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/

smer SSCommitted Points: 1684 More actions · Answer 2

Thank you for reply I check the link for first issue and let you know. For second issue example my current week day is Thursday I look for example that query take the previous 7 days data from current week Thursday always

Snigdha Vartak Default port Points: 1486 More actions · Answer 3

Code below will return Thursday from the current week for the @Date supplied. You can modify your where clause to use this date as FROM DATE and DATEADD(DAY,-7, THISDATE) as TO DATE

DECLARE @DATE AS DATETIME

SET @DATE='2013-08-28'

SELECT DATEADD(DAY, 5- DATEPART(dw, @DATE ), @DATE)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

farrukhhameed786 (8/23/2013)
Hello all,
I need two sample sqlserver queries
1. query get the data 1st date of the month to calendar week like week 1 , week 2 , week 3 etc.
2. Second I need a query example the query runs always before Thursday get the previous sum of week data but end date always Thursday?
Thanks
fkh

Since you're talking about Thursday, what day of the week does a week start on for you? Also, if a month doesn't start on the same day as the first day of the week and the fact the the first "week" of any given month could be as small a single day, what days of the week do you want to show for the first and last weeks of the month?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Jeff Moden (8/28/2013)
Since you're talking about Thursday, what day of the week does a week start on for you? Also, if a month doesn't start on the same day as the first day of the week and the fact the the first "week" of any given month could be as small a single day, what days of the week do you want to show for the first and last weeks of the month?

Jeff - You're always trying to get people to think through their requirements fully instead of encountering problems later. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

smer SSCommitted Points: 1684 More actions · Answer 6

I have attached a workbook to have a Idea how I need it.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

Ed Wagner (8/29/2013)
Jeff Moden (8/28/2013)
Since you're talking about Thursday, what day of the week does a week start on for you? Also, if a month doesn't start on the same day as the first day of the week and the fact the the first "week" of any given month could be as small a single day, what days of the week do you want to show for the first and last weeks of the month?
Jeff - You're always trying to get people to think through their requirements fully instead of encountering problems later. 😉

Heh... It's a "bad" habit of mine, huh? 😛 I can't help it, though. I realize you can't correct a blank piece of paper but I've seen enough "bad paper" to know what should be included on the first blush and maybe even do a little mind reading to avoid foreseeable problems. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Snigdha Vartak Default port Points: 1486 More actions · Answer 8

farrukhhameed786 (8/29/2013)
I have attached a workbook to have a Idea how I need it.

Let me work out something for you this long week end 🙂

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

farrukhhameed786 (8/29/2013)
I have attached a workbook to have a Idea how I need it.

So... according to that partial spreadsheet (week 1 is missing a label as is the last week of 2013 so I have to make a guess), the following rules are what you want to follow...

1. Weeks always start on Thursdays.

2. The year that Wednesday falls in determines the year for that entire week.

Is that correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

smer SSCommitted Points: 1684 More actions · Answer 10

Yes you are correct my week starts from wednesday and end on thrusday.

smer SSCommitted Points: 1684 More actions · Answer 11

Yes you correct my start week from thrusay and end on wednesday...

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

Sounds "urgent". Only 4 months have gone by since I asked that question. 😉 Have you done anything to help yourself on this problem in all that time?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

smer SSCommitted Points: 1684 More actions · Answer 13

Jeff,

Actually very sorry to say i dont find any notification from notifications@sqlservercentral.com. I tried 12 hours my self and now i have done.

Thank you 😀 you helped me 🙂

Select 'WEEK' = CASE DATEPART(weekday,START_DATETIME)

When 5 then DATEPART(week,START_DATETIME)+1

When 6 then DATEPART(week,START_DATETIME)+1

When 7 then DATEPART(week,START_DATETIME)+1

ELSE DATEPART(week,START_DATETIME)

END

From ...

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 14

Oh, be careful with that. The "week" datepart can change based on the setting of DATEFIRST. And, no... you shouldn't count on that setting.

I'm on my way to work. I'll see what I can do when I get home. Maybe someone else will pick this up in the meantime.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)