March 5, 2014 at 9:54 pm
How can I implement this? It'll give me syntax error as it wouldn't know loaddate at this point. I was trying to see if I could do this without loading the entire results in a temp table and then putting a filter on it on the loaddatte. Trying to see if I could optimize in the first pass itself by putting loaddate on the filter. Any help on this would be greatly appreciated.
declare @Start datetime
declare @End datetime
set @Start = getdate()-3
set @End = getdate()
SELECT,
a.ordernuber,
b.product
loaddate = case when a.loadate >= b.loaddate then a.loaddate else b.loaddate end
from order a
join product b
on a.itemid = b.itemid
where loaddate between @Start and @END
March 6, 2014 at 12:49 am
What exactly are you trying to accomplish? It is not very clear from your question.
It would also be useful if you provided table DDL, sample data and desired output (see the first link in my signature on how to do this).
The first thing I see that is wrong is the fact you didn't alias loaddate in the WHERE clause.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 6, 2014 at 1:34 am
Other than mis-placed commas, reserved words as table names, the where clause 'problem' is solved by putting the expression in the where. General hint, don't use column names as alias names for an expression, you'll end up with a situation where you're not sure if SQL's using the column or the alias
DECLARE @Start DATETIME
DECLARE @End DATETIME
SET @Start = DATEADD(dd, -3, GETDATE())
SET @End = GETDATE()
SELECT a.ordernuber ,
b.product ,
CASE WHEN o.loadate >= p.loaddate THEN o.loaddate
ELSE p.loaddate
END AS ComputedLoadDate
FROM [order] o
INNER JOIN product p ON o.itemid = p.itemid
WHERE CASE WHEN o.loadate >= p.loaddate THEN o.loaddate
ELSE p.loaddate
END BETWEEN @Start AND @END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2014 at 1:34 am
I am not sure if you are looking to do the same case in the where clause to compare the product and order load date with the start and end date
where loaddate between @Start and @END
the above where can be written as
loaddate >= @Start and loaddate<= @END
the below query migt be help for you , but if u could provide exact requrment with the sample data and the table structure , could help in writing
where @END>loaddate and @Start<loaddate
where @End >=
CASE WHEN a.loadate >= b.loaddate
THEN a.loaddate
ELSE b.loaddate
END
and
@Start <=
CASE WHEN a.loadate >= b.loaddate
THEN a.loaddate
ELSE b.loaddate
END
March 6, 2014 at 1:49 am
Thanks. Would putting this case in the where clause slow down the query?
March 6, 2014 at 1:56 am
Maybe. Is it performing inadequately? Do you have to filter on the expression or can you filter on the raw columns instead without returning incorrect results?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2014 at 8:53 am
Yes. Whats happening currently is that expression is calculated in a view. Those tables are large OLTP tables and only the subsets in view. And there are so many other tables (about 15 but non OLTP) joined to this view and I'm applying the filter on the loaddate outside the view. So, I was thinking to convert the view to a proc and limit those results on top part of the query with loaddate into the temp table inside the proc and then join that temp table to other tables to get results faster.
March 6, 2014 at 1:12 pm
Try this TVF. Make sure there are indexes on loaddate in both order and product, with covering columns for ordernuber and product columns.
CREATE FUNCTION tvf_loadDate
(
-- Add the parameters for the function here
@Start Datetime,
@End Datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT
a.ordernuber,
b.product,
a.loaddate
from [order] a
inner join [product] b
on a.itemid = b.itemid
where loaddate between @Start and @END and a.loadate >= b.loaddate
union all
SELECT
a.ordernuber,
b.product,
b.loaddate
from [order] a
inner join [product] b
on a.itemid = b.itemid
where loaddate between @Start and @END and a.loadate < b.loaddate
)
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply