Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

returning specific dates

Posted on 2013-05-24
7
Medium Priority
?
183 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
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 49

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
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.

 

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 1200 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 800 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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