Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Totals not adding up

    Hi all I am running a report and I have not found anything on Msdn that covers this for some reason, either that or I'm just not looking in the right place. I run a report at the end of the month for the Monthly totals that the departments give me. I have to provide a total for the month as well as a total for the year. Now if I'm on December and the total for the month is 12 but for the year its 400 how would I incorporate that into my stored procedure, so it calculates it?

    Code:
    CREATE PROCEDURE [DeptTotals]
     SELECT     COUNT ([Violation Type]) AS Total, [Inspector], SUM([Loss]) AS [Total_Losses]
    FROM         dbo.Revised_MainTable
    GROUP BY [Inspector]

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Huh?

    -PatP

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    date between ???, with a group by month added to inspector.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I have a database that keeps track of the department activity. Every month each department types in their activieis for that month (either by man hours or how many times it was done in that month) I have a report where I have to show what they did for that month also I need it to keep a running total for the year. here is one of the tables, I need to produce this report once a month with monthly and yearly totals. but I'm not sure as to how to do that.




    Code:
    [ScheduledAudits] 
    	[ComplianceReviews] 
    	[SpecialInvestigations] 
    	[P&PReviews_Approvals] 
    	[VCMICSReview_Approval]
    	[MarketingPromo_ReviewApproval] 
    	[GamingRules_Review&Approval] 
    	[ContractReview&Approvals] 
    	[Background_FinancialKeyReview_KeyEmployees] 
    	[BackgroundRenewApproval] 
    	[BackgroundFinalSuitApproval] 
    	[ConstructionProjectReviews] 
    	[ConstructionProjApproved] 
    	[ConstructionPlanReviews] 
    	[CertificatofOccupancyIssued]
    	[EnvirHealthInspections] 
    	[SpecialProjects]

    these are the results I'm trying to get


    January 32 4 16 15 4 17 96


    February 5 3 3 12 2 5 10


    February Monthly totals YTD
    [ComplianceReviews] = 5 37
    [SpecialInvestigations] = 3 7
    [P&PReviews_Approvals] = 3 19
    [VCMICSReview_Approval]= 12 27
    [MarketingPromo_ReviewApproval] = 2 6
    [GamingRules_Review&Approval] = 5 22
    [ContractReview&Approvals] = 10 106
    Last edited by desireemm; 02-24-09 at 13:41.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Heres is a picture I hope this helps


    Code:
    CREATE TABLE [dbo].[Technicians](
    	[CameraRepair] [numeric](18, 0) NULL CONSTRAINT [DF_Technicians_CameraRepair]  DEFAULT ((0)),
    	[CableRepair] [numeric](18, 0) NULL CONSTRAINT [DF_Technicians_CableRepair]  DEFAULT ((0)),
    	[CameraMaintenance_Cleaning] [numeric](18, 0) NULL CONSTRAINT [DF_Technicians_CameraMaintenance_Cleaning]  DEFAULT ((0)),
    	[DVRMaintenance_Cleaning] [numeric](18, 0) NULL CONSTRAINT [DF_Technicians_DVRMaintenance_Cleaning]  DEFAULT ((0)),
    	[TechAreaCleaning] [numeric](18, 0) NULL CONSTRAINT [DF_Technicians_TechAreaCleaning]  DEFAULT ((0)),
    	[Moves_Installs] [numeric](18, 0) NULL CONSTRAINT [DF_Technicians_Moves_Installs]  DEFAULT ((0)),
    	[Other] [numeric](18, 0) NULL CONSTRAINT [DF_Technicians_Other]  DEFAULT ((0)),
    	[TechnicianID] [int] IDENTITY(1,1) NOT NULL,
    	[Start_Date] [datetime] NULL,
    	[End_Date] [datetime] NULL,
     CONSTRAINT [PK_Technicians] PRIMARY KEY CLUSTERED





    here is what I have so far, but what I am tryign to do is when I execute this I want it to give me the Stats for one month at a time along with the YTD. Does that make sense??


    Code:
    ALTER PROCEDURE [dbo].[YTDTechniciansDept]
    AS
    SELECT DATENAME(mm,Date) AS Month,
    MonthCameraRepairCount,
    MonthCableRepairCount,
    MonthCameraMaintenance_CleaningCount,
    MonthDVRMaintenance_CleaningCount,
    MonthTechAreaCleaningCount,
    MonthMoves_InstallsCount,
    MonthOtherCount,
    YTDCameraRepairCount,
    YTDCableRepairCount,
    YTDCameraMaintenance_CleaningCount,
    YTDDVRMaintenance_CleaningCount,
    YTDTechAreaCleaningCount,
    YTDMoves_InstallsCount,
    YTDOtherCount
    FROM 
    (
    SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
          sum(CameraRepair) AS MonthCameraRepairCount,
           sum(CableRepair) AS MonthCableRepairCount,
           sum(CameraMaintenance_Cleaning) AS MonthCameraMaintenance_CleaningCount,
          SUM(DVRMaintenance_Cleaning) AS MonthDVRMaintenance_CleaningCount,	
    	sum(TechAreaCleaning) AS MonthTechAreaCleaningCount,
    	SUM(Moves_Installs) AS MonthMoves_InstallsCount,
    	SUM(Other) AS MonthOtherCount
    
    FROM Technicians
    GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
    )t
    CROSS APPLY
    (
    SELECT SUM(CameraRepair) AS YTDCameraRepairCount,
           SUM(CableRepair) AS YTDCableRepairCount,
           SUM(CameraMaintenance_Cleaning) AS YTDCameraMaintenance_CleaningCount,
           SUM(DVRMaintenance_Cleaning) AS YTDDVRMaintenance_CleaningCount,
    	SUM(TechAreaCleaning) AS YTDTechAreaCleaningCount,
    	SUM(Moves_Installs) AS YTDMoves_InstallsCount,
    SUM(Other) AS YTDOtherCount
    
    FROM Technicians
    WHERE [Start_Date] <DATEADD(mm,1,Date)
    )t1
    Last edited by desireemm; 04-15-09 at 17:23.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT DateAdd(mm, DateDiff(mm, 0, a.some_date), 0) As monthyness
         , Sum(a.value) As monthly_sum
         , b.yearly_sum
    FROM   your_table As a
     CROSS
     APPLY (
            SELECT Sum(value) As yearly_sum
            FROM   your_table
            WHERE  some_date >= '20080201'
            AND    some_date <  '20080301'
           ) As b
    WHERE  a.some_date >= '20080201'
    AND    a.some_date <  '20080301'
    GROUP
        BY DateAdd(mm, DateDiff(mm, 0, a.some_date), 0)
    
    
    
    --OR
    ; WITH cte AS (
      SELECT DateAdd(mm, DateDiff(mm, 0, a.some_date), 0) As monthyness
           , Sum(value) As monthly_sum
      FROM   your_table
      WHERE  a.some_date >= '20080201'
      AND    a.some_date <  '20080301'
      GROUP
          BY DateAdd(mm, DateDiff(mm, 0, a.some_date), 0)
    )
    SELECT a.monthyness
         , a.monthly_sum
         , b.yearly_sum
    FROM   cte As a
     CROSS
     APPLY (
            SELECT Sum(monthly_sum) As yearly_sum
            FROM cte
           ) As b
    ?
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Here is what I have so far but I am getting an error message

    Code:
    SELECT YEAR = YEAR(End_Date),    
           CameraRepair, CableRepair, CameraMaintenance_Cleaning, DVRMaintenance_Cleaning, TechAreaCleaning,
    		Moves, Other    
           COALESCE(CONVERT(VARCHAR,SUM(CASE    
                                          WHEN MONTH(End_Date) = 1 THEN CameraRepair, CableRepair, CameraMaintenance_Cleaning, DVRMaintenance_Cleaning, TechAreaCleaning,
    		Moves, Other  
                                        END)),'') AS 'JAN',    
           COALESCE(CONVERT(VARCHAR,SUM(CASE    
                                          WHEN MONTH(End_Date) = 2 THEN CameraRepair, CableRepair, CameraMaintenance_Cleaning, DVRMaintenance_Cleaning, TechAreaCleaning,
    		Moves, Other     
                                        END)),'') AS 'FEB',    
           COALESCE(CONVERT(VARCHAR,SUM(CASE    
                                          WHEN MONTH(End_Date) = 3 THEN CameraRepair, CableRepair, CameraMaintenance_Cleaning, DVRMaintenance_Cleaning, TechAreaCleaning,
    		Moves, Other   
                                        END)),'') AS 'MAR',    
           COALESCE(CONVERT(VARCHAR,SUM(CameraRepair, CableRepair, CameraMaintenance_Cleaning, DVRMaintenance_Cleaning, TechAreaCleaning,
    		Moves, Other)),'') AS 'YTD'    
    FROM     Technicians
    WHERE    YEAR(End_Date) = YEAR(getdate())    
    GROUP BY YEAR(End_Date),    
             CameraRepair, CableRepair, CameraMaintenance_Cleaning, DVRMaintenance_Cleaning, TechAreaCleaning,
    		Moves, Other  
    ORDER BY CameraRepair 
    GO

    Msg 156, Level 15, State 1, Line 6
    Incorrect syntax near the keyword 'COALESCE'.
    Last edited by desireemm; 02-25-09 at 18:18.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by gvee
    Code:
    SELECT DateAdd(mm, DateDiff(mm, 0, a.some_date), 0) As monthyness
         , Sum(a.value) As monthly_sum
         , b.yearly_sum
    FROM   your_table As a
     CROSS
     APPLY (
            SELECT Sum(value) As yearly_sum
            FROM   your_table
            WHERE  some_date >= '20080201'
            AND    some_date <  '20080301'
           ) As b
    WHERE  a.some_date >= '20080201'
    AND    a.some_date <  '20080301'
    GROUP
        BY DateAdd(mm, DateDiff(mm, 0, a.some_date), 0)
    
    
    
    --OR
    ; WITH cte AS (
      SELECT DateAdd(mm, DateDiff(mm, 0, a.some_date), 0) As monthyness
           , Sum(value) As monthly_sum
      FROM   your_table
      WHERE  a.some_date >= '20080201'
      AND    a.some_date <  '20080301'
      GROUP
          BY DateAdd(mm, DateDiff(mm, 0, a.some_date), 0)
    )
    SELECT a.monthyness
         , a.monthly_sum
         , b.yearly_sum
    FROM   cte As a
     CROSS
     APPLY (
            SELECT Sum(monthly_sum) As yearly_sum
            FROM cte
           ) As b
    ?

    thank you George, is this correct??

    Code:
    SELECT DateAdd(mm, DateDiff(mm, 0, a.End_Date), 0) As monthly
         , Sum(a.CameraRepair) As MonthCameraRepair, SUM(a.CableRepair) AS MonthCableRepair, SUM(a.CameraMaintenance_Cleaning) AS MonthCameraMaintenance, SUM(a.DVRMaintenance_Cleaning) AS MonthDVRMaintenance_Cleaning, SUM(a.TechAreaCleaning) AS MonthTechAreaCleaning,
    		SUM(a.Moves) AS MonthMoves, SUM(a.Other) As MonthOther
         , b.yearly_sum
    FROM   Technicians As a
     CROSS
     APPLY (
            SELECT Sum((b.CameraRepair) As YearlyCameraRepair, SUM(b.CableRepair) AS YearlyCableRepair, SUM(b.CameraMaintenance_Cleaning) AS YearlyCameraMaintenance, SUM(b.DVRMaintenance_Cleaning) AS YearlyDVRMaintenance_Cleaning, SUM(b.TechAreaCleaning) AS YearTechAreaCleaning,
    		SUM(b.Moves) AS YearlyMoves, SUM(b.Other) As YearlyOther
            FROM   Technicians
            WHERE  End_Date >= '20080201'
            AND    End_Date <  '20080301'
           ) As b
    WHERE  a.End_Date >= '20080201'
    AND    a.End_Date <  '20080301'
    GROUP
        BY DateAdd(mm, DateDiff(mm, 0, a.End_Date), 0)

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You haven't got a column called yearly_sum in the derived table...
    Code:
    SELECT DateAdd(mm, DateDiff(mm, 0, a.End_Date), 0) As monthly
          , Sum(a.CameraRepair) As MonthCameraRepair
          , SUM(a.CableRepair) AS MonthCableRepair
          , SUM(a.CameraMaintenance_Cleaning) AS MonthCameraMaintenance
          , SUM(a.DVRMaintenance_Cleaning) AS MonthDVRMaintenance_Cleaning
          , SUM(a.TechAreaCleaning) AS MonthTechAreaCleaning
          , SUM(a.Moves) AS MonthMoves, SUM(a.Other) As MonthOther
          , b.YearlyCameraRepair
          , b.YearlyCableRepair
          , b.YearlyCameraMaintenance
          , b.YearlyDVRMaintenance_Cleaning
          , b.YearTechAreaCleaning
          , b.YearlyMoves
          , b.YearlyOther
    FROM   Technicians As a
     CROSS
     APPLY (
            SELECT Sum((b.CameraRepair) As YearlyCameraRepair
                  , SUM(b.CableRepair) AS YearlyCableRepair
                  , SUM(b.CameraMaintenance_Cleaning) AS YearlyCameraMaintenance
                  , SUM(b.DVRMaintenance_Cleaning) AS YearlyDVRMaintenance_Cleaning
                  , SUM(b.TechAreaCleaning) AS YearTechAreaCleaning
                  , SUM(b.Moves) AS YearlyMoves
                  , SUM(b.Other) As YearlyOther
            FROM   Technicians
            WHERE  End_Date >= '20080201'
            AND    End_Date <  '20080301'
           ) As b
    WHERE  a.End_Date >= '20080201'
    AND    a.End_Date <  '20080301'
    GROUP
        BY DateAdd(mm, DateDiff(mm, 0, a.End_Date), 0)
    Formatting your code makes that easier to spot


    Just a note - you may have to aggregate the values from the alias b as they are not contained within the group by clause, or indeed move them to the group by clause...
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you gvee when I execute it I get this error message

    Msg 102, Level 15, State 1, Line 19
    Incorrect syntax near ','.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is the syntax error in the exact code I posted above or a modified version you've put together? Post the exact code you're playing with please.
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    the exact code you put, and yes I am on sql server 2005

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
     CROSS
     APPLY (
            SELECT Sum((b.CameraRepa...
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I took that out but and executed it and now I get


    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "b.CameraRepair" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "b.CableRepair" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "b.CameraMaintenance_Cleaning" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "b.DVRMaintenance_Cleaning" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "b.TechAreaCleaning" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "b.Moves_Installs" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "b.Other" could not be bound.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    feh -- what's wrong with good old CROSS JOIN, which seems far more appropriate here

    my understanding of APPLY is that it's intended for table expressions (e.g. functions) which rely on the value(s) of some column(s) in the left table

    e.g.
    Code:
    SELECT S.SupplierID, ProductID
    FROM dbo.Suppliers AS S
    OUTER APPLY dbo.fn_top_products(S.SupplierID,1,2) AS P
    (sample from Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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