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 |
|
JTProg
Starting Member
24 Posts |
 Posted - 2006-07-19 : 08:53:52
|
| How could I exclude weekends from this query.SELECT 'Department' = CASE Department + ' ' + Route WHEN 'AM TC' THEN 'Joe' WHEN 'AT TC' THEN 'Lory' WHEN 'CT TC' THEN 'Willy' Department AS 'Ind. Department', SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TCDate) Between 0 and 1 Then 1 Else 0 End)as '0-1', SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TCDate) Between 2 and 3 Then 1 Else 0 End)as '2-3', SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TCDate) Between 4 and 5 Then 1 Else 0 End)as '4-5', SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TCDate) Between 6 and 7 Then 1 Else 0 End)as '6-7', SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TCDate) Between 8 and 12 Then 1 Else 0 End)as '8-12', SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TCDate) Between 13 and 16 Then 1 Else 0 End)as '12-16', SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TCDate) Between 17 and 255 Then 1 Else 0 End)as '16-255' FROM dbo.Batch INNER JOIN dbo.ArchiveQue ON dbo.Batch.BMIBatch = dbo.ArchiveQue.BMIBatch WHERE (dbo.Batch.Status = '20') AND (dbo.ArchiveQue.Route = 'TC') GROUP BY Department, Route ORDER BY Department, Route Desc |
|
|
humanpuck
Yak Posting Veteran
94 Posts |
Posted - 2006-07-19 : 08:58:57
|
| where datepart(dw,dbo.Batch.PreparedDate) > 1 anddatepart(dw,dbo.Batch.PreparedDate) <=6 |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-19 : 09:00:03
|
| where datename(dw,dte) not in ('Saturday','Sunday')humanpuck's will depend on the datefirst setting, this will depend on the language.Take your pick.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-19 : 09:33:44
|
This is independent of datefirst and language settings:where datediff(dd,-53690,Batch.PreparedDate)%7 > 4 Edit:Just to explain what that is, it is the same as this:where datediff(dd,'17530101',Batch.PreparedDate)%7 > 4 What it is doing is getting the number of days since 1753-01-01 (a Monday, and the lowest possible SQL Server date), and getting the modulus 7 (remainder from division by 7). Saturday and Sunday will be 5 and 6. Monday will be 0, Tuesday will be 1, etc.CODO ERGO SUM |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-07-19 : 09:47:48
|
quote:
Originally posted by Michael Valentine Jones
This is independent of datefirst and laqnguage settings:where datediff(dd,-53690,Batch.PreparedDate)%7 > 4
|
|
|
|
JTProg
Starting Member
24 Posts |
Posted - 2006-07-20 : 11:12:19
|
| Thank you for the replies. With this working correctly now, how can I list the averages next to the summed values. Example, if I want to see the average for Joe in comparison to Willy from the summed values.I would like it to read something like:Department 0-1 AVG 2-3 AVGJoe 7 1.5 2 1.2Lorry 6 1.1 1 1.1Willy 4 1.3 2 1.6Total 17 3.9 5 3.9 |
|
|
|
humanpuck
Yak Posting Veteran
94 Posts |
Posted - 2006-07-20 : 11:20:47
|
| I'd put your first query in a view and select against it.select department, 0-1,avg(0-1),2-3,avg(2-3)from myViewgroup by department,0-1,2-3 |
|
|
|
|
|
|
|
|
Basic Calculations