SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Compare to a slicker set of code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

X002548
Not Just a Number

15586 Posts

Posted - 08/30/2011 :  15:29:01  Show Profile  Reply with Quote
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/



Edited by - X002548 on 08/30/2011 16:06:31

X002548
Not Just a Number

15586 Posts

Posted - 08/30/2011 :  16:03:40  Show Profile  Reply with Quote


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




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/


Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/30/2011 :  16:29:55  Show Profile  Reply with Quote
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)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/30/2011 :  16:43:11  Show Profile  Reply with Quote
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)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/30/2011 :  17:44:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000