Solved

returning specific dates

Posted on 2013-05-24
7
166 Views
Last Modified: 2013-06-11
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
0
Comment
Question by:royjayd
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:TechSinger
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:royjayd
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:royjayd
Comment Utility
>>>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
 

Author Comment

by:royjayd
Comment Utility
<<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
 
LVL 3

Accepted Solution

by:
TechSinger earned 300 total points
Comment Utility
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now