Avatar of metropia
metropia
Flag for United States of America 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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
jogos

8/22/2022 - Mon
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question