Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get data month to calander week Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 2:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:13 AM
Points: 8, Visits: 47
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
Post #1488011
Posted Friday, August 23, 2013 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 13,078, Visits: 12,529
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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1488014
Posted Friday, August 23, 2013 2:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:13 AM
Points: 8, Visits: 47
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
Post #1488019
Posted Wednesday, August 28, 2013 3:00 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:35 PM
Points: 522, Visits: 238
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)
Post #1489425
Posted Wednesday, August 28, 2013 3:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489431
Posted Thursday, August 29, 2013 6:15 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 4,202, Visits: 3,639
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
Post #1489672
Posted Thursday, August 29, 2013 11:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:13 AM
Points: 8, Visits: 47
I have attached a workbook to have a Idea how I need it.

  Post Attachments 
Book1.xlsx (14 views, 198.23 KB)
Post #1489813
Posted Thursday, August 29, 2013 5:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489971
Posted Friday, August 30, 2013 3:27 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:35 PM
Points: 522, Visits: 238
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
Post #1490317
Posted Saturday, August 31, 2013 1:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490420
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse