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

    Unanswered: Year To Date Table

    Hi guys I have a question, dont know if this is the best approach though. I have a database that has 11 tables in it, each table represents a department. Now once a month the departments but there stats for the month into thier designated table. The data from this table will show in a report in report services, what the boss wants is for the report to show a YTD for each category (COLUMN). In other words

    New Hires - Month 20 YtD 30
    Updates - Month 40 YTD 100


    What I wsa wondering is if a YTD child table link to the Deparment tables would be best or calcualte it inot report services for each column?? And how would i do that??


    thank yoiu

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    one of the old axioms is that is some times a bad idea to store calculated values.

    Do numbers ever get restated or are they forever static?
    “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
    Quote Originally Posted by Thrasymachus
    one of the old axioms is that is some times a bad idea to store calculated values.

    Do numbers ever get restated or are they forever static?

    Restated, the numbers will change. There will also be text

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    The desire for the storing the data would also help in showing business increase or decrease. Would assist in whether a department is in need of another person..etc

    also comparisons. Also every department will be responsible for inputing thier own data. Also there will be a beginning date and ending date (date range)
    Last edited by desireemm; 11-06-08 at 15:02.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Are the YTD numbers calculated from other data you are already storing?

    If so and they are subject to change, I would not store them.

    If they are not derived from other data, I would have another table to store the summary info.
    “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.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Thrasymachus
    Are the YTD numbers calculated from other data you are already storing?

    If so and they are subject to change, I would not store them.

    If they are not derived from other data, I would have another table to store the summary info.

    Are you staying you would not store the YTD data?? am I understanding you correctly. The data will change in the new month, every month will hold different data
    they are going to enter data in once a month
    because I need to keep a runing total for the year on each column and then a grand total

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have a hard time figuring out why each dept. has its own table. Does the table structure differs that much between the depts?
    If not, I would make 1 result table (or view) that stores the data from those 11 tables. The result table would have 1 extra column, to store the dept_ID. The result table will be much easier to query and thus make reports.

    YTD is in essence WHERE YEAR(date_column) = YEAR(GetDate()) or GROUP BY YEAR(date_column). I would not store it separately, unless you notice severe performance problems. Not likely with only 1 row per month (12 per year times 11 depts = 132 records/year, even Access laughs at that).

    Can you give us some more information about the structure of the tables and the way data is put in them? Does the data in the tables grow each month or is it replaced with the data of the most recent month. I suppose data is added, not replaced.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Numerics are going to be stored into the tables and the data will change from month to month
    And each month will have different values. Each colum has to be calculated so that I can have a total of each colum (YTD) at the end of the year.

    This is the Administrative Table

    Code:
    CREATE TABLE [dbo].[AdministrativeHearing_Legal](
    	[TotalActiveSelfExclusions] [nvarchar](50) NULL,
    	[QrtlyAdditions] [nvarchar](50) NULL,
    	[SelfExclusionsHearings] [nvarchar](50) NULL,
    	[ExpiredSelfExclusions] [nvarchar](50) NULL,
    	[LicenseHearing] [nvarchar](50) NULL,
    	[PermitHearing] [nvarchar](50) NULL,
    	[Reinstated] [nvarchar](50) NULL,
    	[Revoked] [nvarchar](50) NULL,
    	[Requested] [nvarchar](50) NULL,
    	[Granted] [nvarchar](50) NULL,
    	[AdminLegalID] [int] IDENTITY(1,1) NOT NULL,
    	[Start_Date] [datetime] NULL,
    	[End_Date] [datetime] NULL,
     CONSTRAINT [PK_AdministrativeHearing_Legal] PRIMARY KEY CLUSTERED
    and the Audit Table


    Code:
    CREATE TABLE [dbo].[Compliance](
    	[GamingUntisInstalled] [nvarchar](50) NULL,
    	[GamingUnitsRemoved] [nvarchar](50) NULL,
    	[Conversion_Upgrades] [nvarchar](50) NULL,
    	[ComplianceID] [int] IDENTITY(1,1) NOT NULL,
    	[EPROMTested_Verified] [nvarchar](50) NULL,
    	[GameFlashTested_Verified] [nvarchar](50) NULL,
    	[OSTested_Verified] [nvarchar](50) NULL,
    	[CdsTested_Verified] [nvarchar](50) NULL,
    	[Taped_SealedLogicAreas] [nvarchar](50) NULL,
    	[RemovedTamperEvidentTape] [nvarchar](50) NULL,
    	[ApprovedRemovalRequestFrms] [nvarchar](50) NULL,
    	[WAP] [nvarchar](50) NULL,
    	[Start_Date] [datetime] NULL,
    	[End_Date] [datetime] NULL,
     CONSTRAINT [PK_Compliance] PRIMARY KEY CLUSTERED 
    (

    These are Just two of the tables each one is for each department
    Last edited by desireemm; 11-07-08 at 12:17.

Posting Permissions

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