Date issues

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)What about Max(ISNULL(tblACRDIMProgramPending.ProgramLOIAcknowledged,'')
HTH, Jens Smeyer.
"Fetty" <dfetrow410@.hotmail.com> schrieb im Newsbeitrag
news:u$z8Z2bQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>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)
>|||d not work
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238jww5bQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> What about Max(ISNULL(tblACRDIMProgramPending.ProgramLOIAcknowledged,'')
> HTH, Jens Smeyer.
> "Fetty" <dfetrow410@.hotmail.com> schrieb im Newsbeitrag
> news:u$z8Z2bQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>|||> The only problem is the record does not show up if it is NULL.
The row does not show up when column [?] is null. Try:
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(isnull(tblACRDIMProgramPending.ProgramLOIAcknowledged, GetDate())) 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)
AMB
"Fetty" wrote:

> 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 sho
w
> 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.SysOrgSI
D,
> 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() El
se
> 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)
>
>|||I tried that too
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AD944C94-4200-400E-AA6B-BB1AAE5F91C4@.microsoft.com...
> The row does not show up when column [?] is null. Try:
> 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(isnull(tblACRDIMProgramPending.ProgramLOIAcknowledged, GetDate())) 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)
>
> AMB
>
> "Fetty" wrote:
>|||Then give us real specs, and tell us what "d not work" means. Error
message? If so, what is it? Wrong results? If so, how are they wrong?
What results were you expecting?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Fetty" <dfetrow410@.hotmail.com> wrote in message
news:OoDeTlcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
>I tried that too|||On Fri, 15 Apr 2005 09:18:21 -0400, Fetty wrote:

>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() Els
e
>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)
>
Hi Fetty,
As others already indicated, it's nigh on impoossible to help you
without knowing your tables, sample data and expected output. And it
would also help if you could reformat the SQL to be more readable, and
replace those long unintelligible table names with short and snappy
aliases.
But allow me to take a wild shot:
SELECT sub2.ProgramPendingID,
temp.SysOrgSID,
temp.ProgramName,
sub2.ProgramLOIAcknowledged
FROM dbo.tblACRDIMProgramTemp AS temp
INNER JOIN (SELECT pend.*
FROM tblACRDIMProgramPending AS pend
INNER JOIN (SELECT ProgramSID,
MAX(COALESCE(ProgramLOIAcknowledged,
getdate()) AS MyMaxDate
FROM tblACRDIMProgramPending
GROUP BY ProgramSID) AS sub1
ON pend.ProgramSID = sub1.ProgramSID
AND COALESCE(pend.ProgramLOIAcknowledged,
getdate() = sub1.MyMaxDate) AS sub2
ON temp.ProgramSID = sub2.ProgramSID
WHERE temp.SysOrgSID = 2
(untested, of course)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)