Select help - SQL Server Forums

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

Transact-SQL (2000)

Select help

Author

Topic

shigman
Starting Member

14 Posts

Posted - 2009-06-19 : 08:34:24

Hi Everyone,

I am trying to create a report that takes a table of our customers that own equipment, then match it up to a Parts table that shows what type of parts they have purchased (Product Code). So the output displays the customer, all the machines they own and then in a grouped format the product code with total price

Example Product1 Product2 Product3
Customer A 0.00 50.00 75.00
Machine 1
Machine 2
Machine 3

So My problem is in my query (below) I keep getting dups of the machines.

Query Code:
SELECT Parts_GL_TableF.Customer, Equip_List_F.ItemDescription, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Prod_Line, SUM(Parts_GL_TableF.Qty), SUM(Parts_GL_TableF.Ext_Amnt)

FROM Parts_GL_TableF LEFT OUTER JOIN Equip_List_F ON Parts_GL_TableF.Customer = Equip_List_F.Customer

WHERE ((Parts_GL_TableF.Inv_Date>='01/01/2009' And Parts_GL_TableF.Inv_Date<=GetDate())) AND Parts_GL_TableF.Prod_Line IN ('ADPT', 'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE', 'FABR', 'FILT', 'FINL', 'GRID', 'HAZE', 'HOSE', 'HW', 'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 'NSHW', 'NSSC', 'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI', 'ROLL', 'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE', 'TESB', 'TRAC', 'TROM', 'TS', 'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 'WPPG', 'WPPR') AND Parts_GL_TableF.Customer NOT IN ('A C S')

GROUP BY Equip_List_F.ItemDescription, Parts_GL_TableF.Prod_Line, Parts_GL_TableF.Customer, Parts_GL_TableF.GL_Accnt

ORDER BY Parts_GL_TableF.Customer, Equip_List_F.ItemDescription, Parts_GL_TableF.Prod_Line, Parts_GL_TableF.GL_Accnt

Now my customers can have multiple machines, so I only want to display the distinct machine once.

Can I do this?

Any help is appreciated. Thanks Sue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-19 : 12:15:32

Drop these columns from the SELECT list and GROUP BY clause
Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Prod_Line

E 12°55'05.63"
N 56°04'39.26"

shigman
Starting Member

14 Posts

Posted - 2009-06-19 : 13:14:45

Thanks for you response!

I do need those 2 columns though....that is where my problem is

I basically need to query to sum up the Prod_lines, Qty and Ext_Amnt
then list the itemnumbers.

so the layout would be something like

Example
Prod_Line Prod_Line Prod_Line
Customer A 0.00 50.00 75.00
Machine 1
Machine 2
Machine 3
Customer B 15.00 0.00 100.00
Machine 1
Customer C 0.00 0.00 0.00
Machine 1
Machine 2
Machine 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-19 : 13:23:56

seems like what you need is subreport for showing the machine related info while main report will show Prod_lines, Qty and Ext_Amnt figures

shigman
Starting Member

14 Posts

Posted - 2009-06-19 : 13:44:20

ok. That makes sense. My goal was to use this query for a end result Pivot Table Report. If I add a sub report, can I still get the pivot table result?

Sue

shigman
Starting Member

14 Posts

Posted - 2009-06-22 : 16:05:56

Hi again

Well I was able to fix some of the query. I figured out where I was grouping it incorrectly so I was receiving duplicated rows of my prod_line.

So the code that works for me now is
SELECT Parts_GL_TableF.Customer, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Prod_Line, SUM(Parts_GL_TableF.Qty) AS QTY , SUM(Parts_GL_TableF.Unit_Cost) AS UC , SUM(Parts_GL_TableF.Ext_Cost) AS COST , SUM(Parts_GL_TableF.Unit_Price) AS UP , SUM(Parts_GL_TableF.Ext_Amnt)AS PRICE

FROM Parts_GL_TableF

WHERE ((Parts_GL_TableF.Inv_Date>='01/01/2009' And Parts_GL_TableF.Inv_Date<=GetDate())) AND Parts_GL_TableF.Prod_Line IN ('ADPT', 'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE', 'FABR', 'FILT', 'FINL', 'GRID', 'HAZE', 'HOSE', 'HW', 'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 'NSHW', 'NSSC', 'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI', 'ROLL', 'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE', 'TESB', 'TRAC', 'TROM', 'TS', 'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 'WPPG', 'WPPR') AND Parts_GL_TableF.Customer NOT IN ('A C S')

GROUP BY Parts_GL_TableF.Prod_Line, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Customer

ORDER BY Parts_GL_TableF.Customer, Parts_GL_TableF.Prod_Line

But Now I want to add/incorporate another field called Item Description from another table. Now this table can link to Parts_GL_TableF by the customer field, BUT in the new table the customer may be listed several times with different machines.

Can I incorporate that item description into this query?

I had tried the following code but the output dups my prod_line information for every machine.

Example:

Code:
SELECT Parts_GL_TableF.Customer, Equip_List_F.ItemDescription, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Prod_Line, SUM(Parts_GL_TableF.Qty) , SUM(Parts_GL_TableF.Unit_Cost) , SUM(Parts_GL_TableF.Ext_Cost) , SUM(Parts_GL_TableF.Unit_Price) , SUM(Parts_GL_TableF.Ext_Amnt)

FROM Parts_GL_TableF LEFT OUTER JOIN ED.dbo.Equip_List_F Equip_List_F ON Parts_GL_TableF.Customer = Equip_List_F.Customer

WHERE ((Parts_GL_TableF.Inv_Date>='01/01/2009' And Parts_GL_TableF.Inv_Date<=GetDate())) AND Parts_GL_TableF.Prod_Line IN ('ADPT', 'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE', 'FABR', 'FILT', 'FINL', 'GRID', 'HAZE', 'HOSE', 'HW', 'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 'NSHW', 'NSSC', 'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI', 'ROLL', 'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE', 'TESB', 'TRAC', 'TROM', 'TS', 'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 'WPPG', 'WPPR') AND Parts_GL_TableF.Customer NOT IN ('A C S')
GROUP BY Parts_GL_TableF.Prod_Line, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Customer, Equip_List_F.ItemDescription
ORDER BY Parts_GL_TableF.Customer, Equip_List_F.ItemDescription, Parts_GL_TableF.Prod_Line

Date Output Example:
ADHAN PIPING 30300-10-100-000 ADPT 11.0 9.4979 18.206 26.1800 49.2000 WARRIOR 1800 SN:12301360
ADHAN PIPING 30300-10-100-000 ADPT 11.0 9.4979 18.206 26.1800 49.2000 M85 CONVEYOR SN:6104059
ADHAN PIPING 30300-10-100-000 BEAR 2.0 39.969 79.9399 79.9399 159.88 WARRIOR 1800 SN:12301360
ADHAN PIPING 30300-10-100-000 BEAR 2.0 39.969 79.9399 79.9399 159.88 M85 CONVEYOR SN:6104059

Any help would be so appreciated. Thanks Sue

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-22 : 16:22:01

Maybe this?

SELECT Parts_GL_TableF.Customer, B.ItemDescription, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Prod_Line, SUM(Parts_GL_TableF.Qty) , SUM(Parts_GL_TableF.Unit_Cost) , SUM(Parts_GL_TableF.Ext_Cost) , SUM(Parts_GL_TableF.Unit_Price) , SUM(Parts_GL_TableF.Ext_Amnt)

