Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
royjayd
Asked:
royjayd
  • 3
  • 2
  • 2
2 Solutions
 
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
 
PortletPaulCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
PortletPaulCommented:
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now