asked on

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

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.

jogos

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 https://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

TempDBA

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

jogos

@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.

TempDBA

Hmm... I will consider it as good practise for coding. Thanks jogos.

Theo Kouwenhoven

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

metropia

ASKER

Saturday is weekending.

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

metropia

ASKER

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

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial