Date difference and a little logic

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 difference and a little logic

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-05 : 13:51:08

I am trying to enter a little logic into my select statement and I'm a little confused in whether to use an IF statement or something else to get what I need.

In the main table there are a couple of date columns and I want to do a comparison between two of them and if the result is True I would like it to print "YES", if false print "NO"

Here is the code I am trying and the response.

Thanks for your assistance as usual.

GC

SELECT losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.CTwentyOne.StationCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.ShopSubcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)AS AbsenceMonth,
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
IF(losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate) PRINT 'YES'
ELSE PRINT 'NO' AS C23Due,
DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate())AS DaysOverDue,
DATEDIFF(dd,CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.C23DiscussionDate)AS DaysC23CompletedIn,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
SUM(losttime.CTwentyOne.SKL_Count
+ losttime.CTwentyOne.ID_Count
+ losttime.CTwentyOne.IU_Count
+ losttime.CTwentyOne.NC_Count
+ losttime.CTwentyOne.PO_Count
+ losttime.CTwentyOne.RL_Count
+ losttime.CTwentyOne.SK_Count
+ losttime.CTwentyOne.SKI_Count
+ losttime.CTwentyOne.SKP_Count
+ losttime.CTwentyOne.SKQ_Count
+ losttime.CTwentyOne.SKU_Count
+ losttime.CTwentyOne.UA_Count ) AS TotalCount,
SUM(losttime.CTwentyOne.SKL_Hours
+ losttime.CTwentyOne.ID_Hours
+ losttime.CTwentyOne.IU_Hours
+ losttime.CTwentyOne.NC_Hours
+ losttime.CTwentyOne.PO_Hours
+ losttime.CTwentyOne.RL_Hours
+ losttime.CTwentyOne.SK_Hours
+ losttime.CTwentyOne.SKI_Hours
+ losttime.CTwentyOne.SKP_Hours
+ losttime.CTwentyOne.SKQ_Hours
+ losttime.CTwentyOne.SKU_Hours
+ losttime.CTwentyOne.UA_Hours ) AS TotalHours,
SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points ) AS TotalPoints
FROM losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.StationCode = '0600'
and losttime.Branches_Table.BranchCategorySmall = 'RSVC'
GROUP BY losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.CTwentyOne.StationCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.ShopSubcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate
ORDER BY losttime.CTwentyOne.StationCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.ShopCode,
losttime.CTwentyOne.ShopSubCode

Response from SQL :Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'IF'.
Server: Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-05 : 13:53:18

IF ... ELSE .... is a control of flow construct and now allowed in a query. You need to use a CASE statement.

...
case when losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate then 'YES' else 'NO' end as C23Due,
...

Jay White
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 13:53:56

Instead of IF, use the CASE statement. I hope that you don't use PRINT in your code though and that this is just a sample query.

Check out this CASE statement from SQL Server Books Online (you can run this code if you have not deleted the pubs database):

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+
RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
Type =
CASE
WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-05 : 14:00:29

The Case statement worked like a dream!!!

Thanks to both of you, and the quick response.

Regards.

GC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources