Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 27th, 2003, 01:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default Odd Grouping Request

I have some data that I need to group I currently group on severla columns

Lets say I have the following fields and data

Item Grade Type Weight Sequence
1 A RR 11.2 1
1 A RR 11.2 2
2 B RR 12.7 3
1 A RR 11.2 4

I currently group on Item, Grade, Type and Weight and get this

Item Grade Type Weight Sequence Count
1 A RR 11.2 1 (3)
2 B RR 12.7 3 (1)

(I just take the minimum Sequence number on the group by - I added the Count field so you could see how many records were grouped there. There are many other columns also involved, but they are just totaled up so there is really a reason for me grouping these records.)

I somehow need to order this by the sequence and then group if they are next to each other only. This is what it would look like:

Item Grade Type Weight Sequence Count
1 A RR 11.2 1 (2)
2 B RR 12.7 3 (1)
1 A RR 11.2 4 (1)

Can anyone help me accomplish this? I am not sure how I would go about this. We have an existing report that runs like this (Old code off of an AS400 IBM OS) and I need to reporduce the report in ASP. I have the report basically done and I just now discovered this SQL problem. I am anxiously awaiting your replies.

Thanks in advance
__________________
Chris
Reply With Quote
  #2 (permalink)  
Old June 30th, 2003, 07:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've been thinking about this problem a little and the best I have been able to come up with so far is this:

Code:
CREATE TABLE #Report
(
ReptID Int IDENTITY,
Grade char(1),
Type char(2),
Weight decimal(3,1),
Sequence Int
)

INSERT INTO #Report
(Grade, Type, Weight, Sequence)
SELECT Grade, Type, Weight, Sequence
FROM MyTable

SELECT ReptID, Grade, Type, Weight, Sequence
FROM #Report tR
WHERE NOT EXISTS (SELECT NULL
                  FROM #Report t2R
                  WHERE t2R.ReptID = tR.ReptID + 1
                      AND t2R.Item = tR.Item)

DROP TABLE #Report
This should return exactly what you want but without the COUNT column. If you want the count column I'll see if I can come up with something.

regards
David Cameron
Reply With Quote
  #3 (permalink)  
Old July 10th, 2003, 02:36 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Romania.
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think a rowset with the Type & Grade & Weight & Sequence is insufficient for kilika, since he (she) wants to 'group by' so some totals can be calculated.

here is my solution: (i hope this is not to late)

i assume that the data that needs grouping is in a table named T1. also, i assume that 'next to each other' is relative to sequence.

you need a supplementary column (i called it GroupNo of type int) in the T1 table.

code:

Code:
set nocount on

--change variable data types acording to your data types
declare @Item char(10), @Grade char(10), @Type char(10),
    @Weight char(10), @GroupNo int
declare @ItemLast char(10), @GradeLast char(10), @TypeLast char(10),
    @WeightLast char(10)

declare @First bit

declare crsT1 cursor local for
    select Item, Grade, Type, Weight
    from T1
    order by Sequence

open crsT1

fetch next from crsT1 into @Item, @Grade, @Type, @Weight

set @First = 1

while @@fetch_status = 0
begin
    if @First = 1
    begin
        set @GroupNo = 1
        set @First = 0
    end
    else if @Item <> @ItemLast or @Grade <> @GradeLast or @Type <> @TypeLast
        or @Weight <> @WeightLast
    begin
        set @GroupNo = @GroupNo + 1
    end

    update T1
    set GroupNo = @GroupNo
    where current of crsT1

    set @ItemLast = @Item
    set @GradeLast = @Grade
    set @TypeLast = @Type
    set @WeightLast = @Weight

    fetch next from crsT1 into @Item, @Grade, @Type, @Weight
end

select Item, Grade, Type, Weight, min(Sequence) as Sequence, count(*) as Count
from T1
group by Item, Grade, Type, Weight, GroupNo
order by min(Sequence)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
request forwarging & request redirection hafizmuhammadmushtaq Servlets 2 April 24th, 2008 12:42 AM
Request.Form / Request.QueryString Toran Classic ASP Databases 4 January 17th, 2007 02:23 PM
Protect cells and allow grouping/un-grouping sfreuden Excel VBA 4 December 14th, 2006 08:01 AM
request.qurystring vs. request.form Durwood Edwards Classic ASP Databases 3 June 18th, 2004 12:09 AM
request.querystring() , request.form() alyeng2000 ASP.NET 1.0 and 1.1 Basics 1 December 30th, 2003 12:07 AM



All times are GMT -4. The time now is 12:47 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.