retrieving last 12 months and date no greater than current end of week

metropia
metropia used Ask the Experts™
on
I am working on a query in which I have to retrieve the last 12 months, based on a field named "weekending"

There is some data with dates in the future that i really do not want to look at.

my query:

SELECT r.weekending,(sum(NULLIF(actualfootage,0))/sum(NULLIF(targetfootage,0))*100)
FROM OE_DATA r
where r.weekending > DATEADD(ww, -12, current_timestamp)
group by r.weekending
order by r.weekending ASC

How can a clause be added so that the weekending field is 12 months in the past but no greater than weekending of current week.

Thanks a lot for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Your query retrieves last 12 weeks and future so I changed it in last 12 months and next week.
As always think if you want to include borders < or <=. And a remark on this is that you compare with current_timestamp, this seems to include the time-part dependend if your weekending column also has a timepart same query 5 minutes later can give another result.

DATEADD http://msdn.microsoft.com/en-us/library/ms186819.aspx
Date and time http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html
where r.weekending > DATEADD(mm, -12, current_timestamp)
AND r.weekending < DATEADD(ww, 1, current_timestamp)

Open in new window

Commented:
SELECT r.weekending,(sum(NULLIF(actualfootage,0))/sum(NULLIF(targetfootage,0))*100)
FROM OE_DATA r
where r.weekending > DATEADD(ww, -12, current_timestamp)
and r.weekending < getdate()-7
group by r.weekending
order by r.weekending ASC

Commented:
@TempDBA
Not a good practice to introduce a second method to get same result.
Even when getdate()  returns same value as current_timestamp when someone reviews this query later then they can get confused 'why the difference? What's the catch?' and loose time and focus on non-important things.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
Hmm... I will consider it as good practise for coding. Thanks jogos.
Theo KouwenhovenApplication Consultant

Commented:
What is weekending for you? Friday, Saturday, Sunday?

Author

Commented:
Saturday is weekending.

Thank you all for your help. I am going to start testing the results.

Author

Commented:
I changed just a little bit the query to look back to the last 16 weeks.

I am attaching an image of my results.

In that image you will see the last week ending being 5/26/2012
but we have not yet finished the week (that is Saturday)

The last week ending date the query should retrieve is 5/19/2012 since that week has been already completed.
Capture.PNG
Commented:
<< but no greater than weekending of current week.>>
This left room for interpretation that's why I commented my sollution with 'and next week', guess comparing with current day will probably more what you expected.


AND r.weekending <  current_timestamp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial