Post reply

Need Help

  • October 21, 2013 at 1:02 pm

    #300300

    I have 2 tables, Parts & WOParts. Parts keeps a comprehensive list of all parts in all warehouses, WOParts are all parts that have been on work orders. I am trying to make a query that a report can use, which will tell how many parts have been used in a selected time frame, and also identify parts that are not on WO's in that time frame. Because the data was bad to begin with, you will notice some things in the code I have so far that are unconventional. I am untrained in T-SQL so any improvements on my current code are also welcome.

    Crucial or Useful Columns in each Table

    Parts: p

    Part Number -[PartNo] (PK, Not Null)

    Warehouse - [Warehouse] (PK, Not Null)

    Master SKU - [MasterPartNo]

    Description - [Description]

    Cust. Part Number - [PartNoAlias]

    Bin Location - [Bin]

    Part Cost - [Cost]

    Part Price - [List]

    Stock Qty. - [OnHand]

    Alt. Number - [PartNoOriginal]

    Alt. Number - [OldNumber]

    WOParts: w

    Order Number - [WONo] (PK, Not Null)

    Order Date - [EntryDate] (PK, Not Null)

    Cust. ID - [BillTo]

    Part Number - [PartNo]

    Cust. Part Number - [PartNoAlias]

    Amount Sold - [Qty]

    The code below returns to me all parts in the warehouse and then dates in which they were ordered since the date i specified:

    SELECT p.Warehouse, p.PartNo, p.PartNoAlias, p.PartNoOriginal, p.OldNUmber, p.description, p.Bin, p.onhand, p.Cost, p.List, w.EntryDate, w.Qty

    FROM (SELECT *

    FROM woparts

    WHERE Left(billto,1)='t' and EntryDate > '2012-10-16' and WONo<910000000) w

    right join

    (SELECT *

    FROM parts

    WHERE Left(Warehouse,1)='t' and OnHand > 0) p

    ON (p.partno=w.partno or w.PartNo = p.PartNoOriginal or w.partno = p.PartNoAlias or w.PartNo = p.OldNUmber or w.PartNoAlias=p.PartNoAlias) and p.Warehouse= left (w.BillTo,5)

    with what i have returned, If there were multiple orders on a certain part in a warehouse, i will show up multiple times on this list. What I need help with is to modify this to show, only 1 row of each part number per warehouse (similar to the Parts PK). and show the number of orders placed for that part ( a unique count of {P.PartNo, P.Warehouse}), a sum of Qty sold in that date range ( Sum of {w.Qty} per distinct {p.PartNo,P.Warehouse}), and the last entry sale date of the part ( max{EntryDate} per distinct {P.PartNumber,P.Warehouse}).

    It sounds like a big task, but for someone who knows this T-SQL well, it shouldn't be that difficult. I just hope I did well explaining, Please ask any questions if you are confused about what I am looking for, I have been trying to figure this out for a while and am not knowledgeable enough about code.

    Thank you very much to whoever helps me!!

  • October 21, 2013 at 1:04 pm

    #1660248

    Hi and welcome to the forums. In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 21, 2013 at 1:07 pm

    #1660250

    Please provide the actual code to create the tables and some sample data inserts too. After that we should be able to help you with the query.

    At first glance it looks like you just need a group by statement. Here is a link on what a group by clause does:

    http://bit.ly/pvIbr


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • October 21, 2013 at 1:07 pm

    #1660251

    thank you, i will create those test tables now

  • October 21, 2013 at 1:34 pm

    #1660268

    when trying to export my test data, I am using the procedures from your signiture line, yet i am recieving some of the values as null, instead of the select statement, what could be causing this?

  • October 21, 2013 at 1:41 pm

    #1660271

    derekmcdonnell3 (10/21/2013)

    when trying to export my test data, I am using the procedures from your signiture line, yet i am recieving some of the values as null, instead of the select statement, what could be causing this?

    Maybe you have some NULL values in your data? It is kind of hard to know for sure. It doesn't have to be perfect, we can work with it a little bit to help you get it figured out. Or you can just manually modify the inserts if you need to. You don't need to create a lot of rows, just enough to demonstrate the scope of the situation which for your case might be as few as 5 - 10 rows or so.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 21, 2013 at 1:46 pm

    #1660274

    OK so here are tables and sample data

    Parts:

    /****** Object: Table [dbo].[Parts] Script Date: 10/21/2013 15:10:45 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Parts](

    [PartNo] [varchar](50) NOT NULL,

    [Warehouse] [varchar](50) NOT NULL,

    [MasterPartNo] [nvarchar](50) NULL,

    [Description] [nvarchar](50) NULL,

    [PartNoAlias] [nvarchar](50) NULL,

    [Bin] [nvarchar](50) NULL,

    [Cost] [money] NULL,

    [List] [money] NULL,

    [OnHand] [float] NULL,

    [OldNUmber] [varchar](50) NULL,

    [PartNoOriginal] [varchar](50) NULL,

    PRIMARY KEY CLUSTERED

    (

    [PartNo] ASC,

    [Warehouse] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[Parts] ADD DEFAULT ('') FOR [UnitDescription]

    GO

    Parts sample Data:

    SELECT '12X41/2X8EZ','T1188','RA1060703101','EZ DRIVE TIRE','RA1060703101','Consignment',76.40,104.40,'2','12X41/2X8EZ','RA1060703101' UNION ALL

    SELECT '906','T1188','RA590724','350 GRAY ANDERSON','RA590-724','Consignment',2.54,3.50,'10','RA590724','RA590724' UNION ALL

    SELECT '940','T1188','RA590701','175 GRAY HOUSING','RA590-701','Consignment',1.49,2.25,'10','RA590701','RA590701' UNION ALL

    SELECT '943','T1188','','175 YELLOW HSG','RA590-715','Consignment',1.42,2.50,'4','RA590715','RA590715' UNION ALL

    SELECT 'CL664135','T1188','','BEARING','BG6206-2RS','Consignment',1.23,4.00,'6','BG62062RS','BG62062RS' UNION ALL

    SELECT 'CL994579','T1188','','FILTER - OIL','RA520-509-01','Consignment',4.25,5.95,'1','RA52050901','RA52050901' UNION ALL

    SELECT 'CR100462-306','T1188','','XL POLY WHEEL','CR100462-306','Consignment',14.72,22.00,'10','PWS9141XL','PWS9141XL' UNION ALL

    SELECT 'CR100463','T1188','CR100463 ','SPACER','CR100463','Consignment',0.55,0.90,'20','CR100463','CR100463' UNION ALL

    SELECT 'CR115032-306','T1188','','XL POLY WHEEL','CR115032-306','Consignment',23.13,35.10,'6','CR115032306','CR115032306' UNION ALL

    SELECT 'CR115418-1','T1188','CR115418-1 ','SWITCH POD ASSEMBLY','CR115418S001','Consignment',75.00,105.00,'1','CR115418-1','CR115418S001' UNION ALL

    SELECT 'CR115496','T1188','CR115496','AXLE','CR115496','Consignment',9.50,14.25,'1','CR115496','CR115496' UNION ALL

    SELECT 'CR115509','T1188','CR115509','AXLE','CR115509','Consignment',11.00,16.50,'1','CR115509','CR115509' UNION ALL

    SELECT 'CR115515','T1188','CR115515 ','AXLE','CR115515','Consignment',7.55,15.00,'1','CR115515','CR115515' UNION ALL

    SELECT 'CR115518','T1188','CR115518','AXLE','CR115518','Consignment',18.60,25.49,'1','CR115518','CR115518' UNION ALL

    SELECT 'CR115521','T1188','CR115521','SHAFT','CR115521','Consignment',14.89,20.10,'1','CR115521','CR115521' UNION ALL

    SELECT 'CR125237-19','T1188','','TENSION BAR','CR125237-19','Consignment',125.00,216.18,'1','CR12523719','CR12523719' UNION ALL

    SELECT 'CR125237-20','T1188','','TENSION BAR','CR125237-20','Consignment',117.00,233.50,'1','CR12523720','CR12523720' UNION ALL

    SELECT 'CR127723-25','T1188','','XL POLY WHEEL','CR123240-306','Consignment',48.91,68.00,'1','CR123240306','CR123240306' UNION ALL

    SELECT 'CR65081-6','T1188','','BEARING','BG6306-2RS','Consignment',3.50,4.50,'4','BG63062RS','BG63062RS' UNION ALL

    SELECT 'CR65081-70','T1188','85052RS','2 RUBBER SHIELD BEARING','CR65081-70','Consignment',2.00,3.15,'20','85052RS','85052RS' UNION ALL

    SELECT 'CR73374','T1188','CR73374 ','SWITCH - BRAKE','CR73374','Consignment',6.50,9.87,'3','CR73374','CR73374' UNION ALL

    SELECT 'CR83179-306','T1188','PWS9143XL','WHEEL-POLY','CR83179-306','Consignment',10.94,19.25,'8','PWS9143XL','PWS9143XL' UNION ALL

    SELECT 'CR83950','T1188','CR83950 ','AXLE','CR116802','Consignment',9.50,16.00,'3','CR83950','CR116802' UNION ALL

    SELECT 'D221348','T1188','','44" 6 DRAWER CABINET','D221348','Consignment',500.00,1500.00,'1','D221348','D221348' UNION ALL

    SELECT 'HY15135','T1188','HY15135 ','WASHER','HY15135','Consignment',0.05,0.06,'4','HY15135','HY15135' UNION ALL

    SELECT 'LP1426-1653','T1188','','DOUBLE SHIELD BEARING','CR65081-57','Consignment',0.47,2.00,'20','BG62052RS','BG62052RS' UNION ALL

    SELECT 'PM314187-000','T1188','RA850138757','MAGNETIC STRIP','RA850-138-757','Consignment',7.25,7.98,'2','RA850138757','RA850138757' UNION ALL

    SELECT 'PWS656XL','T1188','','XL POLY WHEEL','RA632-041-07','Consignment',22.29,81.25,'4','RA63204107','RA63204107' UNION ALL

    SELECT 'R01350550803','T1188','','RUBBER TRACTION','131/2X51/2X8RT','Consignment',59.49,99.50,'2','R01350550803','131/2X51/2X8RT' UNION ALL

    SELECT 'RA570-236-514','T1188','','BRUSH SET - 4','RA570-236-514','Consignment',14.75,20.65,'2','RA570236514','RA570236514' UNION ALL

    SELECT 'RA590-643-001','T1188','','HORN','RA590-643-001','Consignment',16.00,22.40,'2','RA590643001','RA590643001' UNION ALL

    SELECT 'RA590-643-002','T1188','RA590643002','HORN - 36 VOLT','RA590-643-002','Consignment',22.00,30.80,'1','RA590643002','RA590643002' UNION ALL

    SELECT 'RA710-241','T1188','RA710-241 ','SCREW - HEX HEAD','HY16820','Consignment',0.25,0.35,'4','RA710-241','HY16820' UNION ALL

    SELECT 'RA850-138-472','T1188','RA850138472','ORDER PICKING TRAY','RA850-138-472','Consignment',82.10,90.31,'2','RA850138472','RA850138472' UNION ALL

    SELECT 'RA919-352-043','T1188','','BOLT','RA919-352-043','Consignment',0.97,0.94,'5','RA939352043','RA939352043' UNION ALL

    SELECT 'RA939-352-050','T1188','','LOCK NUT','RA939-352-050','Consignment',1.05,1.16,'5','RA939352050','RA939352050' UNION ALL

    SELECT 'SY995','T1188','','HANDLE KIT GRAY','RA590-732','Consignment',3.25,4.55,'2','RA590732','RA590732' UNION ALL

    SELECT 'SYBU-AGC15','T1188','','FUSE - 15 AMP','RA590-514','Consignment',0.20,0.28,'5','RA590514','RA590514' UNION ALL

    SELECT 'SYF806','T1188','','XL LOAD WHEEL','YT524148750','Consignment',15.44,21.75,'6','YT524448750','YT524448750' UNION ALL

    SELECT 'SYF9811PS','T1188','RA632016101','XL POLY WHEEL','RA632-016-101','Consignment',17.25,24.25,'2','RA632016101','RA632016101' UNION ALL

    SELECT 'TH10096B1','T1188','RA632091','EZ DRIVE TIRE','RA632091','Consignment',0.01,85.00,'1','10X43/4X61/2EZ','RA632091' UNION ALL

    SELECT 'TH10326J2S','T1188','','EZ BLACK DRIVE TIRE PRESSED','13X51/2X91/2T','Consignment',96.41,175.00,'1','13X51/2X91/2EZB','13X51/2X91/2EZB' UNION ALL

    SELECT 'TH581A','T1188','RA632078107','XL POLY WHEEL','RA632-078-107','Consignment',44.69,70.00,'2','RA632078107','RA632078107' UNION ALL

    SELECT 'TH654A','T1188','RA632052107','CENTER GROOVE XL','RA632-052-107','Consignment',25.51,45.00,'8','RA632052107','RA632052107' UNION ALL

    SELECT 'YT580034920','T1188','YT580034920 ','CHARGER 24V 12A 120VAC ON BOA','YT524245865','Consignment',250.00,350.00,'1','YT5280034920','YT524245865' UNION ALL

    WOParts:

    /****** Object: Table [dbo].[WOParts] Script Date: 10/21/2013 15:10:19 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[WOParts](

    [WONo] [int] NOT NULL,

    [EntryDate] [datetime] NOT NULL,

    [BillTo] [nvarchar](50) NULL,

    [PartNo] [nvarchar](50) NULL,

    [PartNoAlias] [nvarchar](50) NULL,

    [Qty] [float] NULL,

    PRIMARY KEY CLUSTERED

    (

    [WONo] ASC,

    [EntryDate] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Sample Data:

    SELECT '150024961','May 15 2012 10:53AM','T1188','RA590-587-119','','1' UNION ALL

    SELECT '150025116','May 18 2012 2:19PM','T1188','BR855','RA441-225','10' UNION ALL

    SELECT '150025116','May 18 2012 2:20PM','T1188','RA632016101','','2' UNION ALL

    SELECT '150025116','May 18 2012 2:22PM','T1188','HY15135','','4' UNION ALL

    SELECT '150025116','May 18 2012 2:22PM','T1188','RA1060703101','','2' UNION ALL

    SELECT '150025116','May 18 2012 2:26PM','T1188','131/2X51/2X8RTWASSY','','2' UNION ALL

    SELECT '150025116','May 18 2012 2:27PM','T1188','RA590-514','','5' UNION ALL

    SELECT '150025116','May 18 2012 2:27PM','T1188','SB940','','10' UNION ALL

    SELECT '150025116','May 18 2012 2:28PM','T1188','SB906','','10' UNION ALL

    SELECT '150025116','May 18 2012 2:29PM','T1188','RA632052107','','4' UNION ALL

    SELECT '150025116','May 18 2012 2:31PM','T1188','RA632078107','','1' UNION ALL

    SELECT '150025116','May 18 2012 2:32PM','T1188','RA850138472','','2' UNION ALL

    SELECT '150025116','May 18 2012 2:32PM','T1188','RA850138757','','2' UNION ALL

    SELECT '150025116','May 18 2012 2:32PM','T1188','CR100463','','20' UNION ALL

    SELECT '150025116','May 18 2012 2:33PM','T1188','PWS9141XL','CR100462-306','7' UNION ALL

    SELECT '150025116','May 18 2012 2:34PM','T1188','CR115515','','1' UNION ALL

    SELECT '150025116','May 18 2012 2:35PM','T1188','PWS9131XL','','1' UNION ALL

    SELECT '150025116','May 18 2012 2:35PM','T1188','PWS9143XL','','7' UNION ALL

    SELECT '150025116','May 18 2012 2:35PM','T1188','85052RS','CR65081-070','17' UNION ALL

    SELECT '150025116','May 18 2012 2:36PM','T1188','BG6306-2RS','','4' UNION ALL

    SELECT '150025116','May 18 2012 2:37PM','T1188','RA632091','','1' UNION ALL

    SELECT '150025116','May 18 2012 2:37PM','T1188','CR125237-19','','1' UNION ALL

    SELECT '150025116','May 18 2012 2:38PM','T1188','CR125237-20','','1' UNION ALL

    SELECT '150025118','May 18 2012 2:22PM','T1188','HY15135','','4' UNION ALL

    SELECT '150025118','May 18 2012 2:27PM','T1188','RA590-514','','5' UNION ALL

    SELECT '150025118','May 18 2012 2:34PM','T1188','CR115515','','1' UNION ALL

    SELECT '150025118','May 18 2012 2:36PM','T1188','BG6306-2RS','','4' UNION ALL

    SELECT '150025118','May 18 2012 2:37PM','T1188','CR125237-19','','1' UNION ALL

    SELECT '150025118','May 18 2012 2:38PM','T1188','CR125237-20','','1' UNION ALL

    SELECT '150025254','May 23 2012 11:05AM','T1188','RA114-010-750','','2' UNION ALL

    SELECT '150026822','Jul 5 2012 3:29PM','T1188','CR60000-82','','8' UNION ALL

    SELECT '150026822','Jul 5 2012 3:30PM','T1188','CR65081-70-BULK','CR65081-070','20' UNION ALL

    SELECT '150026822','Jul 5 2012 3:30PM','T1188','CR60000-109','','8' UNION ALL

    SELECT '150026822','Jul 5 2012 3:30PM','T1188','CR60000-081','','8' UNION ALL

    SELECT '150026822','Jul 5 2012 3:31PM','T1188','LP1426-1633','CR65081-021','20' UNION ALL

    SELECT '150026822','Jul 5 2012 3:31PM','T1188','CR100463','','20' UNION ALL

    SELECT '150026822','Jul 5 2012 3:32PM','T1188','PWS9143XL','CR83179-306','10' UNION ALL

    SELECT '150026822','Jul 5 2012 3:33PM','T1188','PWS9141XL','CR100462-306','8' UNION ALL

    SELECT '150026822','Jul 5 2012 3:33PM','T1188','SYCFW1100BK','','3' UNION ALL

    SELECT '150026822','Jul 5 2012 3:33PM','T1188','SYCFW1100RD','','3' UNION ALL

    SELECT '150026822','Jul 5 2012 3:34PM','T1188','SY283','','1' UNION ALL

    SELECT '150026824','Jul 5 2012 3:29PM','T1188','CR60000-82','','8' UNION ALL

    SELECT '150026824','Jul 5 2012 3:30PM','T1188','CR65081-70-BULK','CR65081-070','20' UNION ALL

    SELECT '150026824','Jul 5 2012 3:30PM','T1188','CR60000-109','','8' UNION ALL

    SELECT '150026824','Jul 5 2012 3:30PM','T1188','CR60000-081','','8' UNION ALL

    SELECT '150026824','Jul 5 2012 3:31PM','T1188','LP1426-1633','CR65081-021','20' UNION ALL

    SELECT '150026824','Jul 5 2012 3:31PM','T1188','CR100463','','20' UNION ALL

    SELECT '150026824','Jul 5 2012 3:33PM','T1188','SYCFW1100BK','','3' UNION ALL

    SELECT '150026824','Jul 5 2012 3:33PM','T1188','SYCFW1100RD','','3' UNION ALL

    SELECT '150026824','Jul 5 2012 3:34PM','T1188','SY283','','1' UNION ALL

    SELECT '150028071','Aug 7 2012 12:13PM','T1188','CR145626','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:04PM','T1188','CR121502','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:05PM','T1188','CR90723','','2' UNION ALL

    SELECT '150042783','Aug 28 2013 4:14PM','T1188','CR73374','','3' UNION ALL

    SELECT '150042783','Aug 28 2013 4:14PM','T1188','CR100463','','20' UNION ALL

    SELECT '150042783','Aug 28 2013 4:14PM','T1188','CR1167942','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:15PM','T1188','CR116802','','3' UNION ALL

    SELECT '150042783','Aug 28 2013 4:16PM','T1188','PWS9141XL','CR100462-306','10' UNION ALL

    SELECT '150042783','Aug 28 2013 4:17PM','T1188','CR115418S001','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:19PM','T1188','CR115496','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:20PM','T1188','CR115509','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:20PM','T1188','CR115515','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:21PM','T1188','CR115518','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:21PM','T1188','CR115521','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:25PM','T1188','PWS9143XL','CR83179-306','8' UNION ALL

    SELECT '150042783','Aug 28 2013 4:26PM','T1188','13X51/2X91/2EZB','13X51/2X91/2T','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:27PM','T1188','85052RS','CR65081-070','20' UNION ALL

    SELECT '150042783','Aug 28 2013 4:29PM','T1188','BG62062RS','BG6206-2RS','6' UNION ALL

    SELECT '150042783','Aug 28 2013 4:30PM','T1188','BG63062RS','BG6306-2RS','4' UNION ALL

    SELECT '150042783','Aug 28 2013 4:32PM','T1188','RA632091','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:32PM','T1188','CR12523719','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:33PM','T1188','CR12523720','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:34PM','T1188','BG62052RS','RA441-225','30' UNION ALL

    SELECT '150042783','Aug 28 2013 4:35PM','T1188','RA590715','RA590-715','4' UNION ALL

    SELECT '150042783','Aug 28 2013 4:36PM','T1188','RA632016101','RA632-016-101','2' UNION ALL

    SELECT '150042783','Aug 28 2013 4:38PM','T1188','RA939352043','RA939-352-043','5' UNION ALL

    SELECT '150042783','Aug 28 2013 4:39PM','T1188','RA939352050','RA939-352-050','5' UNION ALL

    SELECT '150042783','Aug 28 2013 4:40PM','T1188','HY15135','','4' UNION ALL

    SELECT '150042783','Aug 28 2013 4:41PM','T1188','HY16820','','4' UNION ALL

    SELECT '150042783','Aug 28 2013 4:42PM','T1188','YT524245865','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:43PM','T1188','RA1060703101','','2' UNION ALL

    SELECT '150042783','Aug 28 2013 4:46PM','T1188','131/2X51/2X8RT','','2' UNION ALL

    SELECT '150042783','Aug 28 2013 4:47PM','T1188','RA52050901','RA520-509-01','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:48PM','T1188','RA570236514','','2' UNION ALL

    SELECT '150042783','Aug 28 2013 4:51PM','T1188','RA590514','','5' UNION ALL

    SELECT '150042783','Aug 28 2013 4:52PM','T1188','RA590643001','','2' UNION ALL

    SELECT '150042783','Aug 28 2013 4:53PM','T1188','RA590643002','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:54PM','T1188','RA590701','RA590-701','10' UNION ALL

    SELECT '150026640','Jun 28 2012 1:49PM','T1188','CR127938','','1' UNION ALL

    SELECT '150026640','Jun 28 2012 1:50PM','T1188','OL2230-PO','CR100462-306','12' UNION ALL

    SELECT '150026640','Jun 28 2012 1:50PM','T1188','TH10350J2SASSY','13X41/2X8T','3' UNION ALL

    SELECT '150026640','Jun 28 2012 1:51PM','T1188','TH10326J2SASSY','13X51/2X91/2T','2' UNION ALL

    SELECT '150026642','Jun 28 2012 1:49PM','T1188','CR127938','','1' UNION ALL

    SELECT '150026723','Jul 2 2012 10:49AM','T1188','TH2283A0','','5' UNION ALL

    SELECT '150026723','Jul 2 2012 10:50AM','T1188','PWS9131XL','','1' UNION ALL

    SELECT '150026723','Jul 2 2012 10:50AM','T1188','BR855','BG6205Z','20' UNION ALL

    SELECT '150028607','Aug 24 2012 12:23PM','T1188','PWS9131XL','CR123240-306','1' UNION ALL

    SELECT '150028607','Aug 24 2012 12:43PM','T1188','PWS9143XL','CR83179-306','4' UNION ALL

    SELECT '150028607','Aug 24 2012 12:43PM','T1188','TH10326J2SASSY','','1' UNION ALL

    SELECT '150028607','Aug 24 2012 12:44PM','T1188','131/2X51/2X8RTWASSY','','3' UNION ALL

    SELECT '150028607','Aug 24 2012 12:46PM','T1188','CR1167942','','1' UNION ALL

    SELECT '150028607','Aug 24 2012 12:51PM','T1188','CR115418S002','','1' UNION ALL

    SELECT '150028607','Aug 24 2012 12:57PM','T1188','TH10350J2SASSY','','2' UNION ALL

    SELECT '150028607','Aug 24 2012 12:58PM','T1188','PWS9131XL','CR123240-306','2' UNION ALL

    SELECT '150028636','Aug 24 2012 12:23PM','T1188','PWS9131XL','CR123240-306','1' UNION ALL

    SELECT '150028636','Aug 24 2012 12:51PM','T1188','CR115418S002','','1' UNION ALL

    SELECT '150042973','Sep 3 2013 11:19AM','T1188','TH10326J2','','1' UNION ALL

    SELECT '150042973','Sep 3 2013 11:19AM','T1188','TH10362A0','','1' UNION ALL

    SELECT '150042783','Aug 28 2013 4:55PM','T1188','RA590724','','10' UNION ALL

    SELECT '150042783','Aug 28 2013 4:56PM','T1188','RA590732','','2' UNION ALL

    SELECT '150042783','Aug 28 2013 4:59PM','T1188','RA632078107','','2' UNION ALL

    SELECT '150042783','Aug 28 2013 5:00PM','T1188','RA63204107','','4' UNION ALL

    SELECT '150042783','Aug 29 2013 7:34AM','T1188','RA850138472','','2' UNION ALL

    SELECT '150042783','Aug 29 2013 7:35AM','T1188','RA850138757','','2' UNION ALL

    SELECT '150042783','Aug 29 2013 7:37AM','T1188','CR6508157','','20' UNION ALL

    SELECT '150042783','Aug 29 2013 7:57AM','T1188','YT524448750','','1' UNION ALL

    SELECT '150042783','Aug 29 2013 8:00AM','T1188','RA632052107','','6' UNION ALL

    SELECT '150042783','Aug 29 2013 8:06AM','T1188','RA632069','','8' UNION ALL

    SELECT '150042831','Aug 29 2013 8:19AM','T1188','D221348','','1' UNION ALL

    SELECT '150028467','Aug 21 2012 3:18PM','T1188','CR132083-002','','1' UNION ALL

    SELECT '150025254','May 23 2012 11:07AM','T1188','RA114-010-751','','2' UNION ALL

    SELECT '150025254','May 23 2012 11:08AM','T1188','RA114-008-762','','2' UNION ALL

    SELECT '150025254','May 23 2012 11:09AM','T1188','RA1-150-356-001','','2' UNION ALL

    SELECT '150025254','May 23 2012 11:09AM','T1188','RA7-714-006-050','','1' UNION ALL

    SELECT '150025254','May 23 2012 11:10AM','T1188','RA114-008-637','','1' UNION ALL

    SELECT '150025254','May 23 2012 11:11AM','T1188','RA114-008-638','','1' UNION ALL

    SELECT '150025254','May 23 2012 11:11AM','T1188','RA611-044','','1' UNION ALL

    SELECT '150025254','May 23 2012 11:12AM','T1188','RA611-028','','1' UNION ALL

    SELECT '150025254','May 23 2012 11:12AM','T1188','944','SY944','15' UNION ALL

    SELECT '150025254','May 23 2012 11:14AM','T1188','907','','10' UNION ALL

    SELECT '150025345','May 23 2012 11:09AM','T1188','RA7-714-006-050','','1' UNION ALL

    SELECT '150025345','May 23 2012 11:10AM','T1188','RA114-008-637','','1' UNION ALL

    SELECT '150025345','May 23 2012 11:11AM','T1188','RA114-008-638','','1' UNION ALL

    SELECT '150027854','Jul 12 2012 1:58PM','T1188','SY8800','sy8800','1' UNION ALL

    SELECT '150027885','Aug 7 2012 12:13PM','T1188','CR145626','','1' UNION ALL

    SELECT '150027885','Aug 7 2012 12:13PM','T1188','CR145211','','1' UNION ALL

    SELECT '150028851','Aug 24 2012 12:51PM','T1188','CR115418S002','','1' UNION ALL

    SELECT '150027268','Jul 19 2012 1:47PM','T1188','CR062936-002','','1' UNION ALL

    SELECT '950001318','Jul 12 2012 1:58PM','T1188','SY8800','sy8800','1' UNION ALL

    SELECT '150027374','Jul 19 2012 1:47PM','T1188','CR062936-002','','-1' UNION ALL

    SELECT '150027375','Jul 19 2012 1:47PM','T1188','CR062936-002','','1' UNION ALL

    SELECT '150029305','Sep 12 2012 3:10PM','T1188','CR132083-002','','3' UNION ALL

  • October 21, 2013 at 1:47 pm

    #1660275

    I hope i did that correctly 🙂

  • October 21, 2013 at 1:55 pm

    #1660281

    Your sample data for WOParts has PK violations.


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • October 21, 2013 at 1:57 pm

    #1660282

    It is close. Had to remove TEXTIMAGE ON for the Parts table. That wasn't to big a deal.

    The WOParts table just doesn't work well. You defined the primary key as WONo, EntryDate and there are dozens and dozens of duplicates. The best thing you can do is to test out your scripts in a test database.

    Once you get the data sorted out we still need to know what you expect as output based on your sample data (this is another reason to keep the amount of sample limited as much as possible).

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 21, 2013 at 2:02 pm

    #1660285

    ah yes it does, that is because it is a time variable as one of the PK

    it is stored in our system as 2011-05-16 11:50:29.000 (down to the second, but when i did the QUOTENAME it only did to the minute, how can i prevent this?

  • October 21, 2013 at 2:05 pm

    #1660288

    Actually I think I just found a solution, thank you vary much!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply