Total Losses - 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 2005 Forums

Transact-SQL (2005)

Total Losses

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-28 : 19:18:33

Hi all I am trying to create a stored procedure for the users that will give them a total amout lost for the month (total losses) I want it to be a self generating report where the users would enter in date range (example: between 01/01/2008 and 01/31/2008) and the report would generate the total loss for that month or which ever month they choose. here is what i have so far

ALTER PROCEDURE [dbo].[TotalLossesByMonth](@StartDate datetime,
@EndDate datetime)
AS SELECT     Date, COUNT(TypeOfIncident) AS Total, [Investigators Name], TypeOfIncident, SUM(Loss) AS [Total Losses]
FROM         dbo.Report
GROUP BY Date, [Investigators Name], TypeOfIncident
HAVING      (Date BETWEEN @StartDate AND @EndDate)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 00:14:00

why are you grouping by TypeOfIncident and then taking COUNT() of it? this will cause COUNT to always return as 1.


ALTER PROCEDURE [dbo].[TotalLossesByMonth](@StartDate datetime,
@EndDate datetime)
AS SELECT     Date, COUNT(TypeOfIncident) AS Total, [Investigators Name], SUM(Loss) AS [Total Losses]
FROM         dbo.Report
WHERE      (Date BETWEEN @StartDate AND @EndDate)
GROUP BY Date, [Investigators Name]

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-29 : 11:01:58

oh Ok thank you Visakh16 well that explains it then. Thank you so much, I'm still learning. I would like for it to give me a total per inspector, to give me the total losses the inspector has discovered. Does that make sense

ALTER PROCEDURE [dbo].[TotalLossesByMonth](@StartDate datetime,
@EndDate datetime)
AS SELECT     [Date], COUNT ([Loss Type]) AS Total, [Inspector], SUM([Loss]) AS [Total_Losses]
FROM         dbo.Revised_MainTable
WHERE      (Date BETWEEN @StartDate AND @EndDate)
GROUP BY Date, [Inspector]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:08:15

quote:
Originally posted by muzzettemm

oh Ok thank you Visakh16 well that explains it then. Thank you so much, I'm still learning. Now it make sense

Cheers

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-29 : 11:24:17

sorry I should have mentioned that. See it gives the same inspectors name with their total but i would like for it to show the total per inspector (example smith 400.00, Johnson 200.00)

smith 200.00
smith 100.0
smith 100.00

johnson 100.00
johnson 100.00

I think I figured it out in report services

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:25:47

i guess thats because you're grouping by date also. remove it and see


ALTER PROCEDURE [dbo].[TotalLossesByMonth](@StartDate datetime,
@EndDate datetime)
AS SELECT     COUNT ([Loss Type]) AS Total, [Inspector], SUM([Loss]) AS [Total_Losses]
FROM         dbo.Revised_MainTable
WHERE      (Date BETWEEN @StartDate AND @EndDate)
GROUP BY [Inspector]

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-29 : 11:42:54

when I do that sql gives me this error message

Column 'dbo.Revised_MainTable.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:44:40

quote:
Originally posted by muzzettemm

when I do that sql gives me this error message

Column 'dbo.Revised_MainTable.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

can you please use the query as i posted. i dont have Date field in select list.

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-29 : 11:51:58

Oh sorry about that Visakh, thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:53:27