Results 1 to 5 of 5

Thread: Yearly total

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

    Unanswered: Yearly total

    Hi guys I have to have a report on report services that shows the monthly totals along with the total for the whole year. there is a report at the end of the month for the departments, each deparmtent has their own report
    and for every item. Its itemized and so I have to show how many per item and the total for the year right next to it. Can anyone help pls?? thank you


    January Monthly totals YTD
    [How many Installs] 10 5
    Maintenance 5 10

    February Monthly total YTD
    [How many 10 20 installs
    [Maintenance] 5 10





    Code:
    [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 
    (

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you have been posting here long enough to know you get better help when you post what you have tried so far.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    sorry didnt show you the code
    when I execute this it will give me the whole year as users enter in data, I would like to break it down by month. Cause the stats are due once a month, so when I have the reports sitting on report services for the user to see they will see thier stats my month rather then the whole year. There will be a report thats for January 2009 and then February 2009..etc does that make sense

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[YTDAdministrativeHrng_Sp]
    as
    
    SELECT DATEPART(mm,Date) AS Month,
    MonthTotalActiveSelfExclusionsCount,
    MonthQrtlyAdditionsCount,
    MonthSelfExclusionsHearingsCount,
    MonthExpiredSelfExclusionsCount,
    MonthLicenseHearingCount,
    MonthPermitHearingCount,
    MonthReinstatedCount,
    MonthRevokedCount,
    MonthRequestedCount,
    MonthGrantedCount,
    YTDTotalActiveSelfExclusionsCount,
    YTDQrtlyAdditionsCount,
    YTDNotificationsCount,
    YTDUpgradesToKeyPosition
    FROM 
    (
    SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
           SUM(RenewalsSenttoState) AS MonthRenewalsSenttoStateCount,
           SUM(NewApplicantsSenttoState) AS MonthNewApplicantsSenttoStateCount,
           SUM(Denials) AS MonthDenialsCount,
           SUM(Revocation) AS MonthRevocationCount,
    		SUM(Suspension) AS MonthSuspensionCount,
    		SUM(Notifications) AS MonthNotificationsCount,
    		SUM(UpgradesToKeyPosition) AS MonthUpgradesToKeyPosition
    
    FROM StateLicensing
    GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
    )mn
    CROSS APPLY
    (
    SELECT sum(RenewalsSenttoState) AS YTDRenewalsSenttoStateCount,
           sum(NewApplicantsSenttoState) AS YTDNewApplicantsSenttoStateCount,
           sum(Denials) AS YTDDenialsCount,
           sum(Revocation) AS YTDRevocationCount,
    	sum(Suspension) AS YTDSuspensionCount,
    	sum(Notifications) AS YTDNotificationsCount,
    	sum(UpgradesToKeyPosition) AS YTDUpgradesToKeyPosition
    FROM StateLicensing
    WHERE [Start_Date] <DATEADD(mm,1,Date)
    )rn
    Last edited by desireemm; 12-22-08 at 12:52.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    this code will give me total for the month but I need month and year

    Code:
    SELECT     SUM(TotalActiveSelfExclusions) AS TotalActiveExcl, SUM(QrtlyAdditions) AS TotalQrtly, SUM(SelfExclusionsHearings) AS TotalSelfExclusions, 
                          SUM(ExpiredSelfExclusions) AS TotalExpiredSelfExcl, SUM(LicenseHearing) AS TotalLicenseHearing, SUM(PermitHearing) AS TotalPermitHearings, 
                          SUM(Reinstated) AS TotalReinstated, SUM(Revoked) AS TotalRevoked, SUM(Requested) AS TotalRequested, SUM(Granted) AS TotalGranted, 
                          Start_Date, End_Date
    FROM         AdministrativeHearing_Legal
    GROUP BY Start_Date, End_Date
    HAVING      (Start_Date = CONVERT(DATETIME, '2008-01-01 00:00:00', 102)) AND (End_Date = CONVERT(DATETIME, '2008-01-31 00:00:00', 102))

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

    Talking

    Nevermind finally figured out what was wrong with my code. this is correct, was totally missing allot

    Code:
    SELECT DATEPART(mm,Date) AS Month,
    MonthITAudtiCount,
    MonthInternalApplicationsCount,
    MonthMonitorCasinoIt_OpsProjCount,
    MonthInternalDatabaseMaintenance_SupportCount,
    MonthSecurityAdminstrationCount,
    MonthInvestigationsCount,
    MonthDesktopSupportCount,
    MonthProfessionalDevelopmentCount,
    YTDITAudtiCount,
    YTDInternalApplicationsCount,
    YTDMonitorCasinoIt_OpsProjCount,
    YTDInternalDatabaseMaintenance_SupportCount,
    YTDSecurityAdminstrationCount,
    YTDInvestigationsCount,
    YTDDesktopSupportCount,
    YTDProfessionalDevelopmentCount
    FROM 
    (
    SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
          sum(ITAudti) AS MonthITAudtiCount,
           sum(InternalApplications) AS MonthInternalApplicationsCount,
           sum(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
          SUM(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,	
    	sum(SecurityAdminstration) AS MonthSecurityAdminstrationCount,
    	SUM(Investigations) AS MonthInvestigationsCount,
    	SUM(DesktopSupport) AS MonthDesktopSupportCount,
    	sum(ProfessionalDevelopment) AS MonthProfessionalDevelopmentCount
    
    FROM ITDepartment
    GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
    )t
    CROSS APPLY
    (
    SELECT SUM(ITAudti) AS YTDITAudtiCount,
           SUM(InternalApplications) AS YTDInternalApplicationsCount,
           SUM(MonitorCasinoIt_OpsProj) AS YTDMonitorCasinoIt_OpsProjCount,
           SUM(InternalDatabaseMaintenance_Support) AS YTDInternalDatabaseMaintenance_SupportCount,
    	SUM(SecurityAdminstration) AS YTDSecurityAdminstrationCount,
    SUM(Investigations) AS YTDInvestigationsCount,
    SUM(DesktopSupport) As YTDDesktopSupportCount,
    SUM(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
    FROM ITDepartment
    WHERE [Start_Date] <DATEADD(mm,1,Date)
    )t1

Posting Permissions

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