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.
| Author |
Topic |
|
jewelfire
Starting Member
4 Posts |
 Posted - 2006-08-13 : 20:04:06
|
| hey thereI am using Visual Studio.net and deploying to Reporting Services.my report has the 7 days in columns.and one colum for callsubject.I think I need to do an if statement but unsure how to tackle it.eg case statement to check if the day of the week is Monday, and if so, select the time, else select zero, then another case statement for Tuesday, Wednesday, etc. Then you will have columns of data returned for the call subject, Monday time, Tuesday time, etc. how would I do this code??this is what I have so far without the case statement so you get the gist.SELECT CONVERT(varchar(11), dbo.Worktime.WorkDateTime) AS WorkDate, dbo.Worktime.WorkDateTime, dbo.Worktime.WorkHours, dbo.Worktime.WorkMins, dbo.Worktime.IthelpUser, dbo.Call.CallSubject3FROM dbo.Worktime INNER JOIN dbo.Call ON dbo.Worktime.CallNumber = dbo.Call.CallNumberWHERE (dbo.Worktime.WorkDateTime >= @startDate + ' 00:00') AND (dbo.Worktime.WorkDateTime <= @Enddate + ' 23:59') AND (dbo.Worktime.IthelpUser = @PersonName)ORDER BY dbo.Worktime.IthelpUser, dbo.Worktime.WorkDateTimethanks for your helpThis is my first time posting here so please if I have done something incorrectly just let me know.jewel |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-08-14 : 00:25:48
|
| What data do you want to put in the columns? If, for example, you wanted to put the total number of WorkHours in the columns, you could do something like this:SELECT SUM(CASE WHEN DATEPART(dw, dbo.Worktime.WorkDateTime) = 1 THEN dbo.Worktime.WorkHours END) AS Sunday, SUM(CASE WHEN DATEPART(dw, dbo.Worktime.WorkDateTime) = 2 THEN dbo.Worktime.WorkHours END) AS Monday, etc... |
 |
|
|
|
|
|
|
|
weekly timesheet