Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Question Unanswered: How to avoid aggregate functions and TOP/BOTTOM ?

    Here's my table:
    Code:
    CREATE TABLE TAB1 
    (
    curr tinyint NOT NULL ,
    ddate smalldatetime NOT NULL ,
    rate decimal(15, 4) NOT NULL ,
    primary key (curr, ddate)
    )
    This is its content:
    ddate curr rate
    ----------------------- ---- ---------------------------------------
    2000-01-01 00:00:00 1 5
    2000-01-02 00:00:00 1 6
    2000-01-02 00:00:00 2 3
    2000-01-03 00:00:00 2 4
    2000-01-14 00:00:00 1 7
    2000-01-15 00:00:00 3 8
    If "rate" didn't change in comparision with previous value of "ddate" then table doesn't contain new record for that unchanged date.

    I need to write a query that returns all "currency" for the specified date. This query MUSTN'T contain any aggregate functions and no TOP/BOTTOM words are allowed there as well. Any ideas how to write this query?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Boy, this sure sounds like a homework assignment . . .

    . . . is that what it is?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give an example of the results that you want? If I interpret this correct, the query is really basic.

    I am willing to help you with your home work, not make it for you. So show us what you came up with so far. And we will go on from there.
    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

  4. #4
    Join Date
    Apr 2011
    Posts
    3

    Question

    Here's my data definition:
    Code:
    --===== If the test table already exists, drop it     
    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
            DROP TABLE #mytable
    
    --===== Create the test table with  
    CREATE TABLE #mytable 
    (
    curr tinyint NOT NULL ,
    ddate smalldatetime NOT NULL ,
    rate decimal(15, 4) NOT NULL ,
    primary key (curr, ddate)
    )
    
    
    
    --===== Setup any special required conditions especially where dates are concerned    
    SET DATEFORMAT DMY
    
    
    --===== Insert the test data into the test table 
    INSERT INTO #mytable        (curr,ddate, rate) 
    
    SELECT '1','Jan  1 2000 12:00AM','5'  UNION ALL
    SELECT '2','Jan  1 2000 12:00AM','55' UNION ALL
    SELECT '1','Jan  2 2000 12:00AM','6'  UNION ALL
    SELECT '2','Jan  2 2000 12:00AM','3'  UNION ALL
    SELECT '2','Jan  3 2000 12:00AM','4'  UNION ALL
    SELECT '1','Jan 14 2000 12:00AM','7'  UNION ALL
    SELECT '2','Jan 15 2000 12:00AM','8'
    These are some results I expect to have for particular dates:


    select curr, rate
    from (................)
    where ddate='01-01-2000'

    curr rate
    ---- ---------------------------------------
    1 5.0000
    2 55.0000



    select curr, rate
    from (................)
    where ddate='02-01-2000'

    curr rate
    ---- ---------------------------------------
    1 6.0000
    2 3.0000


    select curr, rate
    from (................)
    where ddate='03-01-2000'

    curr rate
    ---- ---------------------------------------
    1 6.0000
    2 3.0000


    select curr, rate
    from (................)
    where ddate='04-01-2000'

    curr rate
    ---- ---------------------------------------
    1 6.0000
    2 3.0000



    select curr, rate
    from (................)
    where ddate='05-01-2000'

    curr rate
    ---- ---------------------------------------
    1 6.0000
    2 3.0000


    select curr, rate
    from (................)
    where ddate='13-01-2000'

    curr rate
    ---- ---------------------------------------
    1 6.0000
    2 3.0000


    select curr, rate
    from (................)
    where ddate='14-01-2000'

    curr rate
    ---- ---------------------------------------
    1 7.0000
    2 3.0000


    select curr, rate
    from (................)
    where ddate='15-01-2000'

    curr rate
    ---- ---------------------------------------
    1 7.0000
    2 8.0000


    select curr, rate
    from (................)
    where ddate='16-01-2000'

    curr rate
    ---- ---------------------------------------
    1 7.0000
    2 8.0000

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Ok, I think I get it now. You have some kind of a sparse table.

    What you basically have to do is find all different curr(encies) that are in the table before and including the date given. Try to make a query that does just that.

    Then starting from those curr(encies), get the rate for the last date, before and including the date given. Try to make that query.

    Show us what you can come up with.
    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

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by senglory View Post
    This query MUSTN'T contain any aggregate functions and no TOP/BOTTOM words are allowed there as well. Any ideas how to write this query?
    What have you recently coverer in class? Maybe your assignment wants you to use Windowed functions like rank()

  7. #7
    Join Date
    Apr 2011
    Posts
    3

    Exclamation The answer is:

    This is the solution for my question:
    Code:
    select t.curr, t.rate
      from tab1 t
      where t.ddate <= @date and
            not exists(select *
                         from tab1 n
                         where n.curr = t.curr and
                               n.ddate > t.ddate and
                               n.ddate <= @date
                       )
    Thanks the guy from that site Mirko Marovi

Posting Permissions

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