simple report question, i think 2

[soutener]

i have three views for my data, one is quantity shipped, the other is quantity returned and the third is net

i need to see the data like this:

ITEM,ATTRIBUTE,QTYSHIP,QTYRETURNED,NETQTY

but i cant seem to get accurate numbers out of them, i've just been joining everything to my net qty field to display the rest of the data
what am i doing wrong? or how can i go about getting that info in the format above, note that attribute is needed because it is the color and i need to see the report down to the color level

thanks guys!

 

[AlexCuse]

I think your join criteria are messed up. Can you post your query?

My guess is it should be

SELECT a.ITEM, a.ATTRIBUTE, b.QTYSHIP, c.QTYRETURNED, a.NETQTY
FROM [b]NETTABLE[/b] a inner join [b]SHIPTABLE[/b] b ON
a.ITEM = b.ITEM
a.ATTRIBUTE = b.ATTRIBUTE
inner join [b]RETURNTABLE[/b] c ON
a.ITEM = c.ITEM
a.ATTRIBUTE = c.ATTRIBUTE

Tablenames are in bold, they will need to be changed to reflect your actual names.

Hope this helps, and if not please post your query.

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

I forgot the AND's, here is corrected code

SELECT a.ITEM, a.ATTRIBUTE, b.QTYSHIP, c.QTYRETURNED, a.NETQTY
FROM [b]NETTABLE[/b] a inner join [b]SHIPTABLE[/b] b ON
a.ITEM = b.ITEM [b] AND [/b]
a.ATTRIBUTE = b.ATTRIBUTE
inner join [b]RETURNTABLE[/b] c ON
a.ITEM = c.ITEM [b] AND [/b]
a.ATTRIBUTE = c.ATTRIBUTE

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[soutener]

no dice. if i just query "NETSHIP" in the NET Table, i get 1687 Records for a total QTY of 68160
in my joined table, i get 1500 records with a total QTY of 61008

anyone have this issue before?
is it because some items may not have been returned so they dont show on the report?

 

[AlexCuse]

Sounds like you have missing item/attribute pairings on one of your tables. You will need to find which table has the expected quantity and number of products, and do a outer join from that rather than an inner join. If NET table has everything you want to see, this could be your answer

SELECT a.ITEM, a.ATTRIBUTE, b.QTYSHIP, c.QTYRETURNED, a.NETQTY
FROM NETTABLE a [b]left[/b] join SHIPTABLE b ON
a.ITEM = b.ITEM AND 
a.ATTRIBUTE = b.ATTRIBUTE
[b]left[/b] join RETURNTABLE c ON
a.ITEM = c.ITEM AND 
a.ATTRIBUTE = c.ATTRIBUTE

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[soutener]

i'll see if that works, and net does have all my records, i created return and shipped tables from net.

thanks again!

p.s. this is my first attempt @ sql and everyone @ tek-tips has been a great help!

 

[AlexCuse]

Let me know how it works for you, and welcome to the wonderful world of SQL server. You will find a lot of really smart people on here to help you out (myself excluded).

For future reference, use inner join if you want to show only records that are present in both tables.

Left join will give you all records on the table to the left of the join statement (in this case NET), including null values as placeholders where there is not a match on your join criteria. Right join will do the same only starting from the table on the right (I don't think I've ever used right join, I just switch them and do left).

Good Luck,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[soutener]

awsome!
it worked!!!, lil' more complex, but your description helped me figure it out.




SELECT dbo.ConsignVbaseF.STY_ROYL, dbo.ConsignVbaseF.IVD_STYLE, dbo.ConsignVbaseF.IVD_COLOR, dbo.ConsignVbaseF.CUST_TYP,
dbo.ConsignShi.SHIPQTY, dbo.ConsignShi.SHIPDOL, dbo.ConsignRet.RETQTY, dbo.ConsignRet.RETDOL, dbo.ConsignNet.NETQTY,
dbo.ConsignNet.NETDOL
FROM dbo.ConsignVbaseF LEFT OUTER JOIN
dbo.ConsignNet ON dbo.ConsignVbaseF.CUST_TYP = dbo.ConsignNet.CUST_TYP AND
dbo.ConsignVbaseF.IVD_STYLE = dbo.ConsignNet.IVD_STYLE AND dbo.ConsignVbaseF.IVD_COLOR = dbo.ConsignNet.IVD_COLOR LEFT OUTER JOIN
dbo.ConsignShi ON dbo.ConsignVbaseF.CUST_TYP = dbo.ConsignShi.CUST_TYP AND
dbo.ConsignVbaseF.IVD_COLOR = dbo.ConsignShi.IVD_COLOR AND dbo.ConsignVbaseF.IVD_STYLE = dbo.ConsignShi.IVD_STYLE LEFT OUTER JOIN
dbo.ConsignRet ON dbo.ConsignVbaseF.CUST_TYP = dbo.ConsignRet.CUST_TYP AND
dbo.ConsignVbaseF.IVD_STYLE = dbo.ConsignRet.IVD_STYLE AND dbo.ConsignVbaseF.IVD_COLOR = dbo.ConsignRet.IVD_COLOR

 

[soutener]

oh, and my next question is, how can i calculate qty sold against $ sold to get an average selling price?
thanks again!

 

[soutener]

LOL! one more
UPDATE NOFEAR.dbo.ConsignTblMaster SET RETDOL='0' WHERE RETDOL=NULL;

that doesnt replace my nulls w/ zero's, what am i do'n wrong?

 

[jbenson001]

UPDATE NOFEAR.dbo.ConsignTblMaster SET RETDOL='0' WHERE RETDOL IS NULL;

 

[MaffewW]

does this work for you avg?

SELECT dbo.ConsignVbaseF.STY_ROYL, dbo.ConsignVbaseF.IVD_STYLE, dbo.ConsignVbaseF.IVD_COLOR, dbo.ConsignVbaseF.CUST_TYP,
dbo.ConsignShi.SHIPQTY, dbo.ConsignShi.SHIPDOL, dbo.ConsignRet.RETQTY, dbo.ConsignRet.RETDOL, dbo.ConsignNet.NETQTY,
dbo.ConsignNet.NETDOL,avg(dbo.ConsignNet.NETQTY*dbo.ConsignNet.NETDOL)
FROM dbo.ConsignVbaseF LEFT OUTER JOIN
dbo.ConsignNet ON dbo.ConsignVbaseF.CUST_TYP = dbo.ConsignNet.CUST_TYP AND
dbo.ConsignVbaseF.IVD_STYLE = dbo.ConsignNet.IVD_STYLE AND dbo.ConsignVbaseF.IVD_COLOR = dbo.ConsignNet.IVD_COLOR LEFT OUTER JOIN
dbo.ConsignShi ON dbo.ConsignVbaseF.CUST_TYP = dbo.ConsignShi.CUST_TYP AND
dbo.ConsignVbaseF.IVD_COLOR = dbo.ConsignShi.IVD_COLOR AND dbo.ConsignVbaseF.IVD_STYLE = dbo.ConsignShi.IVD_STYLE LEFT OUTER JOIN
dbo.ConsignRet ON dbo.ConsignVbaseF.CUST_TYP = dbo.ConsignRet.CUST_TYP AND
dbo.ConsignVbaseF.IVD_STYLE = dbo.ConsignRet.IVD_STYLE AND dbo.ConsignVbaseF.IVD_COLOR = dbo.ConsignRet.IVD_COLOR
group by dbo.ConsignVbaseF.STY_ROYL, dbo.ConsignVbaseF.IVD_STYLE, dbo.ConsignVbaseF.IVD_COLOR, dbo.ConsignVbaseF.CUST_TYP,
dbo.ConsignShi.SHIPQTY, dbo.ConsignShi.SHIPDOL, dbo.ConsignRet.RETQTY, dbo.ConsignRet.RETDOL, dbo.ConsignNet.NETQTY,
dbo.ConsignNet.NETDOL

Matt

Brighton, UK

 

[soutener]

Divide by zero error encountered."

i get above error when i try this code...

UPDATE SET AVGSL=NETDOL/NETQTY

 

[soutener]

LOL, OK, im going to start a new thread, i've asked enough on this one, thanks everyone for your help!