Results 1 to 7 of 7

Thread: just query

  1. #1
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96

    Unanswered: just query

    in MsSQL


    USE Northwind
    GO

    SET NOCOUNT ON
    CREATE TABLE myTable99( type char(10),[date] datetime, price money
    , PRIMARY KEY (type, [date]))
    GO

    INSERT INTO myTable99(type,[date],price)
    SELECT 'bat' ,'1/1/2001',10.00 UNION ALL
    SELECT 'bat' ,'1/1/2002',20.00 UNION ALL
    SELECT 'bat' ,'1/1/2003',30.00 UNION ALL
    SELECT 'bat' ,'1/1/2004',40.00 UNION ALL
    SELECT 'ball','1/1/2001',40.00 UNION ALL
    SELECT 'ball','1/1/2002',30.00 UNION ALL
    SELECT 'ball','1/1/2003',20.00 UNION ALL
    SELECT 'ball','1/1/2004',10.00
    GO


    What I need, is to create view
    with the result


    type date (date from) price date_to
    ball 2001-01-01 40.0000 2001-12-31 (next date -1 day)
    ball 2002-01-01 30.0000 2002-12-31
    ball 2003-01-01 20.0000 2003-12-31
    ball 2004-01-01 10.0000 today
    bat 2001-01-01 10.0000 2001-12-31
    bat 2002-01-01 20.0000 2002-12-31
    bat 2003-01-01 30.0000 2003-12-31
    bat 2004-01-01 40.0000 today



    it's needed to be able to add new dates, and prices

    Help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    something like this (untested) --
    Code:
    select type 
         , [date] as date_from
         , price 
         , case when dateadd(dd,-1,dateadd(yy,1,[date]))
                      > getdate() 
                then 'today'
                else convert(char(10),
                     dateadd(dd,-1,dateadd(yy,1,[date]))
                            ,120)  as date_to 
      from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    Thanks but I mean smth. else



    type date (date from) price date_to
    ball 2001-01-01 40.0000 2001-12-31 (next date -1 day)
    ball 2002-01-01 30.0000 2002-12-31
    ball 2003-01-01 20.0000 2003-12-31
    ball 2004-01-01 10.0000 today
    bat 2001-01-01 10.0000 2001-12-31
    bat 2002-01-01 20.0000 2002-12-31
    bat 2003-01-01 30.0000 2003-12-31
    bat 2004-01-01 40.0000 today = getdate()

    but i will add the rows to mu table like

    bat 2004-05-01 50.0000


    so
    the result
    ....
    ...
    bat 2004-01-01 40.0000 2004-04-30 (the next date -1 day)
    bat 2004-05-01 50.0000 today = getdate()


    some of types will be 2 dates and 2 prices but some of it many.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I remeber this...what the hell does "today" mean in the result set?
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    today is today = getdate()

  6. #6
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    in the table are the dates of new prices and the new prices (and type - mean product)

    what i need is the periods when the prices is good
    the last price is good unles today =getdate()

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i thought i understood what you wanted but now i don't have a clue

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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