Calling SP  will not change the beginning week day to Monday using  SET DATEFIRST

GadFriedman
GadFriedman used Ask the Experts™
on
In the attached SP I am running this section, when I run the SP on the SQL server exec hdPbs12_Thom 'CHOMEV','CT','2012/02/01','2012/04/12'
I get the correct information but when I call the SP from my system the information is incorrect and I think it is because the beginning date is not getting set to 1 for Monday as the first day.
Not sure if I place the SET DATEFIRS in the correctly in the SP



SET DATEFIRST 1 -- Week starts on Monday

select
ship_id,
item_no,
sum(item_qty) as item_qty,
sum(amount) as amount,
avg(item_price)as item_price
into #salesLastWeek
from openquery(PP_NEW,
'select item_no,item_qty, amount,item_price,category,
invoice.ship_id,invoice.bill_id,invoice.ship_dt
from invoice left outer join line_it
on invoice.invoice_no=line_it.invoice_no
left outer join ship_to
on invoice.ship_id=ship_to.ship_id
where status=[Posted]
and type=[Invoice]
and invoice.ship_dt>={01/01/12}
and item_qty>0
and lscanpo=.T.
and invoice.bill_id =[CHOMEV]
and ship_to.ship_id in ([[HD04279],[HD04280],[HD04282],[HD04283],[HD04284],[HD04285],[HD04286],
[HD04287],[HD02401],[HD02403],[HD02404],[HD02405], [HD02406],[HD02407],[HD02408],[HD02409],[HD02410],
[HD02412],[HD02414],[HD03401],[HD03403],[HD03480],[HD03481],[HD03482], [HD03484],[HD03485],[HD03486],
[HD03488],[HD04551],[HD03402],[HD03406],[HD03407],[HD03408],[HD04501],[HD04502],[HD08539])
')

where bill_id='CHOMEV' and
ship_dt between (dateadd(d, -datepart(dw, @endDate) - 6, @endDate))  and (dateadd(d, -datepart(dw, @endDate), @endDate) )
group by ship_id,item_no
order by ship_id,item_no
hdPbs12-Thom.sql
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm not sure about the SET command.  It seems like it should work there, but I've never tried to set DATEFIRST, so there may be some issue that I'm not aware of.

 FWIW, in the SP that you posted, you don't have SET DATEFIRST 1 in the third section (for MD).  That shouldn't be an issue if you were actually running the SP for CT, as in your example, but I assume that you want to set it for MD too.

 FWIW, assuming that you want to use SET DATEFIRST 1 for all 3 sections (each state), I would just put it in the very beginning, instead of in each section.  But, AFAIK, it should work either way.

 If you still can't get it to work, I would suggest adding DATEFIRST to the final SP output, so that you could check it.  I think you could just add a column like this to the final query in each section:

,@@DATEFIRST AS DateFirst


 James
<shrug>  Personally, I don't object to the question being deleted, but, FWIW, you might have gotten more responses if you had replied to my first post.  Did you try adding @@DATEFIRST to the SP output to see if it was being changed or not?

 I've used SET for other things in SP's with CR reports and never had any problem with it.  I have no idea why SET DATEFIRST wouldn't work.  One thing you could try would be to create a new SP that did the SET and then executed the old SP.  Or create a new SP that executed that old one and leave the SET in the old SP.  Just a couple of ideas in case it's some kind of "scope" issue.

 Another thing that just occurred to me:
 Do you have "Perform Query Asynchronously" checked for that report (File > "Report Options")?  If so, maybe that's affecting when the SET is evaluated.

 James

Author

Commented:
Sorry I delete the wrong one, I thought I accepted your solution and closed the case, and gave you the points
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

OK.  Were you able to get it to work?  If so, how?  That might help someone else with a similar problem.

 James

Author

Commented:
SET DATEFIRST 1 -- Week starts on Monday

select
ship_id,
item_no,
sum(item_qty) as item_qty,
sum(amount) as amount,
avg(item_price)as item_price
into #salesLastWeek
from openquery(PP_NEW,
'select item_no,item_qty, amount,item_price,category,
invoice.ship_id,invoice.bill_id,invoice.ship_dt
from invoice left outer join line_it
on invoice.invoice_no=line_it.invoice_no
left outer join ship_to
on invoice.ship_id=ship_to.ship_id
where status=[Posted]
and type=[Invoice]
and invoice.ship_dt>={01/01/12}
and item_qty>0
and lscanpo=.T.
and invoice.bill_id =[CHOMEV]
and ship_to.prof_07 in ([106],[66],[93])
')

--to be able to get the dates form Monday to Sunday I had to change the formula from (-6) to (-7)  

where bill_id='CHOMEV' and
ship_dt between (dateadd(d, -datepart(dw, @endDate) - 7, @endDate))  and (dateadd(d, -datepart(dw, @endDate) , @endDate) )
group by ship_id,item_no
order by ship_id,item_no
So, the SET was actually working and the number in DATEADD was wrong?  Or did you change the number in the DATEADD because the SET didn't seem to be working?

 Either way, that wouldn't explain why the SP worked on the server, but not on your system; or if it worked on the server before, that change would presumably break it.

 Just trying to understand.

 James

Author

Commented:
The SET is working.
and the numbers in DATEADD are correct they are from Mon to Sunday as I asked for, but when I ran the SP against my FoxPro Database for some reason to get the Mon data I had to run it from Sun to Sun.
and this is the reason I changed the numbers. I am not sure why but it works.
OK.  Thanks for the explanation.  And I'm glad you figured it out.

 FWIW, off the top of my head, if your ship_dt field or parameters include the time, that might explain it.  For example, if ship_dt is midnight Monday (the beginning of the day) and your parameter includes a time of 1 PM, and the beginning datetime works out to Monday at 1 PM, that ship_dt would not be included, because it's before that.

 If you think that might be it and want to check, I would just add ship_dt and the two calculated values, dateadd(d, -datepart(dw, @endDate) - 7, @endDate) and dateadd(d, -datepart(dw, @endDate) , @endDate), to the SP output and see what you're getting.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial