Professional Web Applications Themes

CrossTab Question - Microsoft SQL / MS SQL Server

Here's a different twist on my previous question. Any help is tremendously appreciated. If any more information is needed I'll be happy to provide it. SELECT TOP 100 PERCENT MonthName, Month, SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE 0 END) AS Complete, SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0 END) AS New, SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0 END) - SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE 0 END) AS Outstanding FROM vwNewComplete GROUP BY MonthName, month ORDER BY 2 which returns the following results: MonthName Month Complete New Outstanding Feb-2003 2 71 ...

  1. #1

    Default CrossTab Question

    Here's a different twist on my previous question. Any help is tremendously
    appreciated. If any more information is needed I'll be happy to provide it.

    SELECT TOP 100 PERCENT
    MonthName,
    Month,
    SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE 0
    END) AS Complete,
    SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0 END)
    AS New,
    SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0
    END) - SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE 0 END) AS
    Outstanding
    FROM vwNewComplete
    GROUP BY MonthName, month
    ORDER BY 2


    which returns the following results:

    MonthName Month Complete New Outstanding
    Feb-2003 2 71 98
    27
    Mar-2003 3 41 63
    22
    Apr-2003 4 252 306
    54
    May-2003 5 527 562
    35
    Jun-2003 6 277 295
    18
    Jul-2003 7 30
    -5


    This is essentially what I'm looking for except for one problem. Currently
    my outstanding column is just New - Complete. I need it to be:

    Last Month's Outstanding + This Month's New - This Month's Complete.

    The numbers I'm looking for are below.


    MonthName Month Complete New Outstanding
    Corrected Outstanding
    Feb-2003 2 71 98
    27 27
    Mar-2003 3 41 63
    22 49
    Apr-2003 4 252 306
    54 103
    May-2003 5 527 562
    35 138
    Jun-2003 6 277 295
    18 156
    Jul-2003 7 30
    -5 151





    Jason MacKenzie Guest

  2. #2

    Default Re: CrossTab Question

    Actually the best way to word it now that I look at it is that I need to do
    a running total on the Outstanding column. Can anyone help with that?


    Jason MacKenzie Guest

  3. #3

    Default Re: CrossTab Question

    Try:
    drop table vwname
    create table vwname
    (monthname varchar(500),
    complete int,
    new int,
    outstanding int
    )

    insert into vwname values('Feb-2003',71 , 98,27)
    insert into vwname values('mar-2003',41 , 63 ,22)
    insert into vwname values('apr-2003',252 , 306 ,54 )
    insert into vwname values('may-2003',527 , 562 ,35 )
    insert into vwname values('jun-2003',277 , 295 ,18 )
    insert into vwname values('jul-2003',30 , -5 ,-5 )

    Following query is without making use of outstanding amount. ie i m
    considering only 3 columns of the table ie complete,new and monthname

    select new_mon,complete,new, outstanding,
    (select sum(new-complete) from
    (select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v2
    where new_mon <= v1.new_mon) correct_outstanding
    from
    (select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v1

    OR
    Following query makes use of outstanding amount column. ie i m considering
    all 4 columns of the table ie complete,new, outstanding and monthname

    select new_mon,complete,new, outstanding,
    (select sum(outstanding) from
    (select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v2
    where new_mon <= v1.new_mon) correct_outstanding
    from
    (select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v1

    --
    -Vishal
    "Jason MacKenzie" <jmackenzie_nospamformet.com> wrote in message
    news:#$IjJ7lQDHA.2432TK2MSFTNGP10.phx.gbl...
    > Here's a different twist on my previous question. Any help is tremendously
    > appreciated. If any more information is needed I'll be happy to provide
    it.
    >
    > SELECT TOP 100 PERCENT
    > MonthName,
    > Month,
    > SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE
    0
    > END) AS Complete,
    > SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0
    END)
    > AS New,
    > SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0
    > END) - SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE 0 END) AS
    > Outstanding
    > FROM vwNewComplete
    > GROUP BY MonthName, month
    > ORDER BY 2
    >
    >
    > which returns the following results:
    >
    > MonthName Month Complete New Outstanding
    > Feb-2003 2 71 98
    > 27
    > Mar-2003 3 41 63
    > 22
    > Apr-2003 4 252 306
    > 54
    > May-2003 5 527 562
    > 35
    > Jun-2003 6 277 295
    > 18
    > Jul-2003 7 30
    > -5
    >
    >
    > This is essentially what I'm looking for except for one problem.
    Currently
    > my outstanding column is just New - Complete. I need it to be:
    >
    > Last Month's Outstanding + This Month's New - This Month's Complete.
    >
    > The numbers I'm looking for are below.
    >
    >
    > MonthName Month Complete New Outstanding
    > Corrected Outstanding
    > Feb-2003 2 71 98
    > 27 27
    > Mar-2003 3 41 63
    > 22 49
    > Apr-2003 4 252 306
    > 54 103
    > May-2003 5 527 562
    > 35 138
    > Jun-2003 6 277 295
    > 18 156
    > Jul-2003 7 30
    > -5 151
    >
    >
    >
    >
    >

    Vishal Parkar Guest

Similar Threads

  1. Crosstab Query Export
    By PrinBD in forum Coldfusion Database Access
    Replies: 1
    Last Post: January 17th, 05:41 PM
  2. Crosstab Query with Date Fields
    By mostlySimple in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 5th, 04:28 AM
  3. Crosstab query with multiple value fields
    By melody in forum ASP Database
    Replies: 1
    Last Post: October 20th, 01:02 AM
  4. Crosstab query - Nearly there!
    By Vishal Parkar in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 09:57 AM
  5. No true Crosstab Query in Oracle???
    By damorgan in forum Oracle Server
    Replies: 0
    Last Post: December 30th, 05:48 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