case date help? 2

[LloydDobler]

Hello all, I have 2 tables I'm looking to query. Here's what I have so far:

SELECT     S.Machinenumber, S.StartingDate, S.frequency, E.PMdate, CONVERT(varchar(10), DATEADD(dd, S.frequency, E.PMdate), 101) AS NEXTPMDATE, 
                      S.task
FROM         dbo.tbl_PM_Schedule S LEFT OUTER JOIN
                      dbo.tbl_PM_Entries E ON S.Machinenumber = E.machinenumber
WHERE     (S.activetask = 'Y') AND (S.tasktype = 'PM') AND (S.Division = 1)

Which returns:

Machine StDate freq PMdate NEXTPMDATE
ABCD 01/01/07 30 NULL NULL
1234 02/02/07 30 02/15/07 03/15/07
xyz 02/02/07 30 NULL NULL
9999 01/01/07 30 01/29/07 02/28/07

My problem is I want the 'startdate' to be the "nextPMDATE" if the field e.pmdate is null, and I have no idea how to go about this.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[sunila7]

Try this:

SELECT     S.Machinenumber, S.StartingDate, S.frequency, E.PMdate, 
CASE WHEN E.PMdate IS NULL THEN  S.StartingDate  ELSE CONVERT(varchar(10), DATEADD(dd, S.frequency, E.PMdate), 101) END AS NEXTPMDATE,
                      S.task
FROM         dbo.tbl_PM_Schedule S LEFT OUTER JOIN
                      dbo.tbl_PM_Entries E ON S.Machinenumber = E.machinenumber
WHERE     (S.activetask = 'Y') AND (S.tasktype = 'PM') AND (S.Division = 1)

Sunil

 

[bborissov]

But if PMDate is NULL nextPMDATE will be also NULL?
Or you want to have StartDate in nextPMDATE?
If so:

SELECT S.Machinenumber,
       S.StartingDate,
       S.frequency,
       E.PMdate,
       CASE WHEN PMdate IS NULL
            THEN CONVERT(varchar(10), S.StartingDate, 101)
            ELSE CONVERT(varchar(10), DATEADD(dd,
                                      S.frequency,
                                      E.PMdate), 101)
       END AS NEXTPMDATE,
       S.task
FROM dbo.tbl_PM_Schedule S
     LEFT JOIN dbo.tbl_PM_Entries E
          ON S.Machinenumber = E.machinenumber
WHERE (S.activetask = 'Y')  AND
      (S.tasktype   = 'PM') AND
      (S.Division = 1)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LloydDobler]

Thank you both for such fast and effective reponses. In such a CASE , both should receive stars.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."