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

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?

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

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.
|||

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

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

WHERE {what} BETWEEN @.StartDate AND @.EndDate

|||

Ok I did that now the error message is

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

|||

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

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

|||

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

|||

Is this a stored procedure?

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

Do you provide those parameters with values anywhere?

|||

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]

|||

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.

|||

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.

|||

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]));

Creating a Select statement with subqueries to 3 other tables...

I have four total tables.

Table One (Documents)- List of Documents. Each record has two fields
related to this issue. First field (Document_ID) is the ID of the
document, second field is the ID of the record (Task_ID) it is
associated to in Table Two.

Table Two (Activities)- List of activities. Each record has two fields
related to this issue. First field (Activity_ID) is the ID of the
activity, the second field (Group_ID) is the ID of the record it is
associated to in Table Three.

Table Three (Groups) - List of groups. Each record has two fields
related to this issue. First field (Group_ID) is the ID of the group,
the second field (Stage_ID) is the ID of the record it is associated to
in Table four.

Table Four (Stages)- List of Event Stages. Each record has two fields
that is related to this issue. The first field (Stage_ID) is the ID of
the stage of an event, the second record is the ID number associated to
the event. This last ID is a known value.

20000024 = the Event ID

I'm trying to come up with a list of Documents from the first table
that is associated to an Event in the Fourth table.

Query Analyzer shows no errors within the script. It just doesn't
return any data. I know that it should, if it does what I'm wanting it
to do.

SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE
(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID
= A.Activity_ID))))))= '20000024')Wenin wrote:
> I have four total tables.
>
> Table One (Documents)- List of Documents. Each record has two fields
> related to this issue. First field (Document_ID) is the ID of the
> document, second field is the ID of the record (Task_ID) it is
> associated to in Table Two.
> Table Two (Activities)- List of activities. Each record has two
fields
> related to this issue. First field (Activity_ID) is the ID of the
> activity, the second field (Group_ID) is the ID of the record it is
> associated to in Table Three.
> Table Three (Groups) - List of groups. Each record has two fields
> related to this issue. First field (Group_ID) is the ID of the
group,
> the second field (Stage_ID) is the ID of the record it is associated
to
> in Table four.
> Table Four (Stages)- List of Event Stages. Each record has two
fields
> that is related to this issue. The first field (Stage_ID) is the ID
of
> the stage of an event, the second record is the ID number associated
to
> the event. This last ID is a known value.
> 20000024 = the Event ID
> I'm trying to come up with a list of Documents from the first table
> that is associated to an Event in the Fourth table.
> Query Analyzer shows no errors within the script. It just doesn't
> return any data. I know that it should, if it does what I'm wanting
it
> to do.
>
> SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
> Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C
WHERE
> (C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE
(B.Activity_ID
> = A.Activity_ID))))))= '20000024')

I'm thinking this is going to require the use of Joins, but I still
can't seem to wrap my head around how joins work exactly.|||First, to save Joe Celko from having to post...

Rows are not records, columns are not fields. You are thinking in the
old sequential file processing mentality. It will take you a year to
unlearn this and get your mind accustomed to thinking in a set-oriented
fashion.

Ok, now that that's out of the way...

You are correct, it will require the use of joins. Please don't take
this as an insult, as I'm sure that you are still learning SQL and
there was a time for all of us when we didn't know it, but this is a
pretty simple set of joins. You really do need to "get your head around
how joins work exactly" if you are going to be doing any SQL coding.
There are plenty of good books for SQL beginners out there. Even the
SQL for Dummies book should get you past this hurdle.

A join takes each table being joined, creates a cartesian product of
the two (i.e., every possible combination of records) then filters that
result based on the ON part of the join. As an example, if I have two
tables:

Customers
cust_id
1
2

Orders
ord_id cust_id
1 1
2 2
3 1

The cartesian product would be:
cust_id ord_id cust_id
1 1 1
1 2 2
1 3 1
2 1 1
2 2 2
2 3 1

If I joined these tables on cust_id = cust_id then it would give me:
cust_id ord_id cust_id
1 1 1
1 3 1
2 2 2

Without the join criteria the cartesian product is useless, but it is
the starting point.

Another way to think of it is this... I know my two tables are related
and I know the column that relates them. Therefore I can join on that
column. That is a VERY simplistic approach to joins, but it will
hopefully point you in the right direction.

Good luck,
-Tom.