Solved

returning specific dates

Posted on 2013-05-24
7
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:TechSinger
ID: 39194331
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
ID: 39194337
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
ID: 39194430
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:royjayd
ID: 39194449
>>>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
ID: 39194498
<<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
ID: 39194919
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
ID: 39197274
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

756 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