?
Solved

last month and week data set

Posted on 2010-01-05
8
Medium Priority
?
302 Views
Last Modified: 2012-05-08
Hello
How can I query on a date field for last full weeks worth of data and if required last full months worth of data - using microsoft sql server

Regards
0
Comment
Question by:philsivyer
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 26179690
this can be done using date functions in sql server
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26179751
Hello philsivyer,

Please provide a full definition for what you mean by "last full month" and "last full week".  Sorry to be pedantic,
but the definitions will drive the expressions...

Regards,

Patrick
0
 

Author Comment

by:philsivyer
ID: 26179876
OK
So, last full weeks worth of data will be Dec 27th 2009 to 2nd Jan 2010
- Sunday to Saturday. It needs to be always last weeks worth of data whatever the date. Same for the month - so, in this case Decembers data.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26179909
philsivyer,

Thanks.  A couple more questions:

1) If the current date is the last day of a month, say Jan 31, do you want January or December?

2) If the current date is a Saturday, say Jan 9, do you want Jan 3 - Jan 9, or Dec 27 - Jan 2?

Patrick
0
 

Author Comment

by:philsivyer
ID: 26179975
Hi
1) December
2)Dec 27 - Jan 2

Regards
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 26180008
philsivyer,

Something like this, then...

/* Month */

DECLARE @Start datetime, @End datetime

SET @Start = DATEADD(m, -1, DATEADD(d, 1 - DAY(CONVERT(datetime, CONVERT(varchar, GETDATE(), 102), 102)), CONVERT(datetime, CONVERT(varchar, GETDATE(), 102), 102)))
SET @End = DATEADD(d, 1 - DAY(CONVERT(datetime, CONVERT(varchar, GETDATE(), 102), 102)), CONVERT(datetime, CONVERT(varchar, GETDATE(), 102), 102))

SELECT col1, col2, col3, ..., colN
FROM SomeTable
WHERE DateCol >= @Start And DateCol < @End



/* Week */

DECLARE @Start datetime, @End datetime

SET @Start = CONVERT(datetime, CONVERT(varchar, GETDATE(), 102), 102)
SET @Start = DATEADD(d, CASE DATENAME(dw, @Start)
      WHEN 'Sunday' THEN -7
      WHEN 'Monday' THEN -8
      WHEN 'Tuesday' THEN -9
-- ...
      WHEN 'Saturday' THEN -13 END, @Start)
SET @End = DATEADD(d, 7, @Start)

SELECT col1, col2, col3, ..., colN
FROM SomeTable
WHERE DateCol >= @Start And DateCol < @End



Patrick
0
 

Author Comment

by:philsivyer
ID: 26180150
Thats just what I wanted - many thanks.

Regards
Phil
0
 

Author Closing Comment

by:philsivyer
ID: 31672872
Thanks
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question