Creating a sum for the year

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?

[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)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?

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

1. Create a column for YYYYMM using DatePart.
link here: http://msdn2.microsoft.com/en-us/library/aa258265(SQL.80).aspx
Be sure to CONVERT the results into VARCHAR so you can string them together (YYYY + MM).

2. Return that and the columns you want.

3. Put that query into a subquery.

4. On the outer query;
Group by:
Investigator, YYYYMM, ViolationType

5. Do an aggregate on the fields you want to tally
SUM(Money), ...

If you want actual code, would you be so kind as to do the typing for "create table" and "insert" statements to create a little test data. The concept seems pretty universally basic however. I'll see if I can find a table with dates to make an example.

EDIT: Here's a (meaningless) sample ... This returns a monthly tally of "Balance" by "Customer ID" from our Invoices table:
Note: It uses the "DateName" function for readability of the "Month" result ... not sure if that's ANSI Standard.

SELECT
CustomerID,
MM_YYYY,
SUM(Balance) AS Balance
FROM
( SELECT
CustomerID,
Balance,
CONVERT(varchar(10), DATENAME(MM, InvoiceDate)) + ' ' +
CONVERT(varchar(4), DATEPART(YYYY, InvoiceDate)) AS MM_YYYY
FROM tbInvoice
WHERE (InvoiceDate >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102))
) InnerSelect
GROUP BY CustomerID, MM_YYYY|||I'm sure it would help if I showed you all the DDL

CREATE TABLE [IncidentReports] (
[Notes] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_Notes] DEFAULT (N'Scanned Report'),
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_I/RDocument] DEFAULT (N'Scanned Reports'),
[Action Type] [int] NULL ,
[Action] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL CONSTRAINT [DF_IncidentReports_Loss] DEFAULT (0.00),
[Violation Type] [int] NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inspector] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_IncidentReports] PRIMARY KEY CLUSTERED
(
[IR Number]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO|||SELECT MONTH([DATE]) AS MNTH, COUNT([VIOLATION] AS VLT
FROM INCIDENTREPORTS
WHERE [DATE] BETWEEN @.BEGDATE AND @.ENDDATE
GROUP BY MONTH([DATE])

I'll let you fill in the restsql