grouping continuous records in a predefined order

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

grouping continuous records in a predefined order

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-22 : 17:46:43

Somone has an idea of how I could group these records. They are imported from excel and I nedd to assign a marker to all lines belonging to the same item:

Item     Description   QTY  Amount  line    InvoiceID  Cell    Marker
---------------------------------------------------------------------
0        0             0    0        1       186007    B4314
CPU5000  MONITOR DIG.. 1    3035,17  2       186007    B4315   B4315
0        2 rangos 1 …  0    0        3       186007    B4316   B4315
0        0             0    0        4       186007    B4317
0        0             0    0        5       186007    B4318
70938784 ACCESORIOS ES 1    6344,53  6       186007    B4319   B4319
0        0             0    0        7       186007    B4320
PV210-P  Bomba neumá.. 1    639,54   8       186007    B4321   B4321
0        ACCESO---     0    0        9       186007    B4322   B4321
0        para WIKA     0    0       10       186007    B4323   B4321

So I need to group by description within continuous line-numbers and assign an identifyer like the cell of the item. Or to allready take the next step and summing the text in description in one string. Does sum(string) exists?

Any suggestions,
Martin

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-22 : 20:06:01

quote:
I nedd to assign a marker to all lines belonging to the same item

Can you explain the rules deciding which lines belong to the same item? Looking at your example, the rows that have been assigned the same marker do not have the same value in the Item column.

It looks to me like these are the rules, but I am guessing:

1. A new group starts if Item column has non-zero value - let us say we call such rows "anchor rows".

2. All consecutive rows that follow an anchor row are grouped with that anchor row if there is a string in the Description column that is not a zero.

Do all the rows in a group have to have the same invoice id?
Does the value in the QTY column in the anchor row have to be non-zero and non-anchor rows zero?

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-23 : 03:42:45

Yes, that is exactly the rule. All members of a group have the same InvoiceID and their Description has to be non-zero, QTY and Amount will be non-zero only for the anchor row.

I thought of a solution where I first assign to all "anchor rows" their Cell position in the column "Marker".
Then I link consecutive lines[CODE]table inner join table as self where table.line = self.line+1 and table.InvoiceID = self.InvoiceID[/CODE]and in case that two successive records have non-zero Description values, I assign the Marker of the upper line to the lower one. After some iterations all marker columns will carry the correct flag (there is a maximum of 20 lines for each Invoice). I guess this will do the job, but there must be a more elegant solution.

Martin

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 07:05:51

You can do an inner join, but you will need to do as many inner joins as there are maximum number of lines that need to be picked up for any item. If you are on SQL Server 2005 or later, a better approach perhaps is to use a recursive CTE, like shown below:

;WITH cte AS
(
	SELECT *, Cell AS Marker
	FROM YourTable
	WHERE Item <> '0' AND Qty <> 0
	
	UNION ALL
	
	SELECT
		y.*,
		c.Marker
	FROM
		YourTable y
		INNER JOIN cte c ON
			c.line + 1 = y.line
	WHERE
		c.InvoiceId = y.InvoiceId
		AND y.[Description] <> '0'
)
SELECT
	a.*,
	b.Marker
FROM
	YourTable a
	LEFT JOIN cte c ON c.line = a.line;

This perhaps can be made a little bit more efficient - for example, in the inner query, you don't really need to carry along all the columns from the original table.

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-23 : 07:39:11

You actually can get along with just a single inner join that links two successive rows; you copy the marker from the above row to the lower one. The trick is that you have to execute the command as many times as lines belong to one group. Each time the value of the anchor row is passed one row further down. It worked for me and I will try your code tonight; unfortunately I'm too busy right now to spend time in order to understand your solution... never heared of recursive CTE? ;)

update itemlines set itemlines.marker = self.marker from itemlines
INNER JOIN itemlines as self on itemlines.InvoiceID = self.InvoiceID and 
itemlines.LINE = self.LINE+1
where itemlines.item ='0' and itemlines.Description <>'0'

Anyway, thank you for your help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 07:50:31

Yes, you can do that - either manually, or in a while loop. Usually while loops tend to be less efficient than set-based queries in SQL, hence the desire to stay away from them. In your case, if the number of Item rows are only a handful, that might not be a bad approach.

CTEs (Common Table Expressions) were introduced in SQL 2005. Recursive CTE's are useful for a variety of tasks where one needs to simulate while loop type of algorithms

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources