case statement issue - 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

SQL Server Development (2000)

case statement issue

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-02-10 : 12:03:19

This partially works but I think my clause in my case statement is off.

Basically if max(tblcontracts.dateentered) returns null i want the startdate returned. But instead when that instance comes up it returns nothing at all.


select datediff(day,case when max(tblcontracts.dateentered) is not null then max(tblcontracts.dateentered) else tblstaff.startdate end, getdate()) as DiffDates
FROM         dbo.tblContracts INNER JOIN
                      dbo.tblStaff ON dbo.tblContracts.StaffID = dbo.tblStaff.RepID
where repid = 428
group by tblstaff.startdate

Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 12:05:07

[code]
select datediff(day,coalesce(max(tblcontracts.dateentered),tblstaff.startdate), getdate()) as DiffDates
FROM dbo.tblContracts INNER JOIN
dbo.tblStaff ON dbo.tblContracts.StaffID = dbo.tblStaff.RepID
where repid = 428
group by tblstaff.startdate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-02-10 : 12:08:53

thanks. but that also returns nothing.

It should return 9 as Max(tblcontracts.dateentered) returns null. and startdate is 2-1-2010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 12:17:35

quote:
Originally posted by mxfrail

thanks. but that also returns nothing.

It should return 9 as Max(tblcontracts.dateentered) returns null. and startdate is 2-1-2010

what does this return?


select  tblstaff.startdate,max(tblcontracts.dateentered)
FROM         dbo.tblContracts INNER JOIN
                      dbo.tblStaff ON dbo.tblContracts.StaffID = dbo.tblStaff.RepID
where repid = 428
group by tblstaff.startdate

------------------------------------------------------------------------------------------------------
SQL Server MVP

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-02-10 : 12:58:13

The original response works. I forgot to take into account the fact that when value is null there is no entry thus my join was off.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 13:01:19

ok Great

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources