Query Help - 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)

Query Help

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2006-08-25 : 11:59:20

How can I display the a the precise value in the last column instead of just a whole number? I'm getting 1 or 2 instead of something like 1.3, 2.8, etc....

Declare @Month int

Set @Month = 7

SELECT Department,
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 0 Then 1 Else 0 End)as '0',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 1 Then 1 Else 0 End)as '1',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 2 Then 1 Else 0 End)as '2',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 3 Then 1 Else 0 End)as '3',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 4 Then 1 Else 0 End)as '4',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 5 Then 1 Else 0 End)as '5',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 6 Then 1 Else 0 End)as '6',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 7 Then 1 Else 0 End)as '7',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) Between 0 and 255 Then 1 Else 0 End)as 'Total',
(0 + 1*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 1 Then 1 Else 0 End))+ 2*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 2 Then 1 Else 0 End))+ 3*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 3 Then 1 Else 0 End))+ 4*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 4 Then 1 Else 0 End))+ 5*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 5 Then 1 Else 0 End))+ 6*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 6 Then 1 Else 0 End))+ 7*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 7 Then 1 Else 0 End)))/(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) Between 0 and 255 Then 1 Else 0 End)) AS 'Averages'
FROM dbo.Batch INNER JOIN
dbo.ArchiveQue ON dbo.Batch.BMIBatch = dbo.ArchiveQue.BMIBatch
WHERE ((dbo.Batch.Status = '20') OR (dbo.Batch.Status = '1')) AND (DATEPART(month, dbo.ArchiveQue.TeamCoordinatorDate) = @Month)
GROUP BY Department
ORDER BY Department

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-25 : 12:07:03

Cast one of the operands as a float, so your calculation must be of the form
cast(intexpr as float) / intexpr

If I have your query right, it should become

Declare @Month int

Set @Month = 7

SELECT Department,
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 0 Then 1 Else 0 End)as '0',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 1 Then 1 Else 0 End)as '1',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 2 Then 1 Else 0 End)as '2',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 3 Then 1 Else 0 End)as '3',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 4 Then 1 Else 0 End)as '4',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 5 Then 1 Else 0 End)as '5',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 6 Then 1 Else 0 End)as '6',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 7 Then 1 Else 0 End)as '7',
SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) Between 0 and 255 Then 1 Else 0 End)as 'Total',
cast((0 + 1*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 1 Then 1 Else 0 End))+ 2*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 2 Then 1 Else 0 End))+ 3*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 3 Then 1 Else 0 End))+ 4*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 4 Then 1 Else 0 End))+ 5*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 5 Then 1 Else 0 End))+ 6*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 6 Then 1 Else 0 End))+ 7*(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) = 7 Then 1 Else 0 End))) as float)/(SUM(Case when datediff(d, dbo.Batch.PreparedDate, dbo.ArchiveQue.TeamCoordinatorDate) Between 0 and 255 Then 1 Else 0 End)) AS 'Averages'
FROM dbo.Batch INNER JOIN
dbo.ArchiveQue ON dbo.Batch.BMIBatch = dbo.ArchiveQue.BMIBatch
WHERE ((dbo.Batch.Status = '20') OR (dbo.Batch.Status = '1')) AND (DATEPART(month, dbo.ArchiveQue.TeamCoordinatorDate) = @Month)
GROUP BY Department
ORDER BY Department

JTProg
Starting Member

24 Posts

Posted - 2006-08-25 : 13:19:58

This works great. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources