Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[TheBoy]

Sorry, just can't get my head around this, and daft timescales again 


3 tables - one is a products table, one a location table, other a stock location table

Products:
ProductID - unique ID
ProductDescription - Product description

Stock:
StockID - unique ID
LocationID - Linked to a locations
ProductID - Linked to product
NumberInLocation - Number in this location

Location:
LocationID - Unique ID
LocationDescription - Name of location


I need to provide a table of:
ProductDescription, Number at Location ID1,Number at Location ID2,Number at Location IDn

[cem_devecioglu]

was heavy on wine  , now solving..

[redelitev6]

Sorry, just can't get my head around this, and daft timescales again 


3 tables - one is a products table, one a location table, other a stock location table

Products:
ProductID - unique ID
ProductDescription - Product description

Stock:
StockID - unique ID
LocationID - Linked to a locations
ProductID - Linked to product
NumberInLocation - Number in this location

Location:
LocationID - Unique ID
LocationDescription - Name of location


I need to provide a table of:
ProductDescription, Number at Location ID1,Number at Location ID2,Number at Location IDn


[ / my head hurts

[TheBoy]

was heavy on wine  , now solving..

Cheers

BTW, has to be SQL, not SPs...

[cem_devecioglu]

assuming all inner join and data existent for stock table at location table and you are using sql server


select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l

where p.pid=s.spid and s.slid=l.llid


OR

you can also drop to 2 tables if you need fewer fields as you stated

select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s , erase the comma
where p.pid=s.spid

after you need to transpose the result on product description either in excel or using some code..


 I can send you a generic .net vb code that I have written for transposition if you need..

reason is for the required transposition you need mdx code for olap which is a bit more complicated and instead I use vb.net..

[redelitev6]

assuming all inner join and data existent for stock table at location table and you are using sql server


select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l

where p.pid=s.spid and s.slid=l.llid


OR

you can also drop to 2 tables if you need fewer fields as you stated

select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
where p.pid=s.spid

after you need to transpose the result on product description either in excel or using some code..


 I can send you a generic .net vb code that I have written for transposition if you need..

reason is for the required transposition you need mdx code for olap

  i was just going to say that!

[TheBoy]

assuming all inner join and data existent for stock table at location table and you are using sql server


select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l

where p.pid=s.spid and s.slid=l.llid


OR

you can also drop to 2 tables if you need fewer fields as you stated

select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
where p.pid=s.spid

after you need to transpose the result on product description either in excel or using some code..


 I can send you a generic .net vb code that I have written for transposition if you need..

reason is for the required transposition you need mdx code for olap

If an item does NOT exist at a location, it *may* either have a 0 in Numberatlocation, or its possible the record will not exist.

[cem_devecioglu]

assuming all inner join and data existent for stock table at location table and you are using sql server


select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l

where p.pid=s.spid and s.slid=l.llid


OR

you can also drop to 2 tables if you need fewer fields as you stated

select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
where p.pid=s.spid

after you need to transpose the result on product description either in excel or using some code..


 I can send you a generic .net vb code that I have written for transposition if you need..

reason is for the required transposition you need mdx code for olap

If an item does NOT exist at a location, it *may* either have a 0 in Numberatlocation, or its possible the record will not exist.

select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid

I hope this will be helpful

[TheBoy]

No OLAP - this has to work in Access/Jet as well as MS-SQL.

Its a VB (VB6  - its old, and the .Net converter says NO!) app. Basically need to fill a (read only) Flexgrid:

Description    Loc1   Loc2   Loc3   Loc4.... ....Loc X
Item1               0        0        1          1               0
Item2               1        4        2          0               0



Currently, I'm populating the Description (and price and other stuff that is unimportant at this point), then repeatedly going back to the DB to populate each Location - this is particularly unkind to the network, and especially to the DB server

[cem_devecioglu]

No OLAP - this has to work in Access/Jet as well as MS-SQL.

Its a VB (VB6  - its old, and the .Net converter says NO!) app. Basically need to fill a (read only) Flexgrid:

Description    Loc1   Loc2   Loc3   Loc4.... ....Loc X
Item1               0        0        1          1               0
Item2               1        4        2          0               0



Currently, I'm populating the Description (and price and other stuff that is unimportant at this point), then repeatedly going back to the DB to populate each Location - this is particularly unkind to the network, and especially to the DB server

ok then.. will check my code.. it can be converted to vb6 simply me thinks.. need some time..

[sexydaz]

the record will exist but and its a big but it will be transposed too far down the numeric synergy to be recogniseable as even a distinct binary code,olap the uplink and restat the product address and then i do believe a restart will sort you out

[TheBoy]

the record will exist but and its a big but it will be transposed too far down the numeric synergy to be recogniseable as even a distinct binary code,olap the uplink and restat the product address and then i do believe a restart will sort you out

That makes more sense than the mgmt rubbish I have to listen to all day long

[cem_devecioglu]

initial step

for x -axis

(will not stress the network as it will work locally on server and only return the y-axis values for numberinlocation)

select distinct NumberinLocation from (
select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid ) as x order by ..

now process the results from left to right in a grid with a loop.. or better place in a single dimension array..

edit : forgot that one and corrected the axis

for y-axis


select distinct ProductDescription from (
select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid ) as x order by ..

this also on a single dimension array

[cem_devecioglu]

process the main query once  and assign into a 2d array (matrix)

txt="select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid "

read from database query txt in a loop like

txt = "select ............."
Set rs = New ADODB.Recordset
rs.Open txt, adoconnection 'that you define and connect through
 
 index=0
 While rs.EOF = False   ' rs is the recordset
 index=index+1
  arry(index,1)=  rs!ProductDescription
  arry(index,2)= rs!NumberinLocation

  rs.MoveNext

  Wend

[cem_devecioglu]

now iterate through the matrix for every item and Numberinlocation and write the results in the grid
so you will need no network access..

for .....
   for ...

   next
 next ..

etc..

I used this technique of buffering and using arrays in a bank that had more than thousand branches for calculating daily sums and it was unbelievably fast and practical..
 
and faster than olap (or I'm bad in olap )

sorry cant give the actual vb6 code as the .net code was fairly long and it was generic on any type of table with any kind of fields..