June 9, 2008 at 2:51 am
Hi,
I need to return the minimum of two values in SQL Server 2000.
Something like
Select min ('10 Apr 2008', '20 Apr 2008') and it returns 10 Apr 2008.
Can someone help here?
Thx
June 9, 2008 at 3:45 am
Try this. It will give you the min date. But I tried this in SQL Server 2005. You change this to SQL Server 2000.
Actually, I have displayed the minimum date from two different tables.
select distinct Mindate =
case
when min(de.ModifiedDate) > min(em.HireDate) then min(em.HireDate)
else min(de.ModifiedDate)
end
from HumanResources.Department as de,HumanResources.Employee as em
Let me know, If you have any problem.
Regards,
Preetha SG
June 9, 2008 at 3:54 am
Thanks for this Preetha !
But my logic for getting the dates is complex and involves big queries and writing the same thing twice for min dates will make it difficult.
I am needed to stick it in a VIEW so cannot use variables as well.
Thx.
June 9, 2008 at 3:59 am
Hi,
You can use without using that variable also.
select distinct
case
when min(de.ModifiedDate) > min(em.HireDate) then min(em.HireDate)
else min(de.ModifiedDate)
end
from HumanResources.Department as de,HumanResources.Employee as em
Let me know, If you are still having problem. If so, explain your problem in detail.
Regards,
Preetha SG
June 9, 2008 at 4:13 am
see if the below makes some sense, I need help in the THEN part below.
[A] = ( Select A =
Case
-- I am in a view and for a given condition (mentioned in When below)
When ((Select count (distinct sign(Qty)) from x With (NoLock) where xtype= 'S' and status = 'LIVE') =2)
Then -- I need to select minimum of the two dates returned from below
Select max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty > 0
Select max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty < 0
Else (0)
End
Thx.
June 9, 2008 at 4:30 am
2 suggestions:
1 - with variables
declare @distinctQTY bigint
select @distinctQTY = count (distinct sign(Qty)) from x With (NoLock) where xtype= 'S' and status = 'LIVE') =2
declare @maxDayQtyGTZero datetime
select @maxDayQtyGTZero = max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty > 0
declare @maxDayQtyLTZero datetime
select @maxDayQtyLTZero = max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty < 0
declare @mindate
set @mindate = case when isnull(@maxDayQtyGTZero, getdate()) < isnull(@minDayQtyGTZero, getdate()) then isnull(@maxDayQtyGTZero, getdate()) else isnull(@minDayQtyGTZero, getdate()) end
select @distinctQTY as DistinctQuantity, @maxDayQtyGTZero as MaxDayQtyGreaterThanZero, @maxDayQtyLTZero as MaxDayQtyLessThanZero, @mindate as AbsoluteFirstDate
2 - In the select
[A] = ( Select A =
Case
-- I am in a view and for a given condition (mentioned in When below) -----ps. A view wont allow dynamics such as variables (for example your status and xtype), you most probably would have a table valued function, or even better and faster a compiled stored proc
When ((Select count (distinct sign(Qty)) from x With (NoLock) where xtype= 'S' and status = 'LIVE') =2)
Then -- I need to select minimum of the two dates returned from below
----something like this?
case when
Select max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty > 0
< Select max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty < 0
then Select max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty > 0
else Select max(DayDt) from x With (NoLock) where xtype= 'S' and status = 'LIVE' and Qty < 0
end
Else (0)
End
However, option 2 has a zillion subselects which will have an adverse impact on performance. I recently had one propellerhead put a lot of subselects into his column selection (i.e. it wasnt represented in the join). So when he started to notice a real degradation in performance, we were able to revise the queries a little smarter and it returned to subsecond response.
Good luck!
~PD
June 9, 2008 at 4:38 am
Variable in views are out of question... I have a limitation that I have to do it in view only... this is what is happening, that subqueries are impacting the performance.
thanks for your time anyways.
June 9, 2008 at 6:26 am
Write a UDF for this.
Your query performance is going to be pretty bad because of the CASE statements anyway, eliminate the headache.
[font="Courier New"]CREATE FUNCTION dbo.MinDate(@Date1 DATETIME, @Date2 DATETIME) RETURNS DATETIME
AS
BEGIN
DECLARE @Ret DATETIME
SET @Ret = (SELECT CASE WHEN @Date1 < @Date2 THEN @Date1 ELSE @Date2 END)
RETURN(@Ret)
END[/font]
June 9, 2008 at 6:32 am
I'd say the CASE statement (sans variables) or the UDF are your best bets. You'll need to test them both to know for sure which works better with your structures.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 9, 2008 at 6:37 am
yes seems like... I hate bad designs, would need to live with it this time... UDF is not permitted in my org... so CASE seems the only option.
Thanks everybody !
June 9, 2008 at 8:43 am
SELECT CASE
WHEN Pos = '19000101' AND Neg = '19000101' THEN 0
WHEN Pos = '19000101' THEN Neg
WHEN Neg = '19000101' THEN Pos
WHEN Pos < Neg THEN Pos
ELSE Neg
END
FROM (
SELECT MAX(CASE WHEN Qty > 0 THEN DayDt ELSE '19000101' END) AS Pos,
MAX(CASE WHEN Qty < 0 THEN DayDt ELSE '19000101' END) AS Neg
FROM TableX WITH (NOLOCK)
WHERE xType = 'S'
AND Status = 'LIVE'
) AS d
N 56°04'39.16"
E 12°55'05.25"
June 10, 2008 at 8:19 am
I've run into orgs before that don't allow UDF or stored proc's becasue the code is not in a 'code safe' (ie: Visual SourceSafe) like all their Java code. This truly handcuffs some of the best features of SQL Server, like compiled and optimized code.
Without UDF or SP's you are limited to CASE statements in your views. Get your org to look into the Visual Studio Team app that allows all SQL Code to be stored in it's code safe, giving you E2E audit trail of every version allowing you to roll changes forwards and backwards at will. Even allows you to do the same with DDL. Good luck with the CASE.
June 13, 2008 at 2:07 pm
Inline table value functions are processed as derived tables and you can write them as such (works great in MySQL which doesn't support table valued functions) even inside a CROSS APPLY or OUTER APPLY. Otherwise you are screwed to being limited to case statements.
CASE WHEN @Date1 < @Date2 THEN @Date1 ELSE @Date2 END would work in concept. You'll have to substitute your aggregates in the @data vars.
November 26, 2008 at 4:45 pm
Once again SQLServerCentral has bailed me out quickly. Thanks for your post. I had not thought of writing my own function. I use them occasionally, but not often enough to have the idea come to mind right away. I appreciate your help.:)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply