A little more logic - 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)

A little more logic

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-05 : 15:00:20

Thanks to all for the quick response on my last post. With your answers I now have another question.

In my query and with your help I ask the following.

CASE WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate
THEN 'YES' ELSE 'NO' END AS C23Due,
DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate())AS DaysOverDue,
DATEDIFF(dd,CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.C23DiscussionDate)AS DaysC23CompletedIn,

Is there a way for SQL to do the following.

1: If the new column "C23Due" response is "NO" can I get the column "DaysOverDue" to show "Completed" instead of the Day Diff. number?

2: If the response in the new column "DaysC23CompletedIn" is a negative number can I get it to show "Needed" instead?

Thanks again.

GC

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 16:49:20

Why not...


SELECT    CASE WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate 
		THEN 'YES' 
		ELSE 'NO' 
	  END AS C23Due
	, CASE WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate
		THEN 'Completed'
		ELSE 
		CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate()))
	  END AS DaysOverDue
	, 
	  CASE WHEN DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate) < 0
		THEN "Needed"
		ELSE 
		CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate))
	  END AS DaysC23CompletedIn,

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-05 : 17:13:34

Brett,

Just gave it a try and here is the response.

Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near ')'.

Here is the entire query.

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,
CASE WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate
THEN 'YES' ELSE 'NO' END AS C23Due,
CASE WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate
THEN 'Completed'ELSE CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate,
getdate())) END AS DaysOverDue,
CASE WHEN DATEDIFF(dd,CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.C23DiscussionDate) < 0 THEN "Needed" ELSE CONVERT(varchar(20),
DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate))
END AS DaysC23CompletedIn,
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'
and losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate
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

Thanks for your help with this.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 17:22:16

You've got a type-o at line 22. There is a ) instead of a space before the AS. When you get an error in Query Analyzer, just double click on the error for it to jump to that line.

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-05 : 17:29:37

Tara,

That did the trick! And thanks for the tip, didn't know it would do that when you clicked on it.

Thanks again to you, Brett, and Jay for all your help today. Have a nice weekend.

GC

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 22:41:41

Damn...this is an an addiction...

I'm glad it worked..

hey, for the future...think small and build...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources