Professional Web Applications Themes

TSQL OR NOT TSQL this is the ? - Microsoft SQL / MS SQL Server

Hi Simon, I'm assuming in the following example that you have defined your month column as a smalldatetime, and that you store the first of the month as the date: CREATE TABLE MyInvestment ( [Month] SMALLDATETIME NOT NULL, [Value] MONEY NOT NULL, CONSTRAINT PK_MyInvestment PRIMARY KEY ([Month])) GO INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030101', 36000) INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030201', 42500) INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030301', 32400) INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030401', 29600) SELECT mi.[Month], mi.[Value], CAST((mi.[Value] - mim.[Value]) * 100./ mim.[Value] AS decimal(9,2)) AS MonthlyPerformance, CAST((mi.[Value] - miy.[Value]) * 100./ miy.[Value] AS decimal(9,2)) AS YTDPerformance FROM ...

  1. #1

    Default Re: TSQL OR NOT TSQL this is the ?

    Hi Simon,

    I'm assuming in the following example that you have defined your month
    column as a smalldatetime, and that you store the first of the month as the
    date:

    CREATE TABLE MyInvestment (
    [Month] SMALLDATETIME NOT NULL,
    [Value] MONEY NOT NULL,
    CONSTRAINT PK_MyInvestment PRIMARY KEY ([Month]))
    GO
    INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030101', 36000)
    INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030201', 42500)
    INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030301', 32400)
    INSERT INTO MyInvestment([Month], [Value]) VALUES ('20030401', 29600)



    SELECT mi.[Month], mi.[Value],
    CAST((mi.[Value] - mim.[Value]) * 100./ mim.[Value] AS decimal(9,2)) AS
    MonthlyPerformance,
    CAST((mi.[Value] - miy.[Value]) * 100./ miy.[Value] AS decimal(9,2)) AS
    YTDPerformance
    FROM MyInvestment mi
    LEFT OUTER JOIN MyInvestment mim
    ON mim.[Month] = DATEADD(mm, -1, mi.[Month])
    LEFT OUTER JOIN MyInvestment miy
    ON miy.[Month] = DATEADD(mm, -(MONTH(mi.[Month]) -1), mi.[Month])

    DROP TABLE MyInvestment


    "Simon" <net> wrote in message
    news:phx.gbl... 
    row...).but 
    sort 


    Jacco Guest

  2. #2

    Default TSQL OR NOT TSQL this is the ?

    Hi,

    I believe I am over using T-sql, and would appreciate some help with SQL

    I have the following fantasy portfolio

    MyInvestments
    Month Value
    Jan/02 36,000
    Feb/02 42,500
    Mar/02 32,400
    Apr/02 29,600

    I want to return the following

    MonthlyPerformance% (compared with previous month)
    YTDPerformance% (from Start of year)

    Currently I have been using t-sql (hold each value, move row by row...).but
    could I use just SQL by using two copies of the source table with some sort
    of join....

    Appreciate any help

    Regards,

    Simon




    Simon Guest

  3. #3

    Default Re: TSQL OR NOT TSQL this is the ?

    Simon,

    If you can post the DDL and required output then it will be easy to help.
    Any how you can easily do this if you have an identity column/sequential
    Primarykey in your source table.



    Create table #MyInvestments
    (
    Slno int identity (1,1),
    xMonth varchar(10),
    Value int
    )

    Insert into #MyInvestments (xMonth,value) values('Jan/02', 36000)
    Insert into #MyInvestments (xMonth,value) values('Feb/02', 42500)
    Insert into #MyInvestments (xMonth,value) values('Mar/02', 32400)
    Insert into #MyInvestments (xMonth,value) values('Apr/02', 29600)

    select A.xmonth,A.value as CurMonthValue,B.value as PrevMonthValue,
    round((A.value-B.value)*100.0/B.value,2) as [MonthlyPerformance%]
    from #MyInvestments A
    left join #MyInvestments B on A.slno=b.slno+1

    select A.xmonth,A.value as CurMonthValue,B.value YearBeginValue,
    round((A.value-B.value)*100.0/B.value,2) as [YTDPerformance%]
    from #MyInvestments A
    CROSS join (Select * from #MyInvestments where slno=1) B

    drop table #MyInvestments

    Note: The calculation of YTDPerformance will fail if you have more than one
    year data in the table. If you can post your exact table structure then I
    can help you.

    HTH
    Praveen Maddali


    "Simon" <net> wrote in message
    news:phx.gbl... 
    row...).but 
    sort 


    praveen Guest

Similar Threads

  1. How u convert this cfquery to TSQL?
    By roofusthedoofus in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 22nd, 09:13 PM
  2. TSQL Query
    By Rayan in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 12:48 PM
  3. Is it possible to do this using TSQL instead of cursor ??
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 05:50 PM
  4. Please improve this TSQL algorithm ..
    By Krist Lioe in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 08:18 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