Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61

    Question Unanswered: Historical Stats

    Ok folks,
    Need your help once again.

    I have 4 Tables that need to get accurate stats from....

    2 are historical, 2 are current

    the 2 historical are identical to the 2 present ones, just the historical is everything up to a cutoff date, the the 2 present is everything from that cutoff date on...

    They contain login information, access times, install counts etc.

    eg. OldTable1 = serialNo, ID, Install Date, NoOfInstalls, etc..
    OldTable2 = SerailNo, Accessdate, Installid, UserID etc.
    NewTable1 = serialNo, ID, Install Date, NoOfInstalls, etc..
    NewTable2 = SerailNo, Accessdate, Installid, UserID etc.
    I need to get total installs (gathered from both tables), total accesses after given dates etc...

    the records are created at each login, so records may be in both sets of tables, (ie, if accessed after cutoff, a new record is created in the new tables...)

    Any ideas?

    Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Look up the use of the UNION operator in Books Online. It can be used to effectively combine two tables for processing, or to combine the results of two select statements.

    blindman

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And if you post some DDL, DML and sample data, I bet we can help alot more..

    CREATE TABEL myTable99 (col1 int, ect

    INSERT INTO myTable99 (col1, ect
    SELECT 1, ect UNION ALL
    SELECT 1, ect UNION ALL
    SELECT 1, ect UNION ALL
    SELECT 1, ect

    SELECT * FROM myTable99 WHERE ect -- or whatever you've tried som far...

    might be worth your time...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61

    Thanks so far, on the right track...

    This is what I am trying, but it has not been working..I get this is not permitted when the subquery follows .... (or when the subquery follows and expression)

    I am trying to do this with out a new table, but a view is fine...

    I am just trying to count version 4s from old, version 4s from new, then the union that combines (just as a test audit for now)

    should be minimal overhead this way...

    CREATE VIEW dbo.FV_Serial_Reg
    AS

    SELECT COUNT(SerialNumber) AS Ver4_Old,
    (

    SELECT COUNT(SerialNumber)
    FROM FRV.dbo.WFSerialNumber
    WHERE left(Version,1) = '4'
    ) AS Ver4_New,
    (
    SELECT Version4 = COUNT(SerialNumber) FROM FRV.dbo.WFSerialNumber WHERE left(Version,1) = '4'
    UNION
    SELECT COUNT(SerialNumber) FROM FRV.dbo.WFSerialNumber_PREV WHERE left(Version,1) = '4'
    ) AS Version4



    FROM FRV.dbo.WFSerialNumber_Prev
    WHERE left(Version,1) = '4'

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DUDE......

    ya lost me...is there a question here?

    why are you doing what you're doing?

    Does it work
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61
    I mentioned the error in the previous post...

    This is what I am trying, but it has not been working..

    I get this is not permitted when the subquery follows .... (or when the subquery follows and expression)


    I need these stats in a view so that we access them via a data access page created with MS Access.

Posting Permissions

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