Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to write this query Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 9:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
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
Post #1548074
Posted Thursday, March 6, 2014 12:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 12,953, Visits: 10,723
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1548093
Posted Thursday, March 6, 2014 1:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 39,878, Visits: 36,220
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1548103
Posted Thursday, March 6, 2014 1:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
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
Post #1548104
Posted Thursday, March 6, 2014 1:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
Thanks. Would putting this case in the where clause slow down the query?
Post #1548106
Posted Thursday, March 6, 2014 1:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 39,878, Visits: 36,220
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1548107
Posted Thursday, March 6, 2014 8:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
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.
Post #1548328
Posted Thursday, March 6, 2014 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
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

Post #1548452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse