Creating a Sum for the Year

muzzettemm1

Hi all I have to create a query that would give the users a a total amount of each field for the month or year.

I just need it to give me a total amount of each item for the month. So basically what happens is that the user enters in a beginning date and an ending date for the month or year and this report gives them the [INVESTIGATOR], [VIOLATION TYP], [DATE], [TOTAL LOSS].

I just want to show how many vilolationtypes per investigator so if Smith had 40 in one month I would need to reflect that in the report. does that make sense

Code Snippet
[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)

Does this look right

Code Snippet
SELECT Month([Date]) AS Mnth, COUNT([Violation]) AS VlT
FROM Revised_MainTable
WHERE [Date] BETWEEN @BeginDate AND @EndDate
GROUP BY Month([Date])
GO

Re: Creating a Sum for the Year

davidbrit2

Try: GROUP BY Year([Date]), Month([Date])

If you only group on the month, then, for example, May 2006 would be grouped with May 2005.

Re: Creating a Sum for the Year

muzzettemm1

this is the error message I get when I exectute that

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'BETWEEN'.

Code Snippet

SELECT Month([Date]) AS Mnth, COUNT([Violation]) AS VlT
FROM Revised_MainTable
WHERE BETWEEN @StartDate AND @EndDate
GROUP BY Year([Date]), Month([Date])
GO

Re: Creating a Sum for the Year

davidbrit2

Looks like you're missing the [Date] column name before the BETWEEN keyword in that second excerpt.

Re: Creating a Sum for the Year

Arnie Rowland

WHERE {what} BETWEEN @StartDate AND @EndDate

Re: Creating a Sum for the Year

muzzettemm1

Ok I did that now the error message is

Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@StartDate'.

Re: Creating a Sum for the Year

Arnie Rowland

In your first post, you used @BeginDate, and then in a later post, you used @StartDate.

Perhaps you need to change this to @BeginDate...

Re: Creating a Sum for the Year

muzzettemm1

Now I'm getting

Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@StartDate'.

Here's the DDL

Code Snippet

CREATE TABLE [Revised_MainTable] (
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Revised_MainTable_I/RDocument] DEFAULT (N'Scanned Report'),
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL CONSTRAINT [DF_Revised_MainTable_Loss] DEFAULT (0.0000),
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Revised_MainTable_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Revised_MainTable] PRIMARY KEY CLUSTERED
(
[IR Number]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Re: Creating a Sum for the Year

Arnie Rowland

Is this a stored procedure

Do you have the parameters @StartDate, @BeginDate, or @EndDate defined

Do you provide those parameters with values anywhere

Re: Creating a Sum for the Year

muzzettemm1

thank you so much it works no thank you

One more question please, how would I get it to calculate each violation. lets say if theres 6 Thefts how would I get it to tell me that plus the total amount that was lost

Code Snippet

SELECT MONTH(Date) AS Mnth, COUNT([Violation Type]) AS [Violation Type], SUM(Loss) AS [Sum Of Loss], [Violation Type] AS Violation
FROM dbo.Revised_MainTable
WHERE (Date BETWEEN @StartDate AND @EndDate)
GROUP BY YEAR(Date), MONTH(Date), [Violation Type]

Re: Creating a Sum for the Year

Arnie Rowland

Assuming that [Violation] and [Violation Type] are not the same thing, you could use the same query, substituting one for the other -and it already had the sum of loss included.

Otherwise, if [Violation] is a sub-set of [Violation Type], then add [Violation] to the GROUP BY.

If my assumption are incorrect, send more information.

Re: Creating a Sum for the Year

Dhericean

I would just like to mention one thing about your use of @StartDate and @EndDate.

Be aware that as these are datetimes they can mark a point in time. If you are using these as date only then you should make sure that they all indicate midnight at the start of the day (the default). The reason for this is that if the Date field ever manages to get time information in it as well then checking against an @EndDate that is midnight at the start of the day will not catch any offences which are for times later in the day.

Re: Creating a Sum for the Year

muzzettemm1

See this is the query the guy built and based his reports off of, I inherited this database and I need to make it into an ADP where sQL server is the engine not access.

But the thing is that Jet SQL and SQL Server are a little different. this is the query he uses in access this the sql view of it. and I'm trying to turn it into SQL server but I'm not getting the same results he's getting.

Code Snippet

SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));