Which approach for parent child

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

SQL Server 2000 Forums

SQL Server Development (2000)

Which approach for parent child

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-02-03 : 20:08:12

Hi,

I don't know how to attack this problem.
I have written a somewhat slow query that
compares quoting and purchasing. There are
groups involved, item masters, dissimilar
group associations. All kinds of crazy stuff,
The guys quote generally and from a different
structure than the actual purchases.
I managed to get this output sweet in a foxpro report
buy using conditional variables and another layer of grouping
etc. etc.

Anyways here is a model
of the data where SO is salesorder,
group is purchasing group,
flag J=actual, Q=Quote

SO---xgroup--Flag--item-----itemext---grouptotal-----total---- groupvariance
1A---blue----J----screws------240------500---------700----_ -100
1A---blue----J----bolts--------260------500---------700----_ -100
1A---red-----J----steel--------200------200---------700----_ 50
1A---blue----Q----fasteners---400------400---------650----_ -100
1A---red-----Q----hardsteel---175------250---------650----_ 50
1A---red-----Q----softsteel-----75------250---------650----_ 50

So what do we have here

All these are for Sales Order 1A

SO=1A Flag=J represents a materials(BOM) total of 700
SO=1A Flag=Q represents a quote total of 650
(a total variance or -50)

The actual materials order within the blue group is 500
The quoted materials for the blue group is 400

The actual materials order within the red group is 200
The quoted materials for the red group is 250

the difference between the quote and actual is -50
the difference between the quote and actual along the blue group is -100
the difference between the actual and quote along the red group is 50

So I attempted datashaping but all the examples showed direct
select statements. And this was a little different maybe because
the return set is complete as is. It just dosen't have a form that translates well to an accounting style layout.
Well I dug some more and was able to use procs but it really was tough getting four deep shaping syntax on basically a single table. Got slow too, I think I screwed it up, could have done something wrong? But when I think about what would be asked of the datashape driver It kind of seems that it would be slow no matter what.

Anyways

this is the output I need
on a web page

SalesOrder---Group------Actual Item-------Quoted Item------group variance

1A
-------------blue
------------------------screws
------------------------bolt
-------------------------------------------fasteners
------------------------------------------------------------- -100
-------------red
------------------------steel
-------------------------------------------hardsteel
-------------------------------------------softsteel
------------------------------------------------------------- 50

So what approach should I take.
I'm under the impression that many would work, actually
did some before but not as complicated. I have spent most
effort on this in traditional asp because things seemed to run fastest
but I get some freaked out parent child conditions depending on the
inclusion or exclusions of different groups.

Thanks

Voted best SQL forum nickname...."Tutorial-D"

CREATE TABLE #table
(
SO varchar(6),
xgroup varchar(6),
Flag char(1),
item varchar(32),
itemext money,
grouptotal money,
total money,
groupvariance money
)

INSERT INTO #table VALUES ('1A','blue','J','screws',240,500,700,-100)
INSERT INTO #table VALUES ('1A','blue','J','bolts',260,500,700,-100)
INSERT INTO #table VALUES ('1A','red','J','steel',200,200,700,50)
INSERT INTO #table VALUES ('1A','blue','Q','fasteners',400,400,650,-100)
INSERT INTO #table VALUES ('1A','red','Q','hardsteel',175,250,650,50)
INSERT INTO #table VALUES ('1A','red','Q','softsteel',75,250,650,50)

--SELECT * FROM #table

--DROP TABLE #table

Edited by - sitka on 02/03/2003 21:54:08

Tim
Starting Member

392 Posts

Posted - 2003-02-03 : 20:29:09

but where's the code to create tables and sample data ?

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-02-03 : 20:32:34

Edited by - sitka on 02/03/2003 21:15:58

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-02-08 : 09:49:53

This isn't forgotten but I have bailed on it by rewriting some stuff
and eliminating the "item level" because as it turns out any reporting
containing the item level is too huge to really paint the proper picture anyway. Thus the reporting is kind self limiting in this case
because the scope of detail becomes too big to be practical.
But, I am still curious if someone has any input on what exactly this (and the above table) is, and how one would go about generating the output.

SalesOrder---Group------Actual Item-------Quoted Item------group variance

1A
-------------blue
------------------------screws
------------------------bolt
-------------------------------------------fasteners
------------------------------------------------------------- -100
-------------red
------------------------steel
-------------------------------------------hardsteel
-------------------------------------------softsteel
------------------------------------------------------------- 50

For instance is it the same as a Threaded discussion, organizational boss/bigboss etc. I would still like at one point to develop the skills to utilize those neat "drop below" dynamic expandable tree web layouts at one point but I'm under the impression that a person kind of meets at a confluence of technologies at that point. Whereas an ado feature like shape (or packaged web components) quickly becomes ungainly for development accross multiple aggregated queries (think accounting reconciliations/costing) and the relational representation of hierarchies can get a little heady at times and is a hotly discussed topic here. I think what I'm really asking is for a gentle push towards the correct path to identify and tackle the goal of shaping the returned results once that result set is basically complete in the traditional sense of the word.
The data is not my design it is a proprietory ERP system and thus unchangeable. A query against an item will have an extremely convoluted relationship to a payment for example.
Here is a dark peek at a thought process.
Item belongs to subjob belongs to job belongs to sales order while at the same time the item belongs to a purchasing queue which references a purchase order which is vendor dependent which is then "received against" which auto generates an AP item that can be chosen in whole or in part once the invoice from the vendor comes in and a cash payment can be made.
Is there a balance remaining to be paid on the item?.. good question.
And one that I to this point have found best left to the efficiencies of the query analyser.

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources