Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    39

    Unanswered: complex sql server 2005 query

    Hi,
    A sql server table is populated with records every 2 minutes. See below sample table
    In the table, the Import_Date is a datetime field.

    create table tblData
    (
    ID int identity(1, 1),
    SourceID int,
    SourceCode varchar(255)
    Security varchar(255),
    Bprice decimal(12, 8),
    Aprice decimal(12, 8),
    ImportDate datetime
    )

    Here is a populated table.
    I have left gaps for better visual checks for you.

    ID SourceID SourceCode Security Bprice BpriceSize Aprice ApriceSize ImportDate

    1 1 sourceA SecA 100.2 2 99.12 1 2007-11-07 16:24:31.297
    2 2 sourceW SecH 95.7 89.43 2007-11-07 16:24:31.297
    3 3 SourceX SecS 50.56 1 76.44 4 2007-11-07 16:24:31.297
    4 4 SourceQ SecZ 87.98 2007-11-07 16:24:31.297
    5 5 SourceJ SecH 100.2 99.12 2 2007-11-07 16:24:31.297
    6 6 SourceK SecU 2007-11-07 16:24:31.297
    7 7 SourceT SecA 50.56 3 87.11 2007-11-07 16:24:31.297

    8 1 sourceA SecA 100.2 6 99.12 2 2007-11-07 16:26:15.123
    9 2 sourceW SecH 99.54 4 89.43 2007-11-07 16:26:15.123
    10 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
    11 4 SourceQ SecZ 16.98 87.98 2007-11-07 16:26:15.123
    12 5 SourceJ SecH 100.2 1 99.12 2 2007-11-07 16:26:15.123
    13 6 SourceK SecU 2007-11-07 16:26:15.123
    14 7 SourceT SecA 50.56 2 87.11 1 2007-11-07 16:26:15.123

    15 1 sourceA SecA 100.2 1 87.11 1 2007-11-07 16:26:15.123
    16 2 sourceW SecH 99.66 89.43 2 2007-11-07 16:26:15.123
    17 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
    18 4 SourceQ SecZ 16.98 3 87.98 3 2007-11-07 16:26:15.123
    19 5 SourceJ SecH 100.2 3 99.12 3 2007-11-07 16:26:15.123
    20 6 SourceK SecU 2007-11-07 16:26:15.123
    21 7 SourceT SecA 101.32 5 87.11 3 2007-11-07 16:26:15.123
    ...

    I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
    In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
    This is what I would like to see:

    Security Max_Bprice Bprice_Size Bprice_SourceCode Min_Aprice Aprice_Size Aprice_SourceCode

    SecA 101.32 5 SourceT 87.11 4 SourceA, SourceT
    SecH 100.2 3 SourceJ 89.43 2 SourceW
    SecS 50.56 2 SourceX 19.33 SourceX
    SecZ 16.98 3 SourceQ 87.98 3 SourceQ


    What is the sql query to do this please?

    This is what I have started with but it is not correct...

    select
    Security,
    max(Bprice) as 'Max_Bprice',
    SourceCode as 'Bprice_SourceCode',
    min(Aprice) as 'Min_Aprice',
    SourceCode as 'Aprice_SourceCode'
    from
    tblData
    group by
    Security,
    SourceCode

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You are almost there but not quite. Could you provide your sample data as point 3 here please:
    http://www.dbforums.com/showthread.php?t=1196943

    Also, your DDL does not match the data you have supplied.

    Not really related, but there looks to be a third normal form issue here.

    Cheers

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  4. #4
    Join Date
    Aug 2006
    Posts
    39

    sample data

    Here it is.
    Thanks

    DECLARE @Sample TABLE (ID INT, SourceID INT, SourceCode VARCHAR(20), Security VARCHAR(20), Bprice MONEY, BpriceSize INT, Aprice MONEY, ApriceSize INT, ImportDate DATETIME)

    INSERT @Sample
    SELECT 1, 1, 'sourceA', 'SecA', 100.2 , 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
    SELECT 2, 2, 'sourceW', 'SecH', 95.7 , NULL, 89.43, NULL, '2007-11-07 16:24:31.297' UNION ALL
    SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
    SELECT 4, 4, 'SourceQ', 'SecZ', 87.98, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
    SELECT 5, 5, 'SourceJ', 'SecH', 100.2 , NULL, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
    SELECT 6, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
    SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, NULL, '2007-11-07 16:24:31.297' UNION ALL
    SELECT 8, 1, 'sourceA', 'SecA', 100.2 , 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, NULL, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, NULL, 87.98, NULL, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 12, 5, 'SourceJ', 'SecH', 100.2 , 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 13, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 15, 1, 'sourceA', 'SecA', 100.2 , 1, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 16, 2, 'sourceW', 'SecH', 99.66, NULL, 89.43, 2, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 19, 5, 'SourceJ', 'SecH', 100.2 , 3, 99.12, 3, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 20, 6, 'SourceK', 'SecU', NULL , NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
    SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'll leave it to Peso. He'll get it soon enough I would imagine.

  6. #6
    Join Date
    Aug 2006
    Posts
    39

    data

    Thanks anyway

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I suggest that you break it up

    Do one, then the other, then combine them

    I think you can do a union or a join of 2 derived table.

    Since each derived table is going to be a single row, you wont have to worry about a cartesian product
    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.

Posting Permissions

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