Convert Compare to a slicker set of code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Compare to a slicker set of code

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2011-08-30 : 15:29:01

Anyone? Idea is if any dates are in the past, the message row should not be selected. There are 2 tables. 1 for the actual message transmission that applies to everyone, and a user message table that has overriding control. However, the lowest date always "wins" and the message is not displayed. I just feel this is a sledgehammer approach...which works.


		 AND CASE 
	   				WHEN um.Term_DT			<= mm.Term_DT		 THEN um.Term_Dt  
	   				WHEN um.Term_DT			<= mm.Expiration_DT  THEN um.Term_Dt 
	   				WHEN um.Term_DT			IS NOT NULL 
	   				 AND mm.Term_DT			IS     NULL 
	   				 AND mm.Expiration_DT	IS     NULL			THEN um.Term_Dt
	   				 
	   				WHEN mm.Term_DT			<= um.Term_DT		THEN mm.Term_Dt
	   				WHEN mm.Term_DT			<= mm.Expiration_DT THEN mm.Term_Dt
	   				WHEN mm.Term_DT			IS NOT NULL 
	   				 AND um.Term_DT			IS     NULL 
	   				 AND mm.Expiration_DT	IS     NULL			THEN mm.Term_Dt
	   				 
	   				WHEN mm.Expiration_DT	<= um.Term_DT		THEN mm.Expiration_DT  
	   				WHEN mm.Expiration_DT	<= mm.Term_DT		THEN mm.Expiration_DT 
	   				WHEN mm.Expiration_DT	IS NOT NULL 
	   				 AND mm.Term_DT			IS     NULL 
	   				 AND um.Term_DT			IS     NULL			THEN mm.Expiration_DT
	   		        ELSE  '01-01-1900'
	   			END > CONVERT(varchar(10),GETDATE(),1)
)

I figure there's got to be a better way

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-08-30 : 16:03:40

[code]

CREATE TABLE #User_Message(Group_Trans_Id int, AKA_Id char(12), Term_Dt Date)
GO

INSERT INTO #User_Message(Group_Trans_Id, AKA_Id, Term_Dt)
SELECT 1, 'X002548', '1900-01-01' UNION ALL
SELECT 2, 'X002548', '1980-02-29' UNION ALL
SELECT 3, 'X002548', '9999-12-31' UNION ALL
SELECT 4, 'X002548', null UNION ALL
SELECT 5, 'X002548', GETDATE() UNION ALL
SELECT 6, 'X002548', null
GO

CREATE TABLE #myMessage(Group_Trans_Id int, Term_Dt Date, Expiration_Dt Date)
GO

INSERT INTO #myMessage(Group_Trans_Id, Term_Dt, Expiration_Dt)
SELECT 1, '1900-01-01','1900-01-01' UNION ALL
SELECT 2, null ,'1900-01-01' UNION ALL
SELECT 3, null , null UNION ALL
SELECT 4, null ,'01/01/2011' UNION ALL
SELECT 5, '01/01/2011',null UNION ALL
SELECT 5, null ,null
GO

DROP TABLE #User_Message, #myMessage
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-30 : 16:29:55

Can we assume the two tables can be joined on the Group_Trans_Id?

If I have some time I can play around more, but do you have some mroe smaple data? I'm guessing this will not work for all the data situations, but it seems to work for your sample data:

COALESCE( um.Term_DT, mm.Term_Dt, mm.Expiration_DT)
> CAST(CURRENT_TIMESTAMP AS DATE) --CONVERT(varchar(10),GETDATE(),1)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-30 : 16:43:11

Unfortunatly, I'm a bit limited on time, but here is an alternative to the CASE expression:

SELECT *
FROM
    (
        SELECT 
            um.Group_Trans_Id,
            (
                SELECT MIN(D.Val)
                FROM
                   (
                        SELECT um.Term_DT AS Val UNION ALL
                        SELECT mm.Term_Dt AS Val UNION ALL 
                        SELECT mm.Expiration_DT AS Val
                   ) AS D
            ) AS  MinDate      
        FROM
            #User_Message AS um
        INNER JOIN  
            #myMessage AS mm
            ON um.Group_Trans_Id = mm.Group_Trans_Id
    )  AS T
WHERE
    MinDate > CAST(CURRENT_TIMESTAMP AS DATE)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-30 : 17:44:27

[code]SELECT um.Group_Trans_ID,
MIN(u.theDate) AS Term_DT
FROM #User_Message AS um
INNER JOIN #MyMessage AS mm ON mm.Group_Trans_ID = um.Group_Trans_ID
CROSS APPLY (
VALUES (um.Term_DT),
(mm.Term_DT),
(mm.Expiration_DT)
) AS u(theDate)
GROUP BY um.Group_Trans_ID
HAVING MIN(u.theDate) > CAST(GETDATE() AS DATE)[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources