July 14, 2011 at 12:35 am
Hi,
I am trying to get all records from two tables invoice_head, invoice_lines when date is yesterday. Expect when it's Monday, in which case I need them from *Friday.
I thought this would work but it showing today as well?
SELECT *
FROM dbo.invoice_head,dbo.invoice_lines
WHERE invoice_lines.ApproveDate >= CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN (GETDATE() - 3) ELSE (GETDATE() - 1) END
*sorry typo monday changed to friday*
July 14, 2011 at 1:14 am
Your criteria has to condition that approve_date should be greater then your calculated date. This means that the present date should also be returned. You need to add another criteria that the calculated date should be less then today’s date.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2011 at 1:17 am
While your at it check the datefirst parameter
http://msdn.microsoft.com/en-us/library/ms181598.aspx
It decides what values is returned for Monday , ( It not always 2 ) .
And most importantly start using ANSI joins
select * from tablea
join table b
on col1 = colb
the above query in the OP would give you cross joins if your not careful
July 14, 2011 at 1:21 am
just to add to the replies ...
1) of course. Your operator is ">=" !
You should alter it with a ">=" and "<" ( between can have issues with datetime datatypes )
2) (just noticed this after sql refactor :blink: ) your query lacks a join predicate
SELECT *
FROM dbo.invoice_head
INNER JOIN dbo.invoice_lines
ON invoice_lines.headerID = invoice_head.ID
Where invoice_lines.ApproveDate >= dateadd(dd, datediff(dd, 0, GETDATE()) - ( case DATEPART(dw, GETDATE())
when 2 then 3
else 1
end ), 0) -- Startdate
and invoice_lines.ApproveDate < dateadd(dd, datediff(dd, 0, GETDATE()), 0) -- Enddate
/* If monday, present weekend data else only from yesterday */
select dateadd(dd, datediff(dd, 0, GETDATE()) - ( case DATEPART(dw, GETDATE())
when 2 then 3
else 1
end ), 0) as Startdate
, dateadd(dd, datediff(dd, 0, GETDATE()), 0) as Enddate
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 14, 2011 at 9:35 pm
ALZDBA (7/14/2011)
just to add to the replies ...1) of course. Your operator is ">=" !
You should alter it with a ">=" and "<" ( between can have issues with datetime datatypes )
2) (just noticed this after sql refactor :blink: ) your query lacks a join predicate
SELECT *FROM dbo.invoice_head
INNER JOIN dbo.invoice_lines
ON invoice_lines.headerID = invoice_head.ID
Where invoice_lines.ApproveDate >= dateadd(dd, datediff(dd, 0, GETDATE()) - ( case DATEPART(dw, GETDATE())
when 2 then 3
else 1
end ), 0) -- Startdate
and invoice_lines.ApproveDate < dateadd(dd, datediff(dd, 0, GETDATE()), 0) -- Enddate
/* If monday, present weekend data else only from yesterday */
select dateadd(dd, datediff(dd, 0, GETDATE()) - ( case DATEPART(dw, GETDATE())
when 2 then 3
else 1
end ), 0) as Startdate
, dateadd(dd, datediff(dd, 0, GETDATE()), 0) as Enddate
Thanks for the code.
When it's monday I need friday, every other day is yesterday. Will this still work? As I thought Sunday =1 Monday=2 Tuesday=3 Wednesday =4 Thurday=5 Friday =6 Saturday=7
July 15, 2011 at 1:05 am
As already stated by Jayanth_Kurup those numbers depend on the datefirst parameter of your instance !
Doublecheck it.
Also have a look at the second query I provided, it will return the date ranges used in your query.
For mondays it will return data for the whole weekend !
/* If monday, present weekend data else only from yesterday */
declare @RunDate datetime
set @RunDate = '2010-12-31'
;
with cteRslt
as (
select dateadd(dd, N, @RunDate ) as RunDate
, dateadd(dd, datediff(dd, 0, dateadd(dd, N, @RunDate )) - ( case DATENAME (dw, dateadd(dd, N, @RunDate ) )
when 'Monday' then 3
else 1
end ), 0) as Startdate
, dateadd(dd, datediff(dd, 0, dateadd(dd, N, @RunDate ) ), 0) as Enddate
, DATENAME (dw, dateadd(dd, N, @RunDate ) ) NameOfDay
-- just using a tally table tfv for demo
from master.dbo.fn_DBA_Tally (default, default, default)
)
Select *
, DATENAME (dw, Startdate ) as NameStartdate
, DATENAME (dw, Enddate ) as NameEnddate
from cteRslt
order by RunDate ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 15, 2011 at 12:28 pm
I would slightly change the CASE function to protect against wrong results using a different @@language setting:
case datediff(dd,0,dateadd(dd, N, @RunDate )) % 7
when 0 then 3
else 1
end
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply