Loading ...
Sorry, an error occurred while loading the content.

querying a history table to generate a report

Expand Messages
  • lansingbuddy
    So I have been working on generating a report and I came up with a few solutions but they all had some bugs. So I am not providing my solution here as they
    Message 1 of 9 , Dec 2, 2009
    • 0 Attachment
      So I have been working on generating a report and I came up with a few solutions but they all had some bugs. So I am not providing my solution here as they will only confuse you. Here is what I am trying to achieve. I have 2 tables. And one keeps history of the other. I want to create a report as I have below under DESIRED OUTPUT.

      First table:
      CREATE TABLE [CustPremiumServices](
      [CustID] [int] NULL,
      [SiteID] [int] NULL,
      [PremiumServiceStartDate] [datetime] NULL,
      [PremiumServiceEndDate] [datetime] NULL,
      [Components] [varchar](200) NULL
      )
      Explanation of columns:
      CustID – Customers ID.
      SiteID – A customer's site ID.
      PremiumServiceStartDate & PremiumServiceEndDate – The site will have access to premium components during the date ranges PremiumServiceStartDate & PremiumServiceEndDate.
      Components – a comma de-limited list of component ids.

      Second table:
      CREATE TABLE [PremiumComponentHistory](
      [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
      [CustID] [int] NOT NULL,
      [SiteID] [int] NOT NULL,
      [Change] [varchar](25) NOT NULL,
      [ModifiedDate] [datetime] NOT NULL,
      [ComponentInvolved] [int] NULL,
      [PremiumServiceStartDate] [datetime] NOT NULL,
      [PremiumServiceEndDate] [datetime] NOT NULL
      )
      Explanation of columns:
      Change – will store of these values [Component Added, Component Dropped, Service Date Changed]
      ModifiedDate – Date Record Added.
      ComponentInvolved – the id of the component that was either added or removed to the CustPremiumServices table.


      How history is kept:
      1. Everytime we change either PremiumServiceStartDate or PremiumServiceEndDate we insert a record in the PremiumComponentHistory table with the new dates and mark the change as "Service Date Changed".
      2. Everytime we update the Components column, we identify and insert record(s) for each component added and dropped and mark the change as "Component Added" or "Component Dropped"


      The goal is to find out a list of sites that had a specific component available during a date range.

      The website will offer an interface for filtering history with all components listed with checkboxes near each of the components and two date pickers. Depending on the criteria picked, the result should appear like this:

      CustID | SiteID | Component Involved | Was the component available to the website anytime during the date range? | Date component access was granted | Date component access was denied (if applicable)

      DESIRED OUTPUT:
      22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000


      If component access was granted/denied multiple times during the date range, print multiple and the number of times. like this:

      22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)


      Sample data(randomly taken):
      CustPremiumServices
      15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
      16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
      16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
      17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
      17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35

      PremiumComponentHistory
      18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03 00:00:00.000 2008-11-02 00:00:00.000
      19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03 00:00:00.000 2008-11-02 00:00:00.000
      19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03 00:00:00.000 2008-11-02 00:00:00.000
      14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03 00:00:00.000 2008-11-02 00:00:00.000
      17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03 00:00:00.000 2014-02-02 00:00:00.000
      17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03 00:00:00.000 2008-11-02 00:00:00.000

      Thanks for your help in advance,
      lansingbuddy
    • lansingbuddy
      So I have been working on generating a report and I came up with a few solutions but they all had some bugs. So I am not providing my solution here as they
      Message 2 of 9 , Dec 2, 2009
      • 0 Attachment
        So I have been working on generating a report and I came up with a few solutions but they all had some bugs. So I am not providing my solution here as they will only confuse you. Here is what I am trying to achieve. I have 2 tables. And one keeps history of the other. I want to create a report as I have below under DESIRED OUTPUT.

        First table:
        CREATE TABLE [CustPremiumServices](
        [CustID] [int] NULL,
        [SiteID] [int] NULL,
        [PremiumServiceStartDate] [datetime] NULL,
        [PremiumServiceEndDate] [datetime] NULL,
        [Components] [varchar](200) NULL
        )
        Explanation of columns:
        CustID – Customers ID.
        SiteID – A customer's site ID.
        PremiumServiceStartDate & PremiumServiceEndDate – The site will have access to premium components during the date ranges PremiumServiceStartDate & PremiumServiceEndDate.
        Components – a comma de-limited list of component ids.

        Second table:
        CREATE TABLE [PremiumComponentHistory](
        [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
        [CustID] [int] NOT NULL,
        [SiteID] [int] NOT NULL,
        [Change] [varchar](25) NOT NULL,
        [ModifiedDate] [datetime] NOT NULL,
        [ComponentInvolved] [int] NULL,
        [PremiumServiceStartDate] [datetime] NOT NULL,
        [PremiumServiceEndDate] [datetime] NOT NULL
        )
        Explanation of columns:
        Change – will store of these values [Component Added, Component Dropped, Service Date Changed]
        ModifiedDate – Date Record Added.
        ComponentInvolved – the id of the component that was either added or removed to the CustPremiumServices table.


        How history is kept:
        1. Everytime we change either PremiumServiceStartDate or PremiumServiceEndDate we insert a record in the PremiumComponentHistory table with the new dates and mark the change as "Service Date Changed".
        2. Everytime we update the Components column, we identify and insert record(s) for each component added and dropped and mark the change as "Component Added" or "Component Dropped"


        The goal is to find out a list of sites that had a specific component available during a date range.

        The website will offer an interface for filtering history with all components listed with checkboxes near each of the components and two date pickers. Depending on the criteria picked, the result should appear like this:

        CustID | SiteID | Component Involved | Was the component available to the website anytime during the date range? | Date component access was granted | Date component access was denied (if applicable)

        DESIRED OUTPUT:
        22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000


        If component access was granted/denied multiple times during the date range, print multiple and the number of times. like this:

        22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)


        Sample data(randomly taken):
        CustPremiumServices
        15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
        16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
        16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
        17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
        17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35

        PremiumComponentHistory
        18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03 00:00:00.000 2008-11-02 00:00:00.000
        19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03 00:00:00.000 2008-11-02 00:00:00.000
        19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03 00:00:00.000 2008-11-02 00:00:00.000
        14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03 00:00:00.000 2008-11-02 00:00:00.000
        17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03 00:00:00.000 2014-02-02 00:00:00.000
        17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03 00:00:00.000 2008-11-02 00:00:00.000

        Thanks for your help in advance,
        lansingbuddy
      • Arnie Rowland
        Is your design still in a state that allows corrections . If so, a couple of design error corrections would make this far easier. 1. Add an IDENTITY column to
        Message 3 of 9 , Dec 2, 2009
        • 0 Attachment
          Is your design still in a state that allows 'corrections'.

          If so, a couple of design error corrections would make this far easier.

          1. Add an IDENTITY column to [CustPremiumServices]
          ([CustPremiumServicesID] int)
          2. Create a Primary key (or UNIQUE Index) for [CustPremiumServices]
          3. Remove the [Components] column from [CustPremiumServices]
          3. Use a third table [CustPremServComponents]
          [CustPremiumServicesID] [int] NOT NULL
          [ComponentInvolved] [int] NOT NULL

          Then one row for each component for each customer receiving premium
          services.

          The Typical model is [Customer] --> [CustomerComponent] <-- [Component]

          Without this alteration, you cannot simply JOIN on the [Components]
          pseudo array. You will constantly have to parse the field.

          Regards,

          Arnie Rowland, MVP (SQL Server)

          "You cannot do a kindness too soon because you never know how soon it
          will be too late."
          -Ralph Waldo Emerson




          -----Original Message-----
          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of lansingbuddy
          Sent: Wednesday, December 02, 2009 5:51 PM
          To: Arnie
          Subject: [SQLQueriesNoCode] querying a history table to generate a
          report

          So I have been working on generating a report and I came up with a few
          solutions but they all had some bugs. So I am not providing my solution
          here as they will only confuse you. Here is what I am trying to achieve.
          I have 2 tables. And one keeps history of the other. I want to create a
          report as I have below under DESIRED OUTPUT.

          First table:
          CREATE TABLE [CustPremiumServices](
          [CustID] [int] NULL,
          [SiteID] [int] NULL,
          [PremiumServiceStartDate] [datetime] NULL,
          [PremiumServiceEndDate] [datetime] NULL,
          [Components] [varchar](200) NULL
          )
          Explanation of columns:
          CustID - Customers ID.
          SiteID - A customer's site ID.
          PremiumServiceStartDate & PremiumServiceEndDate - The site will have
          access to premium components during the date ranges
          PremiumServiceStartDate & PremiumServiceEndDate.
          Components - a comma de-limited list of component ids.

          Second table:
          CREATE TABLE [PremiumComponentHistory](
          [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT
          (newid()),
          [CustID] [int] NOT NULL,
          [SiteID] [int] NOT NULL,
          [Change] [varchar](25) NOT NULL,
          [ModifiedDate] [datetime] NOT NULL,
          [ComponentInvolved] [int] NULL,
          [PremiumServiceStartDate] [datetime] NOT NULL,
          [PremiumServiceEndDate] [datetime] NOT NULL
          )
          Explanation of columns:
          Change - will store of these values [Component Added, Component
          Dropped, Service Date Changed]
          ModifiedDate - Date Record Added.
          ComponentInvolved - the id of the component that was either added or
          removed to the CustPremiumServices table.


          How history is kept:
          1. Everytime we change either PremiumServiceStartDate or
          PremiumServiceEndDate we insert a record in the PremiumComponentHistory
          table with the new dates and mark the change as "Service Date Changed".
          2. Everytime we update the Components column, we identify and
          insert record(s) for each component added and dropped and mark the
          change as "Component Added" or "Component Dropped"


          The goal is to find out a list of sites that had a specific component
          available during a date range.

          The website will offer an interface for filtering history with all
          components listed with checkboxes near each of the components and two
          date pickers. Depending on the criteria picked, the result should appear
          like this:

          CustID | SiteID | Component Involved | Was the component available to
          the website anytime during the date range? | Date component access was
          granted | Date component access was denied (if applicable)

          DESIRED OUTPUT:
          22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000


          If component access was granted/denied multiple times during the date
          range, print multiple and the number of times. like this:

          22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)


          Sample data(randomly taken):
          CustPremiumServices
          15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
          16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
          16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
          17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
          17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35

          PremiumComponentHistory
          18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL
          2008-06-03 00:00:00.000 2008-11-02 00:00:00.000
          19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL
          2008-06-03 00:00:00.000 2008-11-02 00:00:00.000
          19 1 Component Added 2009-12-02 17:18:06.883 35
          2013-06-03 00:00:00.000 2008-11-02 00:00:00.000
          14 2 Component Dropped 2009-12-02 17:18:06.880 12
          2013-06-03 00:00:00.000 2008-11-02 00:00:00.000
          17 3 Component Added 2009-12-02 11:18:56.790 35
          2008-06-03 00:00:00.000 2014-02-02 00:00:00.000
          17 4 Component Dropped 2009-12-02 17:18:02.660 12
          2013-06-03 00:00:00.000 2008-11-02 00:00:00.000

          Thanks for your help in advance,
          lansingbuddy





          ------------------------------------

          Yahoo! Groups Links







          Disclaimer - December 2, 2009
          This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
          This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
        • Ansi
          Thanks but unfortunately that is not an option for me as it is a huge application that utilizes this table. Any suggestion on working the query to get the
          Message 4 of 9 , Dec 3, 2009
          • 0 Attachment
            Thanks but unfortunately that is not an option for me as it is a huge
            application that utilizes this table. Any suggestion on working the query to
            get the desired results?

            Thanks again

            On Wed, Dec 2, 2009 at 7:07 PM, lansingbuddy <ansiguy@...> wrote:

            >
            >
            > So I have been working on generating a report and I came up with a few
            > solutions but they all had some bugs. So I am not providing my solution here
            > as they will only confuse you. Here is what I am trying to achieve. I have 2
            > tables. And one keeps history of the other. I want to create a report as I
            > have below under DESIRED OUTPUT.
            >
            > First table:
            > CREATE TABLE [CustPremiumServices](
            > [CustID] [int] NULL,
            > [SiteID] [int] NULL,
            > [PremiumServiceStartDate] [datetime] NULL,
            > [PremiumServiceEndDate] [datetime] NULL,
            > [Components] [varchar](200) NULL
            > )
            > Explanation of columns:
            > CustID � Customers ID.
            > SiteID � A customer's site ID.
            > PremiumServiceStartDate & PremiumServiceEndDate � The site will have access
            > to premium components during the date ranges PremiumServiceStartDate &
            > PremiumServiceEndDate.
            > Components � a comma de-limited list of component ids.
            >
            > Second table:
            > CREATE TABLE [PremiumComponentHistory](
            > [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
            > [CustID] [int] NOT NULL,
            > [SiteID] [int] NOT NULL,
            > [Change] [varchar](25) NOT NULL,
            > [ModifiedDate] [datetime] NOT NULL,
            > [ComponentInvolved] [int] NULL,
            > [PremiumServiceStartDate] [datetime] NOT NULL,
            > [PremiumServiceEndDate] [datetime] NOT NULL
            > )
            > Explanation of columns:
            > Change � will store of these values [Component Added, Component Dropped,
            > Service Date Changed]
            > ModifiedDate � Date Record Added.
            > ComponentInvolved � the id of the component that was either added or
            > removed to the CustPremiumServices table.
            >
            > How history is kept:
            > 1. Everytime we change either PremiumServiceStartDate or
            > PremiumServiceEndDate we insert a record in the PremiumComponentHistory
            > table with the new dates and mark the change as "Service Date Changed".
            > 2. Everytime we update the Components column, we identify and insert
            > record(s) for each component added and dropped and mark the change as
            > "Component Added" or "Component Dropped"
            >
            > The goal is to find out a list of sites that had a specific component
            > available during a date range.
            >
            > The website will offer an interface for filtering history with all
            > components listed with checkboxes near each of the components and two date
            > pickers. Depending on the criteria picked, the result should appear like
            > this:
            >
            > CustID | SiteID | Component Involved | Was the component available to the
            > website anytime during the date range? | Date component access was granted |
            > Date component access was denied (if applicable)
            >
            > DESIRED OUTPUT:
            > 22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000
            >
            > If component access was granted/denied multiple times during the date
            > range, print multiple and the number of times. like this:
            >
            > 22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)
            >
            > Sample data(randomly taken):
            > CustPremiumServices
            > 15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
            > 16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
            > 16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
            > 17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
            > 17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35
            >
            > PremiumComponentHistory
            > 18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03
            > 00:00:00.000 2008-11-02 00:00:00.000
            > 19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03
            > 00:00:00.000 2008-11-02 00:00:00.000
            > 19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03 00:00:00.000
            > 2008-11-02 00:00:00.000
            > 14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03 00:00:00.000
            > 2008-11-02 00:00:00.000
            > 17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03 00:00:00.000
            > 2014-02-02 00:00:00.000
            > 17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03 00:00:00.000
            > 2008-11-02 00:00:00.000
            >
            > Thanks for your help in advance,
            > lansingbuddy
            >
            >
            >


            [Non-text portions of this message have been removed]
          • John Warner
            Too bad because whoever designed this database should not be employed as a developer. The tables are not normalized. How large (rows) approx are the tables?
            Message 5 of 9 , Dec 3, 2009
            • 0 Attachment
              Too bad because whoever designed this database should not be employed as a
              developer. The tables are not normalized. How large (rows) approx are the
              tables?

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ansi
              > Sent: Thursday, December 03, 2009 9:59 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: Re: [SQLQueriesNoCode] querying a history table to generate a
              > report
              >
              > Thanks but unfortunately that is not an option for me as it is a huge
              > application that utilizes this table. Any suggestion on working the
              query to
              > get the desired results?
              >
              > Thanks again
              >
              > On Wed, Dec 2, 2009 at 7:07 PM, lansingbuddy <ansiguy@...> wrote:
              >
              > >
              > >
              > > So I have been working on generating a report and I came up with a few
              > > solutions but they all had some bugs. So I am not providing my
              solution here
              > > as they will only confuse you. Here is what I am trying to achieve. I
              have 2
              > > tables. And one keeps history of the other. I want to create a report
              as I
              > > have below under DESIRED OUTPUT.
              > >
              > > First table:
              > > CREATE TABLE [CustPremiumServices](
              > > [CustID] [int] NULL,
              > > [SiteID] [int] NULL,
              > > [PremiumServiceStartDate] [datetime] NULL,
              > > [PremiumServiceEndDate] [datetime] NULL,
              > > [Components] [varchar](200) NULL
              > > )
              > > Explanation of columns:
              > > CustID - Customers ID.
              > > SiteID - A customer's site ID.
              > > PremiumServiceStartDate & PremiumServiceEndDate - The site will have
              > access
              > > to premium components during the date ranges PremiumServiceStartDate &
              > > PremiumServiceEndDate.
              > > Components - a comma de-limited list of component ids.
              > >
              > > Second table:
              > > CREATE TABLE [PremiumComponentHistory](
              > > [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
              > > [CustID] [int] NOT NULL,
              > > [SiteID] [int] NOT NULL,
              > > [Change] [varchar](25) NOT NULL,
              > > [ModifiedDate] [datetime] NOT NULL,
              > > [ComponentInvolved] [int] NULL,
              > > [PremiumServiceStartDate] [datetime] NOT NULL,
              > > [PremiumServiceEndDate] [datetime] NOT NULL
              > > )
              > > Explanation of columns:
              > > Change - will store of these values [Component Added, Component
              > Dropped,
              > > Service Date Changed]
              > > ModifiedDate - Date Record Added.
              > > ComponentInvolved - the id of the component that was either added or
              > > removed to the CustPremiumServices table.
              > >
              > > How history is kept:
              > > 1. Everytime we change either PremiumServiceStartDate or
              > > PremiumServiceEndDate we insert a record in the
              > PremiumComponentHistory
              > > table with the new dates and mark the change as "Service Date
              Changed".
              > > 2. Everytime we update the Components column, we identify and insert
              > > record(s) for each component added and dropped and mark the change as
              > > "Component Added" or "Component Dropped"
              > >
              > > The goal is to find out a list of sites that had a specific component
              > > available during a date range.
              > >
              > > The website will offer an interface for filtering history with all
              > > components listed with checkboxes near each of the components and two
              > date
              > > pickers. Depending on the criteria picked, the result should appear
              like
              > > this:
              > >
              > > CustID | SiteID | Component Involved | Was the component available to
              the
              > > website anytime during the date range? | Date component access was
              > granted |
              > > Date component access was denied (if applicable)
              > >
              > > DESIRED OUTPUT:
              > > 22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000
              > >
              > > If component access was granted/denied multiple times during the date
              > > range, print multiple and the number of times. like this:
              > >
              > > 22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)
              > >
              > > Sample data(randomly taken):
              > > CustPremiumServices
              > > 15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
              > > 16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
              > > 16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
              > > 17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
              > > 17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35
              > >
              > > PremiumComponentHistory
              > > 18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03
              > > 00:00:00.000 2008-11-02 00:00:00.000
              > > 19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03
              > > 00:00:00.000 2008-11-02 00:00:00.000
              > > 19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03
              > 00:00:00.000
              > > 2008-11-02 00:00:00.000
              > > 14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03
              > 00:00:00.000
              > > 2008-11-02 00:00:00.000
              > > 17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03
              > 00:00:00.000
              > > 2014-02-02 00:00:00.000
              > > 17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03
              > 00:00:00.000
              > > 2008-11-02 00:00:00.000
              > >
              > > Thanks for your help in advance,
              > > lansingbuddy
              > >
              > >
              > >
              >
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >
            • Arnie Rowland
              Ansi, I forgot to commend you for including the table schemas and some sample data. That will make it easier for someone to tackle your problem and help you
              Message 6 of 9 , Dec 3, 2009
              • 0 Attachment
                Ansi,

                I forgot to commend you for including the table schemas and some sample
                data. That will make it easier for someone to tackle your problem and
                help you find a working solution.

                Be forewarned that the performance will suffer because it will be
                necessary to virtualize the process of parsing that component data to
                simulate the missing table. I can easily tell you that reports are
                likely to be on orders of magnitude SLOWER as a result.

                You may wish to explore creating a 'split' function, similar to the one
                posted here.
                http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringAr
                rayInput
                The Split function will prove to be quite helpful.

                <Soapbox>It is really too bad that organizations don't recognize that
                when they are creating databases for new projects, the cost of bringing
                in someone that really understand relational data and databases is small
                compared to the long term cost of having to support such sub-optimal and
                crappy designs. It is incredible how often I find extremely crappy
                designs like this one. Folks that keep perpetuating these aberrations
                should be driven out of the software business. </ Soapbox>

                My suggestion:

                1. Pull the required component data (by date range) into a #Temp table
                2. Create another #Temp table for the Split out Components
                3. JOIN that second #Temp table with the Services and History tables.

                Regards,

                Arnie Rowland, MVP (SQL Server)

                "You cannot do a kindness too soon because you never know how soon it
                will be too late."
                -Ralph Waldo Emerson




                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com
                [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ansi
                Sent: Thursday, December 03, 2009 6:59 AM
                To: Arnie
                Subject: Re: [SQLQueriesNoCode] querying a history table to generate a
                report

                Thanks but unfortunately that is not an option for me as it is a huge
                application that utilizes this table. Any suggestion on working the
                query to
                get the desired results?

                Thanks again

                On Wed, Dec 2, 2009 at 7:07 PM, lansingbuddy <ansiguy@...> wrote:

                >
                >
                > So I have been working on generating a report and I came up with a few
                > solutions but they all had some bugs. So I am not providing my
                solution here
                > as they will only confuse you. Here is what I am trying to achieve. I
                have 2
                > tables. And one keeps history of the other. I want to create a report
                as I
                > have below under DESIRED OUTPUT.
                >
                > First table:
                > CREATE TABLE [CustPremiumServices](
                > [CustID] [int] NULL,
                > [SiteID] [int] NULL,
                > [PremiumServiceStartDate] [datetime] NULL,
                > [PremiumServiceEndDate] [datetime] NULL,
                > [Components] [varchar](200) NULL
                > )
                > Explanation of columns:
                > CustID - Customers ID.
                > SiteID - A customer's site ID.
                > PremiumServiceStartDate & PremiumServiceEndDate - The site will have
                access
                > to premium components during the date ranges PremiumServiceStartDate &
                > PremiumServiceEndDate.
                > Components - a comma de-limited list of component ids.
                >
                > Second table:
                > CREATE TABLE [PremiumComponentHistory](
                > [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
                > [CustID] [int] NOT NULL,
                > [SiteID] [int] NOT NULL,
                > [Change] [varchar](25) NOT NULL,
                > [ModifiedDate] [datetime] NOT NULL,
                > [ComponentInvolved] [int] NULL,
                > [PremiumServiceStartDate] [datetime] NOT NULL,
                > [PremiumServiceEndDate] [datetime] NOT NULL
                > )
                > Explanation of columns:
                > Change - will store of these values [Component Added, Component
                Dropped,
                > Service Date Changed]
                > ModifiedDate - Date Record Added.
                > ComponentInvolved - the id of the component that was either added or
                > removed to the CustPremiumServices table.
                >
                > How history is kept:
                > 1. Everytime we change either PremiumServiceStartDate or
                > PremiumServiceEndDate we insert a record in the
                PremiumComponentHistory
                > table with the new dates and mark the change as "Service Date
                Changed".
                > 2. Everytime we update the Components column, we identify and insert
                > record(s) for each component added and dropped and mark the change as
                > "Component Added" or "Component Dropped"
                >
                > The goal is to find out a list of sites that had a specific component
                > available during a date range.
                >
                > The website will offer an interface for filtering history with all
                > components listed with checkboxes near each of the components and two
                date
                > pickers. Depending on the criteria picked, the result should appear
                like
                > this:
                >
                > CustID | SiteID | Component Involved | Was the component available to
                the
                > website anytime during the date range? | Date component access was
                granted |
                > Date component access was denied (if applicable)
                >
                > DESIRED OUTPUT:
                > 22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000
                >
                > If component access was granted/denied multiple times during the date
                > range, print multiple and the number of times. like this:
                >
                > 22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)
                >
                > Sample data(randomly taken):
                > CustPremiumServices
                > 15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
                > 16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
                > 16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
                > 17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
                > 17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35
                >
                > PremiumComponentHistory
                > 18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03
                > 00:00:00.000 2008-11-02 00:00:00.000
                > 19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03
                > 00:00:00.000 2008-11-02 00:00:00.000
                > 19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03
                00:00:00.000
                > 2008-11-02 00:00:00.000
                > 14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03
                00:00:00.000
                > 2008-11-02 00:00:00.000
                > 17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03
                00:00:00.000
                > 2014-02-02 00:00:00.000
                > 17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03
                00:00:00.000
                > 2008-11-02 00:00:00.000
                >
                > Thanks for your help in advance,
                > lansingbuddy
                >
                >
                >


                [Non-text portions of this message have been removed]



                ------------------------------------

                Yahoo! Groups Links







                Disclaimer - December 3, 2009
                This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
              • John Warner
                Arnie will this even work given there is no primary key on either table? (not your temps, but the existing) I m thinking a query / sproc like this just goes
                Message 7 of 9 , Dec 3, 2009
                • 0 Attachment
                  Arnie will this even work given there is no primary key on either table?
                  (not your temps, but the existing) I'm thinking a query / sproc like this
                  just goes away and times out eventually due to a lack of 'good' indexes.
                  What I was considering to suggest was depending on size create the
                  'required' tables at night when demand is low and be one day behind with
                  reports, but at least now they can be done. That is this afternoon's
                  report will not have this morning's data.

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                  > Sent: Thursday, December 03, 2009 10:41 AM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] querying a history table to generate a
                  > report
                  >
                  > Ansi,
                  >
                  > I forgot to commend you for including the table schemas and some sample
                  > data. That will make it easier for someone to tackle your problem and
                  > help you find a working solution.
                  >
                  > Be forewarned that the performance will suffer because it will be
                  > necessary to virtualize the process of parsing that component data to
                  > simulate the missing table. I can easily tell you that reports are
                  > likely to be on orders of magnitude SLOWER as a result.
                  >
                  > You may wish to explore creating a 'split' function, similar to the one
                  > posted here.
                  > http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringAr
                  > rayInput
                  > The Split function will prove to be quite helpful.
                  >
                  > <Soapbox>It is really too bad that organizations don't recognize that
                  > when they are creating databases for new projects, the cost of bringing
                  > in someone that really understand relational data and databases is small
                  > compared to the long term cost of having to support such sub-optimal and
                  > crappy designs. It is incredible how often I find extremely crappy
                  > designs like this one. Folks that keep perpetuating these aberrations
                  > should be driven out of the software business. </ Soapbox>
                  >
                  > My suggestion:
                  >
                  > 1. Pull the required component data (by date range) into a #Temp table
                  > 2. Create another #Temp table for the Split out Components
                  > 3. JOIN that second #Temp table with the Services and History tables.
                  >
                  > Regards,
                  >
                  > Arnie Rowland, MVP (SQL Server)
                  >
                  > "You cannot do a kindness too soon because you never know how soon it
                  > will be too late."
                  > -Ralph Waldo Emerson
                  >
                  >
                  >
                  >
                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ansi
                  > Sent: Thursday, December 03, 2009 6:59 AM
                  > To: Arnie
                  > Subject: Re: [SQLQueriesNoCode] querying a history table to generate a
                  > report
                  >
                  > Thanks but unfortunately that is not an option for me as it is a huge
                  > application that utilizes this table. Any suggestion on working the
                  > query to
                  > get the desired results?
                  >
                  > Thanks again
                  >
                  > On Wed, Dec 2, 2009 at 7:07 PM, lansingbuddy <ansiguy@...> wrote:
                  >
                  > >
                  > >
                  > > So I have been working on generating a report and I came up with a few
                  > > solutions but they all had some bugs. So I am not providing my
                  > solution here
                  > > as they will only confuse you. Here is what I am trying to achieve. I
                  > have 2
                  > > tables. And one keeps history of the other. I want to create a report
                  > as I
                  > > have below under DESIRED OUTPUT.
                  > >
                  > > First table:
                  > > CREATE TABLE [CustPremiumServices](
                  > > [CustID] [int] NULL,
                  > > [SiteID] [int] NULL,
                  > > [PremiumServiceStartDate] [datetime] NULL,
                  > > [PremiumServiceEndDate] [datetime] NULL,
                  > > [Components] [varchar](200) NULL
                  > > )
                  > > Explanation of columns:
                  > > CustID - Customers ID.
                  > > SiteID - A customer's site ID.
                  > > PremiumServiceStartDate & PremiumServiceEndDate - The site will have
                  > access
                  > > to premium components during the date ranges PremiumServiceStartDate &
                  > > PremiumServiceEndDate.
                  > > Components - a comma de-limited list of component ids.
                  > >
                  > > Second table:
                  > > CREATE TABLE [PremiumComponentHistory](
                  > > [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
                  > > [CustID] [int] NOT NULL,
                  > > [SiteID] [int] NOT NULL,
                  > > [Change] [varchar](25) NOT NULL,
                  > > [ModifiedDate] [datetime] NOT NULL,
                  > > [ComponentInvolved] [int] NULL,
                  > > [PremiumServiceStartDate] [datetime] NOT NULL,
                  > > [PremiumServiceEndDate] [datetime] NOT NULL
                  > > )
                  > > Explanation of columns:
                  > > Change - will store of these values [Component Added, Component
                  > Dropped,
                  > > Service Date Changed]
                  > > ModifiedDate - Date Record Added.
                  > > ComponentInvolved - the id of the component that was either added or
                  > > removed to the CustPremiumServices table.
                  > >
                  > > How history is kept:
                  > > 1. Everytime we change either PremiumServiceStartDate or
                  > > PremiumServiceEndDate we insert a record in the
                  > PremiumComponentHistory
                  > > table with the new dates and mark the change as "Service Date
                  > Changed".
                  > > 2. Everytime we update the Components column, we identify and insert
                  > > record(s) for each component added and dropped and mark the change as
                  > > "Component Added" or "Component Dropped"
                  > >
                  > > The goal is to find out a list of sites that had a specific component
                  > > available during a date range.
                  > >
                  > > The website will offer an interface for filtering history with all
                  > > components listed with checkboxes near each of the components and two
                  > date
                  > > pickers. Depending on the criteria picked, the result should appear
                  > like
                  > > this:
                  > >
                  > > CustID | SiteID | Component Involved | Was the component available to
                  > the
                  > > website anytime during the date range? | Date component access was
                  > granted |
                  > > Date component access was denied (if applicable)
                  > >
                  > > DESIRED OUTPUT:
                  > > 22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000
                  > >
                  > > If component access was granted/denied multiple times during the date
                  > > range, print multiple and the number of times. like this:
                  > >
                  > > 22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)
                  > >
                  > > Sample data(randomly taken):
                  > > CustPremiumServices
                  > > 15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
                  > > 16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
                  > > 16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
                  > > 17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
                  > > 17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35
                  > >
                  > > PremiumComponentHistory
                  > > 18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03
                  > > 00:00:00.000 2008-11-02 00:00:00.000
                  > > 19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03
                  > > 00:00:00.000 2008-11-02 00:00:00.000
                  > > 19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03
                  > 00:00:00.000
                  > > 2008-11-02 00:00:00.000
                  > > 14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03
                  > 00:00:00.000
                  > > 2008-11-02 00:00:00.000
                  > > 17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03
                  > 00:00:00.000
                  > > 2014-02-02 00:00:00.000
                  > > 17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03
                  > 00:00:00.000
                  > > 2008-11-02 00:00:00.000
                  > >
                  > > Thanks for your help in advance,
                  > > lansingbuddy
                  > >
                  > >
                  > >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                  >
                  > Disclaimer - December 3, 2009
                  > This email and any files transmitted with it are confidential and
                  intended solely
                  > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                  > addressee you should not disseminate, distribute, copy or alter this
                  email. Any
                  > views or opinions presented in this email are solely those of the author
                  and
                  > might not represent those of Westwood Consulting, Inc. Warning: Although
                  > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                  > viruses are present in this email, the company cannot accept
                  responsibility for
                  > any loss or damage arising from the use of this email or attachments.
                  > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                • Arnie Rowland
                  AS a server based process, it can work -albeit most likely taking a very long time, using a lot of resources, perhaps creating a lot of blocking behavior.
                  Message 8 of 9 , Dec 3, 2009
                  • 0 Attachment
                    AS a server based process, it can work -albeit most likely taking a very
                    long time, using a lot of resources, perhaps creating a lot of blocking
                    behavior. (Depending upon how it is done.)

                    I suspect that Ansi will reply that he cannot do this overnight, and
                    that he cannot create temporary data structures...

                    I mean, really, not have a Primary Key on the crucial Services table is
                    a joke -it must be impossible to execute and update or delete commands
                    against that data. And compound that with the GUID as the probable PK on
                    the History table.

                    There is just no way to get reporting out of this super bad design
                    without wasting lots and lots of time waiting, and waiting, and
                    waiting... Oh, and did I mention that the design was not very good...

                    Regards,

                    Arnie Rowland, MVP (SQL Server)

                    "You cannot do a kindness too soon because you never know how soon it
                    will be too late."
                    -Ralph Waldo Emerson




                    -----Original Message-----
                    From: SQLQueriesNoCode@yahoogroups.com
                    [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    Sent: Thursday, December 03, 2009 8:10 AM
                    To: Arnie
                    Subject: RE: [SQLQueriesNoCode] querying a history table to generate a
                    report

                    Arnie will this even work given there is no primary key on either table?
                    (not your temps, but the existing) I'm thinking a query / sproc like
                    this
                    just goes away and times out eventually due to a lack of 'good' indexes.
                    What I was considering to suggest was depending on size create the
                    'required' tables at night when demand is low and be one day behind with
                    reports, but at least now they can be done. That is this afternoon's
                    report will not have this morning's data.

                    John Warner


                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                    > Sent: Thursday, December 03, 2009 10:41 AM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: RE: [SQLQueriesNoCode] querying a history table to generate a
                    > report
                    >
                    > Ansi,
                    >
                    > I forgot to commend you for including the table schemas and some
                    sample
                    > data. That will make it easier for someone to tackle your problem and
                    > help you find a working solution.
                    >
                    > Be forewarned that the performance will suffer because it will be
                    > necessary to virtualize the process of parsing that component data to
                    > simulate the missing table. I can easily tell you that reports are
                    > likely to be on orders of magnitude SLOWER as a result.
                    >
                    > You may wish to explore creating a 'split' function, similar to the
                    one
                    > posted here.
                    >
                    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringAr
                    > rayInput
                    > The Split function will prove to be quite helpful.
                    >
                    > <Soapbox>It is really too bad that organizations don't recognize that
                    > when they are creating databases for new projects, the cost of
                    bringing
                    > in someone that really understand relational data and databases is
                    small
                    > compared to the long term cost of having to support such sub-optimal
                    and
                    > crappy designs. It is incredible how often I find extremely crappy
                    > designs like this one. Folks that keep perpetuating these aberrations
                    > should be driven out of the software business. </ Soapbox>
                    >
                    > My suggestion:
                    >
                    > 1. Pull the required component data (by date range) into a #Temp table
                    > 2. Create another #Temp table for the Split out Components
                    > 3. JOIN that second #Temp table with the Services and History tables.
                    >
                    > Regards,
                    >
                    > Arnie Rowland, MVP (SQL Server)
                    >
                    > "You cannot do a kindness too soon because you never know how soon it
                    > will be too late."
                    > -Ralph Waldo Emerson
                    >
                    >
                    >
                    >
                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ansi
                    > Sent: Thursday, December 03, 2009 6:59 AM
                    > To: Arnie
                    > Subject: Re: [SQLQueriesNoCode] querying a history table to generate a
                    > report
                    >
                    > Thanks but unfortunately that is not an option for me as it is a huge
                    > application that utilizes this table. Any suggestion on working the
                    > query to
                    > get the desired results?
                    >
                    > Thanks again
                    >
                    > On Wed, Dec 2, 2009 at 7:07 PM, lansingbuddy <ansiguy@...>
                    wrote:
                    >
                    > >
                    > >
                    > > So I have been working on generating a report and I came up with a
                    few
                    > > solutions but they all had some bugs. So I am not providing my
                    > solution here
                    > > as they will only confuse you. Here is what I am trying to achieve.
                    I
                    > have 2
                    > > tables. And one keeps history of the other. I want to create a
                    report
                    > as I
                    > > have below under DESIRED OUTPUT.
                    > >
                    > > First table:
                    > > CREATE TABLE [CustPremiumServices](
                    > > [CustID] [int] NULL,
                    > > [SiteID] [int] NULL,
                    > > [PremiumServiceStartDate] [datetime] NULL,
                    > > [PremiumServiceEndDate] [datetime] NULL,
                    > > [Components] [varchar](200) NULL
                    > > )
                    > > Explanation of columns:
                    > > CustID - Customers ID.
                    > > SiteID - A customer's site ID.
                    > > PremiumServiceStartDate & PremiumServiceEndDate - The site will have
                    > access
                    > > to premium components during the date ranges PremiumServiceStartDate
                    &
                    > > PremiumServiceEndDate.
                    > > Components - a comma de-limited list of component ids.
                    > >
                    > > Second table:
                    > > CREATE TABLE [PremiumComponentHistory](
                    > > [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
                    > > [CustID] [int] NOT NULL,
                    > > [SiteID] [int] NOT NULL,
                    > > [Change] [varchar](25) NOT NULL,
                    > > [ModifiedDate] [datetime] NOT NULL,
                    > > [ComponentInvolved] [int] NULL,
                    > > [PremiumServiceStartDate] [datetime] NOT NULL,
                    > > [PremiumServiceEndDate] [datetime] NOT NULL
                    > > )
                    > > Explanation of columns:
                    > > Change - will store of these values [Component Added, Component
                    > Dropped,
                    > > Service Date Changed]
                    > > ModifiedDate - Date Record Added.
                    > > ComponentInvolved - the id of the component that was either added or
                    > > removed to the CustPremiumServices table.
                    > >
                    > > How history is kept:
                    > > 1. Everytime we change either PremiumServiceStartDate or
                    > > PremiumServiceEndDate we insert a record in the
                    > PremiumComponentHistory
                    > > table with the new dates and mark the change as "Service Date
                    > Changed".
                    > > 2. Everytime we update the Components column, we identify and insert
                    > > record(s) for each component added and dropped and mark the change
                    as
                    > > "Component Added" or "Component Dropped"
                    > >
                    > > The goal is to find out a list of sites that had a specific
                    component
                    > > available during a date range.
                    > >
                    > > The website will offer an interface for filtering history with all
                    > > components listed with checkboxes near each of the components and
                    two
                    > date
                    > > pickers. Depending on the criteria picked, the result should appear
                    > like
                    > > this:
                    > >
                    > > CustID | SiteID | Component Involved | Was the component available
                    to
                    > the
                    > > website anytime during the date range? | Date component access was
                    > granted |
                    > > Date component access was denied (if applicable)
                    > >
                    > > DESIRED OUTPUT:
                    > > 22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01
                    05:00:00.000
                    > >
                    > > If component access was granted/denied multiple times during the
                    date
                    > > range, print multiple and the number of times. like this:
                    > >
                    > > 22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)
                    > >
                    > > Sample data(randomly taken):
                    > > CustPremiumServices
                    > > 15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
                    > > 16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
                    > > 16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
                    > > 17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
                    > > 17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35
                    > >
                    > > PremiumComponentHistory
                    > > 18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03
                    > > 00:00:00.000 2008-11-02 00:00:00.000
                    > > 19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03
                    > > 00:00:00.000 2008-11-02 00:00:00.000
                    > > 19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03
                    > 00:00:00.000
                    > > 2008-11-02 00:00:00.000
                    > > 14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03
                    > 00:00:00.000
                    > > 2008-11-02 00:00:00.000
                    > > 17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03
                    > 00:00:00.000
                    > > 2014-02-02 00:00:00.000
                    > > 17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03
                    > 00:00:00.000
                    > > 2008-11-02 00:00:00.000
                    > >
                    > > Thanks for your help in advance,
                    > > lansingbuddy
                    > >
                    > >
                    > >
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    > Disclaimer - December 3, 2009
                    > This email and any files transmitted with it are confidential and
                    intended solely
                    > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                    > addressee you should not disseminate, distribute, copy or alter this
                    email. Any
                    > views or opinions presented in this email are solely those of the
                    author
                    and
                    > might not represent those of Westwood Consulting, Inc. Warning:
                    Although
                    > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                    > viruses are present in this email, the company cannot accept
                    responsibility for
                    > any loss or damage arising from the use of this email or attachments.
                    > This disclaimer was added by Policy Patrol:
                    http://www.policypatrol.com/
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >



                    ------------------------------------

                    Yahoo! Groups Links
                  • Ansi
                    Folks, These tables have primary keys and indexes. and bunch of other columns and bunch of other tables that are referenced. I took out all that to keep
                    Message 9 of 9 , Dec 3, 2009
                    • 0 Attachment
                      Folks, These tables have primary keys and indexes. and bunch of other
                      columns and bunch of other tables that are referenced. I took out all that
                      to keep explain the problem CLEAN to the point and to what is just needed.

                      Please assume that efficiency is not an issue here at this point because the
                      data is not large.

                      I consider myself quite above average in SQL and wanted to see if someone
                      thinks this is a complex problem or if there is a way this can be solved and
                      point me in the right direction to solve the problem.

                      Thanks for your insights and hope to hear more.

                      On Thu, Dec 3, 2009 at 9:03 AM, John Warner <john@...> wrote:

                      >
                      >
                      > Too bad because whoever designed this database should not be employed as a
                      > developer. The tables are not normalized. How large (rows) approx are the
                      > tables?
                      >
                      > John Warner
                      >
                      >
                      > > -----Original Message-----
                      > > From: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                      > > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>]
                      > On Behalf Of Ansi
                      > > Sent: Thursday, December 03, 2009 9:59 AM
                      > > To: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                      > > Subject: Re: [SQLQueriesNoCode] querying a history table to generate a
                      > > report
                      > >
                      > > Thanks but unfortunately that is not an option for me as it is a huge
                      > > application that utilizes this table. Any suggestion on working the
                      > query to
                      > > get the desired results?
                      > >
                      > > Thanks again
                      > >
                      > > On Wed, Dec 2, 2009 at 7:07 PM, lansingbuddy <ansiguy@...<ansiguy%40gmail.com>>
                      > wrote:
                      > >
                      > > >
                      > > >
                      > > > So I have been working on generating a report and I came up with a few
                      > > > solutions but they all had some bugs. So I am not providing my
                      > solution here
                      > > > as they will only confuse you. Here is what I am trying to achieve. I
                      > have 2
                      > > > tables. And one keeps history of the other. I want to create a report
                      > as I
                      > > > have below under DESIRED OUTPUT.
                      > > >
                      > > > First table:
                      > > > CREATE TABLE [CustPremiumServices](
                      > > > [CustID] [int] NULL,
                      > > > [SiteID] [int] NULL,
                      > > > [PremiumServiceStartDate] [datetime] NULL,
                      > > > [PremiumServiceEndDate] [datetime] NULL,
                      > > > [Components] [varchar](200) NULL
                      > > > )
                      > > > Explanation of columns:
                      > > > CustID - Customers ID.
                      > > > SiteID - A customer's site ID.
                      > > > PremiumServiceStartDate & PremiumServiceEndDate - The site will have
                      > > access
                      > > > to premium components during the date ranges PremiumServiceStartDate &
                      > > > PremiumServiceEndDate.
                      > > > Components - a comma de-limited list of component ids.
                      > > >
                      > > > Second table:
                      > > > CREATE TABLE [PremiumComponentHistory](
                      > > > [ID] [uniqueidentifier] NOT NULL CONSTRAINT [ID] DEFAULT (newid()),
                      > > > [CustID] [int] NOT NULL,
                      > > > [SiteID] [int] NOT NULL,
                      > > > [Change] [varchar](25) NOT NULL,
                      > > > [ModifiedDate] [datetime] NOT NULL,
                      > > > [ComponentInvolved] [int] NULL,
                      > > > [PremiumServiceStartDate] [datetime] NOT NULL,
                      > > > [PremiumServiceEndDate] [datetime] NOT NULL
                      > > > )
                      > > > Explanation of columns:
                      > > > Change - will store of these values [Component Added, Component
                      > > Dropped,
                      > > > Service Date Changed]
                      > > > ModifiedDate - Date Record Added.
                      > > > ComponentInvolved - the id of the component that was either added or
                      > > > removed to the CustPremiumServices table.
                      > > >
                      > > > How history is kept:
                      > > > 1. Everytime we change either PremiumServiceStartDate or
                      > > > PremiumServiceEndDate we insert a record in the
                      > > PremiumComponentHistory
                      > > > table with the new dates and mark the change as "Service Date
                      > Changed".
                      > > > 2. Everytime we update the Components column, we identify and insert
                      > > > record(s) for each component added and dropped and mark the change as
                      > > > "Component Added" or "Component Dropped"
                      > > >
                      > > > The goal is to find out a list of sites that had a specific component
                      > > > available during a date range.
                      > > >
                      > > > The website will offer an interface for filtering history with all
                      > > > components listed with checkboxes near each of the components and two
                      > > date
                      > > > pickers. Depending on the criteria picked, the result should appear
                      > like
                      > > > this:
                      > > >
                      > > > CustID | SiteID | Component Involved | Was the component available to
                      > the
                      > > > website anytime during the date range? | Date component access was
                      > > granted |
                      > > > Date component access was denied (if applicable)
                      > > >
                      > > > DESIRED OUTPUT:
                      > > > 22 | 1 | 35 | Yes | 2005-06-01 05:00:00.000 | 2005-08-01 05:00:00.000
                      > > >
                      > > > If component access was granted/denied multiple times during the date
                      > > > range, print multiple and the number of times. like this:
                      > > >
                      > > > 22 | 2 | 12| Yes | Multiple(4 times) | Multiple(5 times)
                      > > >
                      > > > Sample data(randomly taken):
                      > > > CustPremiumServices
                      > > > 15 1 2004-03-09 10:00:00.000 2004-06-01 02:00:00.000 12,35
                      > > > 16 1 2000-08-01 00:00:00.000 2032-06-01 00:00:00.000 35
                      > > > 16 2 2007-06-01 00:00:00.000 2010-08-01 00:30:00.000 12
                      > > > 17 1 2004-09-06 00:20:00.000 2009-06-01 00:00:00.000 35
                      > > > 17 2 2007-06-01 00:10:00.000 2008-06-01 00:00:00.000 12,35
                      > > >
                      > > > PremiumComponentHistory
                      > > > 18 1 Service Date Modified 2009-12-02 11:40:10.410 NULL 2008-06-03
                      > > > 00:00:00.000 2008-11-02 00:00:00.000
                      > > > 19 1 Service Date Modified 2009-12-02 11:42:49.060 NULL 2008-06-03
                      > > > 00:00:00.000 2008-11-02 00:00:00.000
                      > > > 19 1 Component Added 2009-12-02 17:18:06.883 35 2013-06-03
                      > > 00:00:00.000
                      > > > 2008-11-02 00:00:00.000
                      > > > 14 2 Component Dropped 2009-12-02 17:18:06.880 12 2013-06-03
                      > > 00:00:00.000
                      > > > 2008-11-02 00:00:00.000
                      > > > 17 3 Component Added 2009-12-02 11:18:56.790 35 2008-06-03
                      > > 00:00:00.000
                      > > > 2014-02-02 00:00:00.000
                      > > > 17 4 Component Dropped 2009-12-02 17:18:02.660 12 2013-06-03
                      > > 00:00:00.000
                      > > > 2008-11-02 00:00:00.000
                      > > >
                      > > > Thanks for your help in advance,
                      > > > lansingbuddy
                      > > >
                      > > >
                      > > >
                      > >
                      > >
                      > > [Non-text portions of this message have been removed]
                      > >
                      > >
                      > >
                      > > ------------------------------------
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      >
                      >
                      >


                      [Non-text portions of this message have been removed]
                    Your message has been successfully submitted and would be delivered to recipients shortly.