SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to write this query


how to write this query

Author
Message
SQL_Surfer
SQL_Surfer
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 1086
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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27051 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
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

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


sharath.chalamgari
sharath.chalamgari
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 798
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
SQL_Surfer
SQL_Surfer
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 1086
Thanks. Would putting this case in the where clause slow down the query?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
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

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


SQL_Surfer
SQL_Surfer
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 1086
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.
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 615
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search