FROM Parts_GL_TableF LEFT OUTER JOIN (SELECT Customer, MAX(ItemDescription) AS ItemDescription FROM ED.dbo.Equip_List_F GROUP BY Customer) B ON Parts_GL_TableF.Customer = B.Customer
WHERE ((Parts_GL_TableF.Inv_Date>='01/01/2009' And Parts_GL_TableF.Inv_Date<=GetDate())) AND Parts_GL_TableF.Prod_Line IN ('ADPT', 'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE', 'FABR', 'FILT', 'FINL', 'GRID', 'HAZE', 'HOSE', 'HW', 'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 'NSHW', 'NSSC', 'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI', 'ROLL', 'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE', 'TESB', 'TRAC', 'TROM', 'TS', 'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 'WPPG', 'WPPR') AND Parts_GL_TableF.Customer NOT IN ('A C S')
GROUP BY Parts_GL_TableF.Prod_Line, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Customer, Equip_List_F.ItemDescription
ORDER BY Parts_GL_TableF.Customer, Equip_List_F.ItemDescription, Parts_GL_TableF.Prod_Line

shigman
Starting Member

14 Posts

Posted - 2009-06-22 : 16:32:35

Oh Geez...that is so close!! But I was hoping to display each machine the customer has.

Can I do some soft of Select Distinct off the ItemDescription field OR have I already done all I can do.

Thanks so much! Sue

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-22 : 17:29:16

quote:
Originally posted by shigman

Oh Geez...that is so close!! But I was hoping to display each machine the customer has.

Can I do some soft of Select Distinct off the ItemDescription field OR have I already done all I can do.

Thanks so much! Sue

I'm not sure I understand this. So you are saying each customer can have more than one description in the Equip_List_F table? In that case, even if you select distinct values, once you join the tables, the row will repeat once for each description.

Can you provide some sample data and explain how you want the output to look like.

shigman
Starting Member

14 Posts

Posted - 2009-06-23 : 08:29:54

sure....

Here is what my output looks like now

Date Output Example:
ADHAN PIPING 30300-10-100-000 ADPT 11.0 9.4979 18.206 26.1800 49.2000 WARRIOR 1800 SN:12301360
ADHAN PIPING 30300-10-100-000 ADPT 11.0 9.4979 18.206 26.1800 49.2000 M85 CONVEYOR SN:6104059
ADHAN PIPING 30300-10-100-000 BEAR 2.0 39.969 79.9399 79.9399 159.88 WARRIOR 1800 SN:12301360
ADHAN PIPING 30300-10-100-000 BEAR 2.0 39.969 79.9399 79.9399 159.88 M85 CONVEYOR SN:6104059

Is there a way I can get the output to look like this:

Output Example:

ADHAN PIPING 30300-10-100-000 ADPT 11.0 9.4979 18.206 26.1800 49.2000 WARRIOR 1800 SN:12301360
BEAR 2.0 39.969 79.939 79.9399 159.88 M85 CONVEYOR SN:6104059

So I can pull the data into Excel or a Pivot Table looking something like this:
Customer ItemDescription # Product Line

ADHAN PIPING WARRIOR 1800 SN:12301360 30300-10-100-000 ADPT BEAR
M85 CONVEYOR SN:6104059 11.0 9.4979 18.206 26.1800 49.20 2.0 39.969 79.939 79.9399 159.88

shigman
Starting Member

14 Posts

Posted - 2009-06-23 : 11:30:42

another data example when I paste the data into Excel using a Pivot Table is;

ADHAN PIPING M85 CONVEYOR SN:6104059 49.2 159.88 135
WARRIOR 1800 SN:12301360 49.2 159.88 135
ADHAN PIPING Total 98.4 319.76 270

shigman
Starting Member

14 Posts

Posted - 2009-06-23 : 11:47:32

ok..I think I got it.

I went to my pivot table and changed the field settings on SUM(Parts_GL_TableF.Ext_Amnt)

from a SUM to a MAX. So the query SUMS the field but the Pivot Table now shows the MAX,

see below:

ADHAN PIPING M85 CONVEYOR SN:6104059 49.2 159.88
WARRIOR 1800 SN:1230136049.2 159.88
ADHAN PIPING Total 49.2 159.88

do you think I am on the right track? I just don't see anyother way of doing it?

Sue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources