Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql question (urgent)

Posted on 2007-10-04
4
Medium Priority
?
227 Views
Last Modified: 2010-03-19
My query has these columns:      
PO Number      PO Create Line Amount USD      PO DISTRIBUTION
1      $0                                               0-500
2      $5                                               0-500
3      $20,000                                               10,000-20,000
4      $100,000                                               75,000+
I need an extra column in my table. The extra column should be called podistribution. If the po create line amount is $0-$500 I want to put the phrase "0-500" in the newly created column called "po distibution", ETC.

 Is there a way I can do this?

The reason I need the column is so that I can work with the data in excel/pivot table via a data connection. I would group this data via the excel pivot table functionality but there are too many rows for excel so I am hoping to bucket the data on the sql side and then do the data connection and work with a pivot. The pivot is also needed as a non-sql user will be receiving the raw data to review. Thanks.



list of distributions:
0-500
501-1,000
1,001-2,000
2,001-3,000
3,001-4,000
4,001-5,000
5,001-6,000
6,001-7,000
7,001-8,000
8,001-9,000
9,001-10,000
10,000+
20,000+
50,000+
75,000+
0
Comment
Question by:SA4
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 800 total points
ID: 20019018
Hello SA4,

Create a new table, Buckets, with columns AtLeast, LessThan, and BucketName.  Set it up with values
like this:

AtLeast            LessThan      BucketName
-------------------------------------------------------------------
0            501            0-500
501            1001            501-1000
1001            2001            1001-2000
etc.


Then, use a query like this:


SELECT b.BucketName, SUM(o.LineAmount) AS TotalOrderAmt
FROM Buckets b INNER JOIN
    Orders o ON o.LineAmount >= b.AtLeast And o.LineAmount < b.LessThan
GROUP BY b.BucketName


Regards,

Patrick
0
 
LVL 19

Expert Comment

by:folderol
ID: 20019330
create view pt_report as
select [PO Number],
[PO Create Line Amount USD],
[PO DISTRIBUTION]
from Orders join Buckets on
Orders.[PO Create Line Amount USD] between Buckets.AtLeast and Buckets.LessThan

Follow Patrick's suggestions for Buckets, only you don't need to add 1 to LessThan if you use between.  You do have to always specify a value for LessThan, so the last row will be

AtLeast, LessThan, [PO Distribution]
0, 500, '0-500'
-- etc
-- etc
-- etc
75001, 999999999, '75,000+'

For your pivottable in Excel, simply use
select * from pt_report

Tom
0
 
LVL 5

Assisted Solution

by:ursangel
ursangel earned 600 total points
ID: 20020343
try this procedure, it will help you update the column.

create proc pr_update

as begin
      declare @prev money;

      declare cr_Test cursor for select POAMOUNT from Test

      open cr_Test;

      fetch next from cr_Test  into @prev

      while (@@fetch_status = 0) begin
            If (@prev >= 0  and @prev < 500)
                        update test set POdistribution = '0 - 500' where poamount = @prev
            else if (@prev >= 500  and @prev < 1000)
                        update test set POdistribution = '500 - 1000' where poamount = @prev
            else if (@prev >= 500  and @prev < 1000)
                        update test set POdistribution = '500 - 1000' where poamount = @prev
            else if (@prev >= 1000  and @prev < 2000)
                        update test set POdistribution = '1000 - 2000' where poamount = @prev
            else if (@prev >= 2000  and @prev < 3000)
                        update test set POdistribution = '2000 - 3000' where poamount = @prev
            else if (@prev >= 3000  and @prev < 4000)
                        update test set POdistribution = '3000 - 4000' where poamount = @prev
            else if (@prev >= 4000  and @prev < 5000)
                        update test set POdistribution = '4000 - 5000' where poamount = @prev
            else if (@prev >= 5000  and @prev < 6000)
                        update test set POdistribution = '5000 - 6000' where poamount = @prev
            else if (@prev >= 6000  and @prev < 7000)
                        update test set POdistribution = '6000 - 7000' where poamount = @prev
            else if (@prev >= 7000  and @prev < 8000)
                        update test set POdistribution = '7000 - 8000' where poamount = @prev
            else if (@prev >= 8000  and @prev < 9000)
                        update test set POdistribution = '8000 - 9000' where poamount = @prev
            else if (@prev >= 9000  and @prev < 10000)
                        update test set POdistribution = '9000 - 10000' where poamount = @prev
            else if (@prev >= 10000  and @prev < 20000)
                        update test set POdistribution = '10000 +' where poamount = @prev
            else if (@prev >= 20000  and @prev < 30000)
                        update test set POdistribution = '30000 +' where poamount = @prev

            fetch next from cr_Test into @prev;
      end
      
      close cr_Test;
      deallocate cr_Test;
      


end
0
 
LVL 6

Assisted Solution

by:twintai
twintai earned 600 total points
ID: 20029702
i'm sure if you are trying to get this result for a query side only or you want it on the table as well. On query result only, you can use case statments...

Select *,
      case  
      when number between 0 and 500 then '$0-$500'
      when number between 501 and 1000 then '$501-$1000'
      .
      .
      .
      when number >= 10000 then '$10,000+'
      when number >= 20000 then '$20,000+'
      end as [po distibution]
from table

this will give the disired result within a query. The above comment with cursor is a lenghty way to get the same result.

If you want the result on the table it self. use the same case statement for the column fomular. This is the best way. FYI you can even updated based on the above case statement.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question