Date issues 2nd question - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Date issues 2nd question

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-04-15 : 08:07:04

I have 2 tables the first table has master records. The secong has child records. I need to get the latest record in the 2nd table using the 'ProgramLOIAcknowledged' date. The only problem is the record does not show up if it is NULL. I tried to put a date in there using getdate(), but I really just want an emply field.

SELECT MySubQuery2.ProgramPendingID, dbo.tblACRDIMProgramTemp.SysOrgSID, dbo.tblACRDIMProgramTemp.ProgramName,
MySubQuery2.ProgramLOIAcknowledged
FROM dbo.tblACRDIMProgramTemp INNER JOIN
(SELECT tblACRDIMProgramPending.*
FROM tblACRDIMProgramPending JOIN
(SELECT tblACRDIMProgramPending.ProgramSID, Max(case tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate() Else tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
FROM tblACRDIMProgramPending
GROUP BY ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID = MySubQuery1.ProgramSID AND
tblACRDIMProgramPending.ProgramLOIAcknowledged = MySubQuery1.MyMaxDate) MySubQuery2 ON
dbo.tblACRDIMProgramTemp.ProgramSID = MySubQuery2.ProgramSID
WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-15 : 13:51:59

Do you have the DDL for your tables, INSERT INTO statements for sample data, and the expected result set using that sample data?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources