returning specific dates

hi guys

i have a table Sale_Region like


sale_Date   region    

2013-05-19     UK                  
2013-03-1      AUS                  
2013-05-22     AUS                  
2013-05-21     UK                  
2013-05-24     UK                  

Our requirment:

Using the table above we run weekly report every friday for that week (at 6.00 PM US time)

the sql we use to run our weekly reports

SELECT region, max(sale_date) as FROM_DATE, DATEADD(dd, -5,  max(sale_date)) as TO_DATE
FROM Sale_Region GROUP BY region

from the above SQL the FROM_DATE , TO_DATE and region is passed to the UI to generate the excel reports.

our Sample scenario
since today is friday (24 May) i run the above sql to get my from_date and to_date so that i get
region  from_date     to_date
UK       2013-05-19    2013-05-24
AUS      2013-05-22    2013-05-17

The problem
Sometimes our users ask us to rerun the weekly report..now the above sql is a problem because on monday i will have this data in my table

sale_Date   region    

2013-05-19     UK                  
2013-03-1      UK                  
2013-05-22     AUS                  
2013-05-21     UK                  
2013-05-24     UK  
2013-05-28     UK

so sql will return following data
region  from_date     to_date
UK       2013-05-23    2013-05-28  
AUS      2013-05-22    2013-05-17

The problem is since i am running on monday the previous week's report i still expect the
TO_DATE to be 2013-05-24  and FROM_DATE to be 2013-05-19

Any ideas how i can resolve this.
 any help will be greatly appreciated
thanks
royjaydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TechSingerCommented:
What I got from reading this is that you always want the FROM_DATE to be a Monday and the TO_DATE to be a Friday and whenever you call the report before Friday, it should report the week before.

Going from this assumption, there are 2 approaches you can take.  You can provide a complicated algorithm in the query to calculate the previous Friday (if it is not Friday now) as the FROM_DATE and the Monday before as the TO_DATE, or you could, whenever you run the report on Friday, have it write the result to a separate table and use that table for the mid-week reports.

If you don't like the temporary-table approach and would like to use the complex algorithm, let me know and I may be able to offer some suggestions.
0
PortletPaulfreelancerCommented:
how is the report actually run? is it a stored procedure?

perhaps just using from/to date parameters (so you can specify the dates) will suffice.
0
royjaydAuthor Commented:
ok my appologies if i couldnt explain it properly
Let me try one more time what i want to accomplish.
we have this table Sales_Region
sale_Date   region    

2013-05-19     UK                  
2013-03-1      UK                  
2013-05-22     AUS                  
2013-05-21     UK                  
2013-05-24     UK  

we generate weekly report every friday for that week.
To generate weekly report we extract the From_date and To_date from the above table.
so this weeks From_date will be 2013-05-20 and To_date will be 2013-05-24

Similarly next friday(2013-05-31) another weekly report will be run which will have
From_date as 2013-05-27 and To_date as 2013-05-31

and this process would repeat every friday throughout the year

As of today (24 may) i have this data in my table
sale_Date   region    

2013-05-19     UK                  
2013-03-1      AUS                  
2013-05-22     AUS                  
2013-05-21     UK                  
2013-05-24     UK    

The sql i am using to get the From_date and to_date is
SELECT region, max(sale_date) as FROM_DATE, DATEADD(dd, -5,  max(sale_date)) as TO_DATE
FROM Sale_Region GROUP BY region 

Open in new window


*From_date and to_date needs to be calculated from sale_Date column in above table
so basically in above sql i am getting the max(sale_date) as To_date and
 max(sale_date)-5 as From_date

Now the problem
If there is no data in above table for current week, it will take whatever latest date present in table which is older than current week.
I guess the problem boils down to how do i check if i am taking the correct From_date and
To_date. The correct From_date and To_date sequence is
From_date     To_date
2013-05-20   2013-05-24  (this week)
2013-05-27   2013-05-31  (next week)
2013-06-03   2013-06-07  (and so on..)
2013-06-10   2013-06-14

and so on..
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

royjaydAuthor Commented:
>>>how is the report actually run? is it a stored procedure?

we get the From_date and to_date and region and pass it to a another sql.
thx
0
royjaydAuthor Commented:
<<You can provide a complicated algorithm in the query to calculate the previous Friday (if it is not Friday now) as the FROM_DATE and the Monday before as the TO_DATE>>>

I think what you meant is this
You can provide a complicated algorithm in the query to calculate the previous Friday (if it is not Friday now) as the TO_DATE and the Monday before as the FROM_DATE
correct ?


thx
0
TechSingerCommented:
I'm sorry.  The code you have indicated shows the TO_DATE as 5 day before the FROM_DATE.  However, looking at your example report, it shows the opposite.  My comments will be geared towards the code.

An algorithm you could use to make sure the TO_DATE is the current day if it's Friday or the previous Friday if it's a different day is:

DATEADD(dd, - ((DATEPART(dw, GETDATE())+1) % 7), GETDATE())

I don't have access to a SQL server right now to try that code, but to explain it, the DATEPART with the "dw" indicator will return a number that corresponds to the day of the week with 1 being Sunday and 7 being Saturday.  By adding 1 to the weekday and using the % character (modulo), I get 0 on Fridays up to 6 on Thursdays.  If I than decrease today's date by that number, I will get a Friday.

If you are still wanting the get the MAX sale date, you can just use a WHERE clause to state every sale before the Friday date you have evaluated.

So I would convert the code you have above to:

SELECT region, max(sale_date) as FROM_DATE, DATEADD(dd, -5,  max(sale_date)) as TO_DATE
FROM Sale_Region GROUP BY region 
WHERE FROM_DATE <= DATEADD(dd, - ((DATEPART(dw, GETDATE())+1) % 7), GETDATE())

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
DATEADD(dd, - ((DATEPART(dw, GETDATE())+1) % 7), GETDATE())
is affected by the datefirst dbms setting, it works IF datefirst is 7

the following will work regardless of dbms settings:
DATEADD(dd, - ((datediff(day,0,GETDATE())+3) % 7), GETDATE())

i.e. datediff from day 0 to getdate()  % 7 is unaffected by any dbms settings
nb: the +3 adjusts for a Friday

see: http://sqlfiddle.com/#!3/1fa93/7225
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.