Professional Web Applications Themes

Query - Microsoft SQL / MS SQL Server

Sample data is: Name Date Cost Computer1 25/02/2003 1913.35 Computer2 28/03/2003 969.38 Computer3 04/07/2003 900.00 Results should be: For year end 30/06/2003 Name Date Cost NewValue Computer1 25/02/2003 1913.35 1276 Computer2 28/03/2003 969.38 646 Computer3 04/07/2003 900.00 785 The new column is the cost less less a 1/3 (then rounded off). Following years, so if computer1 was from 2002, it would have to lose 2/3. Computers will be equal to 0 after 3 years....

  1. #1

    Default Query

    Sample data is:
    Name Date Cost
    Computer1 25/02/2003 1913.35
    Computer2 28/03/2003 969.38
    Computer3 04/07/2003 900.00

    Results should be: For year end 30/06/2003
    Name Date Cost NewValue
    Computer1 25/02/2003 1913.35 1276
    Computer2 28/03/2003 969.38 646
    Computer3 04/07/2003 900.00 785

    The new column is the cost less less a 1/3 (then rounded
    off). Following years, so if computer1 was from 2002, it
    would have to lose 2/3. Computers will be equal to 0 after
    3 years.

    Wayde Guest

  2. #2

    Default Re: Query

    Wayde,

    I didn't get how you got the newValue for Computer3 as 785. Considering it
    as typo error, I proceeded. Given below is the required DDL and query.

    Need to add the round off function to newValue.

    HTH

    Praveen Maddali,
    MCSD, MCDBA

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

    create table #t2
    (cName varchar(20),
    cDate smalldatetime,
    Cost decimal(10,2)
    )

    truncate table #t2
    Insert into #t2 values ('Computer1','02/25/2003',1913.35)
    Insert into #t2 values ('Computer2','03/28/2003',969.38)
    Insert into #t2 values ('Computer3','07/04/2003',900)
    Insert into #t2 values ('Computer1','02/25/2002',1913.35)
    Insert into #t2 values ('Computer1','01/25/2000',1913.35)


    select * from #t2

    declare Dt smalldatetime
    set dt='06/30/2003'

    select *,
    case
    when cdate>dt then Cost
    when datediff(yy,cdate,dt)=0 then cost*2/3
    when datediff(yy,cdate,dt)=1 then cost/3
    else 0
    end as NewValue
    from #t2


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

    "Wayde" <com> wrote in message
    news:097101c34c4e$9249dc60$gbl... 


    praveen Guest

  3. #3

    Default Re: Query

    Yes, it was a mistake. Anyway, I just wanted to say thank
    you, it works perfectly.
    Wayde Guest

  4. #4

    Default query

    i've a table as below

    table1
    f1
    --
    a
    b
    c
    d

    i want results like below
    f1 test
    -- ----
    a a
    a b
    a c
    a d
    b a
    b b
    b c
    b d
    c a
    c b
    c c
    c d
    d a
    d b
    d c
    d d

    thank you
    priya Guest

Similar Threads

  1. Query of Queries with constructed query using QueryNew()and QueryAddRow()
    By LeadFoot in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 4th, 02:00 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139