[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Seeking a clever in-query SQL solution to sequences

kgerritsen

3/23/2007 3:33:00 PM

Hello,

I have an output from a legacy logistics application that I want to
reconcile with a related table in my SQL Server (update destination
and postage with new values after the logistics app runs). My
granular entity is a tray (or container), which is N:1 to a
manufacturing cell, which is N:1 to a job. A tray may be N:1 to a
parent container (ie, a pallet) or remain unassigned to a pallet;
Pallets are always N:1 with cells. Pallets are modelled differently
between the legacy application and the SQL system: the legacy
application assigns container ids to both pallets and trays from the
same sequence. The SQL system assigns separate pallet and tray
sequence numbers.

Constraints: Both the legacy application and the table I'm supporting
assign a container sequence number (starting from 1), counting within
job (ie, the tray is a weak entity to job). The legacy application
also has a slight diffence in modelling: it assigns parent containers
(pallets) from the same sequence as trays; the tray table does not
sequence parent container . Exception events (tray spoilage, mfg
reruns) can cause gaps in the sequence numbers in both tables.

The biggest issue: in some cases, the legacy application processes
cells in a different order.

So, I cannot simply match a tray to the combination of FKs JobNumber,
CellNumber, ContainerID.

Fortunately, I have determined that tray order within cells always
remains the same before and after the legacy process. Pallet order
should behave similarly between the two applications, but I haven't
confirmed this 100% yet.

If I load both tables into new tables with an identity column, as long
as my insertion orders by Job, Cell, and container ID, I should be
able to derive a new containerID field that will exactly match my
trays between these outputs. I'd rather not do this, because this
would force me to process job-by-job, instead of all sets at once, and
introduce new data and processing time overheads; but if I have to, I
will. Instead of duplicating my data into working tables (with
sequences I'd be resetting every process), I'd rather set up an update
query that doesn't require this intermediate step. Is it possible to
define a sequence or identity in a view?

Sample data (both tables ordered by ContainerID), with arbitrary gaps
in sequence numbers. Updates to both Postage and Destination.

Initial "Tray" table in SQL:
JobNumber CellNumber ContainerID ParentID Postage
Destination
SW1 001 1 1
null Philadelphia
SW1 001 3 1
null Philadelphia
SW1 001 4 2
null Princeton
SW1 002 5 3
null Baltimore
SW1 002 6 4
null Annapolis
SW1 003 10 5
null NYC
SW1 003 11 5
null NYC
SW1 003 12 6
null NYC

Legacy Output:
JobNumber CellNumber ContainerID ParentID Postage
Destination
SW1 002 2 1
20.20 Baltimore
SW1 002 4 3
15.45 Baltimore
SW1 001 6 5
20.20 Philadelphia
SW1 001 7 5
20.20 Philadelphia
SW1 001 10 9
18.5 Trenton
SW1 003 13 12
20.20 NYC
SW1 003 14 12
20.20 NYC
SW1 003 17 16
17.00 NYC

Desired output
JobNumber CellNumber ContainerID ParentID Postage
Destination
SW1 001 1 1
20.20 Philadelphia
SW1 001 3 1
20.20 Philadelphia
SW1 001 4 2
18.5 Trenton
SW1 002 5 3
20.20 Baltimore
SW1 002 6 4
15.45 Baltimore
SW1 003 10 5
20.20 NYC
SW1 003 11 5
20.20 NYC
SW1 003 12 6
17.00 NYC

Can anyone point out a clever in-update or in-join sequencing
statement for me?

Regards,
Keith
2 Answers

Hugo Kornelis

3/25/2007 11:57:00 AM

0

On 23 Mar 2007 08:33:11 -0700, kgerritsen wrote:

>Hello,
(snip)
>Can anyone point out a clever in-update or in-join sequencing
>statement for me?

Hi Keith,

Probably, but only if I understand *HOW* the initial tray and the legacy
output shoould be transformed to the desired output. Neither the
explanation, nor the sample data, has helped me her.

Can you please post the following:

1. The structure of all relevant tables, posted as CREATE TABLE
statements. Make sure to include all constraints, indexes and
properties, as they might be relevant to the answer. If a table has lots
of columns that are not relevant to the question, you can omit them.

2. A few well-chosen rows of sample data to illustrate the problem. Try
to include special cases in the sample data, but try to keep it compact
as well (don't post hundreds of rows!!). Becuase nobody here likes
typing, make sure to post the sample data as INSERT statements, so that
we can use copy and paste to recreate the test data on our side.

3. The expected output from the sample data you posted. You can post
this in a tabular format. (For readability, use spaces rather than the
TAB character and make sure that the colums are properly aligned when
displayed in a non-proportiaonal font).

4. An explanation that details how each row in the desired output is
formed, which rows from the input table(s) were processed to create it
and what transformation were applied to the data to get from that input
to the desired output.

More information, and some hints on how to assemble the CREATE TABLE and
INSERT statements with minimal effort, are on www.aspfaq.com/5006.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

David Portas

3/25/2007 2:33:00 PM

0

On 23 Mar, 16:33, "kgerritsen" <k...@drexel.edu> wrote:
> Hello,
>
> I have an output from a legacy logistics application that I want to
> reconcile with a related table in my SQL Server (update destination
> and postage with new values after the logistics app runs). My
> granular entity is a tray (or container), which is N:1 to a
> manufacturing cell, which is N:1 to a job. A tray may be N:1 to a
> parent container (ie, a pallet) or remain unassigned to a pallet;
> Pallets are always N:1 with cells. Pallets are modelled differently
> between the legacy application and the SQL system: the legacy
> application assigns container ids to both pallets and trays from the
> same sequence. The SQL system assigns separate pallet and tray
> sequence numbers.
>
> Constraints: Both the legacy application and the table I'm supporting
> assign a container sequence number (starting from 1), counting within
> job (ie, the tray is a weak entity to job). The legacy application
> also has a slight diffence in modelling: it assigns parent containers
> (pallets) from the same sequence as trays; the tray table does not
> sequence parent container . Exception events (tray spoilage, mfg
> reruns) can cause gaps in the sequence numbers in both tables.
>
> The biggest issue: in some cases, the legacy application processes
> cells in a different order.
>
> So, I cannot simply match a tray to the combination of FKs JobNumber,
> CellNumber, ContainerID.
>
> Fortunately, I have determined that tray order within cells always
> remains the same before and after the legacy process. Pallet order
> should behave similarly between the two applications, but I haven't
> confirmed this 100% yet.
>
> If I load both tables into new tables with an identity column, as long
> as my insertion orders by Job, Cell, and container ID, I should be
> able to derive a new containerID field that will exactly match my
> trays between these outputs. I'd rather not do this, because this
> would force me to process job-by-job, instead of all sets at once, and
> introduce new data and processing time overheads; but if I have to, I
> will. Instead of duplicating my data into working tables (with
> sequences I'd be resetting every process), I'd rather set up an update
> query that doesn't require this intermediate step. Is it possible to
> define a sequence or identity in a view?
>
> Sample data (both tables ordered by ContainerID), with arbitrary gaps
> in sequence numbers. Updates to both Postage and Destination.
>
> Initial "Tray" table in SQL:
> JobNumber CellNumber ContainerID ParentID Postage
> Destination
> SW1 001 1 1
> null Philadelphia
> SW1 001 3 1
> null Philadelphia
> SW1 001 4 2
> null Princeton
> SW1 002 5 3
> null Baltimore
> SW1 002 6 4
> null Annapolis
> SW1 003 10 5
> null NYC
> SW1 003 11 5
> null NYC
> SW1 003 12 6
> null NYC
>
> Legacy Output:
> JobNumber CellNumber ContainerID ParentID Postage
> Destination
> SW1 002 2 1
> 20.20 Baltimore
> SW1 002 4 3
> 15.45 Baltimore
> SW1 001 6 5
> 20.20 Philadelphia
> SW1 001 7 5
> 20.20 Philadelphia
> SW1 001 10 9
> 18.5 Trenton
> SW1 003 13 12
> 20.20 NYC
> SW1 003 14 12
> 20.20 NYC
> SW1 003 17 16
> 17.00 NYC
>
> Desired output
> JobNumber CellNumber ContainerID ParentID Postage
> Destination
> SW1 001 1 1
> 20.20 Philadelphia
> SW1 001 3 1
> 20.20 Philadelphia
> SW1 001 4 2
> 18.5 Trenton
> SW1 002 5 3
> 20.20 Baltimore
> SW1 002 6 4
> 15.45 Baltimore
> SW1 003 10 5
> 20.20 NYC
> SW1 003 11 5
> 20.20 NYC
> SW1 003 12 6
> 17.00 NYC
>
> Can anyone point out a clever in-update or in-join sequencing
> statement for me?
>
> Regards,
> Keith

Your spec is much too cryptic for me to be sure of giving a good
answer. Please read my signature.

What I would suggest is that you take a look at the Window functions:
ROW_NUMBER(), RANK(), DENSE_RANK() in Books Online.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--