Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: 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)

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by desireemm
    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/lib...5(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.
    Code:
    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
    Last edited by vich; 04-09-07 at 20:42.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ddl

    I'm sure it would help if I showed you all the DDL
    Code:
    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

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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 rest
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •