Solved

structure columns in query

Posted on 2012-12-27
43
168 Views
Last Modified: 2013-01-08
I have a query that uses a union to get the results that I need, but I am having problems understanding how I need to change my query to be able to format the same results in a more readable manner. I am ending with a larger number of columns that I actually need.

The query is simple, I think the complexity comes in how it needs to be structured. May be I need to use CASE statements? I am not sure but any help would be appreciated.

My query:

SELECT 
	sl.No_ [ItemNumber]	
,   sl.Description
,   sl.[Document No_] [SalesOrderNumber]
,   sl.[Location Code] [LocationCode]
,   sl.[Shipment Date] [ShipmentDate]
,   sl.[Sell-to Customer No_] [CustomerNumber]
,   sl.Quantity [OrderQty]
,   cus.Name [CustomerName]
,   0  [FirmedPlannedQty]
,   '' [FirmedPlannedDueDate]
,   '' [FirmedPlannedNumber]
,   '' [FirmedPlannedLocation]
FROM
	dbo.[CQC$Sales Line] sl INNER JOIN
    dbo.[CQC$Customer] cus ON cus.No_ = sl.[Sell-to Customer No_]
WHERE
    sl.No_ = '10568'
AND
    sl.[Document Type] IN ('1')
AND
    sl.Quantity = sl.[Outstanding Quantity]
UNION
SELECT 
	'' [ItemNumber]	
,   '' [Description]
,   '' [SalesOrderNumber]
,   '' [LocationCode]
,   '' [ShipmentDate]
,   '' [CustomerNumber]
,   0  [OrderQty]
,   '' [CustomerName]
,	pol.Quantity [FirmedPlannedQty]
,   pol.[Due Date] [FirmedPlannedDueDate]
,   pol.[Prod_ Order No_] [FirmedPlannedNumber]
,   pol.[Location Code] [FirmedPlannedLocation]
FROM 
	dbo.[CQC$Prod_ Order Line] pol
WHERE	
	[Item No_] = '10568'	
AND 
	Status = 2
AND 
	[Variant Code] <> 'R'
ORDER BY 
	ShipmentDate asc
,	FirmedPlannedDueDate asc

Open in new window



the results:

current results of my query
how I need it to be (mock up with excel):

mock up with excel

Thank you very much for your help.

If more details need to be added, please let me know.
0
Comment
Question by:metropia
  • 23
  • 18
  • 2
43 Comments
 
LVL 1

Expert Comment

by:Omar_El_Sergany
Comment Utility
Don't know if that what you need or not but you can give it a try

SELECT 
	sl.No_ [ItemNumber]	
,   sl.Description
,   sl.[Document No_] [Document No]
,   sl.[Location Code] [Location]
,   '' [DueDate]
,   sl.[Shipment Date] [ShipmentDate]
,   sl.[Sell-to Customer No_] [CustomerNumber]
,   cus.Name [CustomerName]
,   sl.Quantity [Quantity]



FROM
	dbo.[CQC$Sales Line] sl INNER JOIN
    dbo.[CQC$Customer] cus ON cus.No_ = sl.[Sell-to Customer No_]
WHERE
    sl.No_ = '10568'
AND
    sl.[Document Type] IN ('1')
AND
    sl.Quantity = sl.[Outstanding Quantity]
UNION
SELECT 
	'' [ItemNumber]	
,   '' [Description]
,   pol.[Prod_ Order No_] [Document No]
,   pol.[Location Code] [Location]
,   pol.[Due Date] [DueDate]
,   '' [ShipmentDate]
,   '' [CustomerNumber]
,   '' [CustomerName]
,	pol.Quantity [Quantity]


FROM 
	dbo.[CQC$Prod_ Order Line] pol
WHERE	
	[Item No_] = '10568'	
AND 
	Status = 2
AND 
	[Variant Code] <> 'R'
ORDER BY 
	ShipmentDate asc
,	FirmedPlannedDueDate asc

Open in new window

0
 

Author Comment

by:metropia
Comment Utility
do you think this change will that give me the output sort of what the last screen shot looks like?
0
 
LVL 1

Expert Comment

by:Omar_El_Sergany
Comment Utility
if you attach SQL script to create the tables with data I can help you to get closer to what you want
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
It is better to use crystal reports or ssrs for such layouts but if you only want to to use sql for the job then one of the solutions is as follows

-------------------------------------------------------------------------------
DECLARE @SalesLine TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[Description]	VARCHAR(50)
	,[SalesOrderNumber]	VARCHAR(50)
	,[LocationCode]	VARCHAR(50)
	,[ShipmentDate]	DATETIME
	,[CustomerNumber]	VARCHAR(50)
	,[OrderQty]	VARCHAR(50)
)
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10603','90','2012-05-01','10090','42000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10604','90','2012-05-04','10090','12000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10605','90','2012-05-04','10090','6000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10619','90','2012-05-04','10090','32000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10767','20','2012-05-08','10537','24000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10722','90','2012-06-01','10090','18000')

DECLARE @Customer TABLE (
	 [CustomerNumber]	VARCHAR(50)
	,[CustomerName]	VARCHAR(50)
)
INSERT INTO @Customer VALUES ('10090', 'Cano Packaging Corporation')
INSERT INTO @Customer VALUES ('10537', 'Create A Pack Foods')

DECLARE @Product TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[Description]	VARCHAR(50)
)
INSERT INTO @Product VALUES ('10568','Wilton Cocoa')
INSERT INTO @Product VALUES ('10569','Pepsi Max')

DECLARE @OrderLine TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[FirmedPlannedQty]	VARCHAR(50)
	,[FirmedPlannedDueDate]	DATETIME
	,[FirmedPlannedNumber]	VARCHAR(50)
	,[FirmedPlannedLocation]	VARCHAR(50)
)
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-06','FP15309','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-08','FP15311','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-09','FP15313','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15337','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15339','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15341','20')

--SELECT * FROM @SalesLine
--SELECT * FROM @Customer
--SELECT * FROM @OrderLine

DECLARE @Result TABLE (
	 [Item]	VARCHAR(50)
	,[Description]	VARCHAR(50)
	,[DOCUMENT No.]	VARCHAR(50)
	,[Location] VARCHAR(50)
	,[Due DATE]	VARCHAR(50)
	,[Ship DATE]	VARCHAR(50)
	,[Sell-TO]	VARCHAR(50)
	,[Customer]	VARCHAR(50)
	,[Quantity]	VARCHAR(50)
	,[SortDate] DATETIME
)
DECLARE @ItemNumber VARCHAR(50)

-------------------------------------------------------------------------------
SET @ItemNumber = '10568'

INSERT INTO
	@Result
SELECT
	 ItemNumber
	,Description
	,''
	,''
	,''
	,''
	,''
	,''
	,''
	,''
FROM
	@Product
WHERE
	ItemNumber = @ItemNumber

;WITH cteResult 
AS 
(
	(
		SELECT 
			 '' AS ItemNumber
			,'' AS Description
			,FirmedPlannedNumber
			,FirmedPlannedLocation
			,CONVERT(VARCHAR,FirmedPlannedDueDate,103) AS DueDate
			,'' AS ShipDate
			,'' AS SellTo
			,'' AS Customer
			,FirmedPlannedQty
			,FirmedPlannedDueDate
		FROM
			@OrderLine
		WHERE
			ItemNumber = @ItemNumber
	)
	UNION
	(
	SELECT 
		 ''
		,''
		,SalesOrderNumber
		,LocationCode
		,''
		,CONVERT(VARCHAR,ShipmentDate,103)
		,c.CustomerNumber
		,CustomerName
		,OrderQty
		,ShipmentDate
	FROM
		@SalesLine s
		INNER JOIN @Customer c ON s.CustomerNumber = c.CustomerNumber
	WHERE
		ItemNumber = @ItemNumber
	)	
)
INSERT INTO 
	@Result
SELECT 
	*
FROM 
	cteResult

-------------------------------------------------------------------------------		
SELECT 
	 Item
	,Description
	,[Document No.]
	,Location
	,[Due Date]
	,[Ship Date]
	,[Sell-TO]
	,Customer
	,Quantity
FROM
	@Result
ORDER BY 
	SortDate

Open in new window

0
 

Author Comment

by:metropia
Comment Utility
@RehanYousaf

I have a question regarding your code example. I see that you use a variable for ItemNumber. What if I don't need to specify an Item Number? What if I need to retrieve all Items? Should I just remove the variable and the WHERE clasues?


Thank you for your example, it is awesome.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Nope, removing where clause will have different effect, what you need to do is use while loop with the two inserts

I will paste the solution soon
0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 500 total points
Comment Utility
try the following script for all items

-------------------------------------------------------------------------------
DECLARE @SalesLine TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[Description]	VARCHAR(50)
	,[SalesOrderNumber]	VARCHAR(50)
	,[LocationCode]	VARCHAR(50)
	,[ShipmentDate]	DATETIME
	,[CustomerNumber]	VARCHAR(50)
	,[OrderQty]	VARCHAR(50)
)
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10603','90','2012-05-01','10090','42000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10604','90','2012-05-04','10090','12000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10605','90','2012-05-04','10090','6000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10619','90','2012-05-04','10090','32000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10767','20','2012-05-08','10537','24000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10722','90','2012-06-01','10090','18000')

INSERT INTO @SalesLine VALUES ('10569','Wilton Cocoa','S10603','90','2012-05-01','10090','42000')
INSERT INTO @SalesLine VALUES ('10569','Wilton Cocoa','S10604','90','2012-05-04','10090','12000')

DECLARE @Customer TABLE (
	 [CustomerNumber]	VARCHAR(50)
	,[CustomerName]	VARCHAR(50)
)
INSERT INTO @Customer VALUES ('10090', 'Cano Packaging Corporation')
INSERT INTO @Customer VALUES ('10537', 'Create A Pack Foods')

DECLARE @Product TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[Description]	VARCHAR(50)
)
INSERT INTO @Product VALUES ('10568','Wilton Cocoa')
INSERT INTO @Product VALUES ('10569','Pepsi Max')

DECLARE @OrderLine TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[FirmedPlannedQty]	VARCHAR(50)
	,[FirmedPlannedDueDate]	DATETIME
	,[FirmedPlannedNumber]	VARCHAR(50)
	,[FirmedPlannedLocation]	VARCHAR(50)
)
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-06','FP15309','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-08','FP15311','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-09','FP15313','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15337','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15339','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15341','20')

INSERT INTO @OrderLine VALUES ('10569','49000','2012-04-08','FP15311','20')
INSERT INTO @OrderLine VALUES ('10569','49000','2012-05-09','FP15313','20')

--SELECT * FROM @SalesLine
--SELECT * FROM @Customer
--SELECT * FROM @OrderLine

DECLARE @Result TABLE (
	 [Item]	VARCHAR(50)
	,[Description]	VARCHAR(50)
	,[DOCUMENT No.]	VARCHAR(50)
	,[Location] VARCHAR(50)
	,[Due DATE]	VARCHAR(50)
	,[Ship DATE]	VARCHAR(50)
	,[Sell-TO]	VARCHAR(50)
	,[Customer]	VARCHAR(50)
	,[Quantity]	VARCHAR(50)
	,[SortDate] DATETIME
)
DECLARE @ItemNumber VARCHAR(50)
DECLARE @x INT
DECLARE @y INT

-------------------------------------------------------------------------------
SELECT 
	*,
	ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#Product
FROM
	@Product
	
--SELECT *
--FROM #Product	

SET @x = 1
SELECT 
	@y = COUNT(*)
FROM
	@Product

WHILE @x <= @y
BEGIN
	SELECT 
		@ItemNumber = ItemNumber
	FROM	
		#Product
	WHERE
		RowNo = @x
		
	INSERT INTO
		@Result
	SELECT
		 ItemNumber
		,Description
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
	FROM
		@Product
	WHERE
		ItemNumber = @ItemNumber

	;WITH cteResult 
	AS 
	(
		(
			SELECT 
				 '' AS ItemNumber
				,'' AS Description
				,FirmedPlannedNumber
				,FirmedPlannedLocation
				,CONVERT(VARCHAR,FirmedPlannedDueDate,103) AS DueDate
				,'' AS ShipDate
				,'' AS SellTo
				,'' AS Customer
				,FirmedPlannedQty
				,FirmedPlannedDueDate AS 'SortDate'
			FROM
				@OrderLine
			WHERE
				ItemNumber = @ItemNumber
		)
		UNION
		(
		SELECT 
			 ''
			,''
			,SalesOrderNumber
			,LocationCode
			,''
			,CONVERT(VARCHAR,ShipmentDate,103)
			,c.CustomerNumber
			,CustomerName
			,OrderQty
			,ShipmentDate
		FROM
			@SalesLine s
			INNER JOIN @Customer c ON s.CustomerNumber = c.CustomerNumber
		WHERE
			ItemNumber = @ItemNumber
		)	
	)
	INSERT INTO 
		@Result
	SELECT 
		*
	FROM 
		cteResult
	ORDER BY
		SortDate
		
	SET @x = @x + 1
END

-------------------------------------------------------------------------------		
SELECT 
	 Item
	,Description
	,[DOCUMENT No.]
	,Location
	,[Due DATE]
	,[Ship DATE]
	,[Sell-TO]
	,Customer
	,Quantity
FROM
	@Result

	
-------------------------------------------------------------------------------			
DROP TABLE #Product	

Open in new window

0
 

Author Comment

by:metropia
Comment Utility
@RehanYousaf, thank you again for your help. Your query results are impeccable.

I would like to understand something. I noticed that due to the need for data for you to show me how the query works, you are using hard coded values and the multipe INSERT statements.

How would the query change, in which places, to make it data driven instead that using hard coded values? Do I just remove the INSERT statements and replace them with the SELECT statements from my  original query posted at the top of this question?

I am not sure how to make this change, if you could offer some guidance, that would be kind.

Thank you very much.
0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 500 total points
Comment Utility
Hi,

I had to create variable tables and insert data into them so that I can write the script

Variable table syntax is like: DECLARE @SalesLine TABLE

and then I used the follwoing script to insert data into variable table: INSERT INTO @SalesLine VALUES

What you have to do is replace variable table with your own and replace columns with your own

			SELECT 
				 '' AS ItemNumber
				,'' AS Description
				,FirmedPlannedNumber
				,FirmedPlannedLocation
				,CONVERT(VARCHAR,FirmedPlannedDueDate,103) AS DueDate
				,'' AS ShipDate
				,'' AS SellTo
				,'' AS Customer
				,FirmedPlannedQty
				,FirmedPlannedDueDate AS 'SortDate'
			FROM
				@OrderLine
			WHERE
				ItemNumber = @ItemNumber

Open in new window

For instance in the above mentioned script you will replace @OrderLine with your tablename and the related columns with your own

P.S. @Result and #Product are extra tables to process data
0
 

Author Comment

by:metropia
Comment Utility
Hi again RehanYousaf,

Sorry I have been busy with other tasks and just today I am able to get back to this work.
While removing some of your code and starting to use my actual views, I came up to this question, what abou parts such as:

SELECT 
	*,
	ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#Product
FROM
	@Product
	
SET @x = 1
SELECT 
	@y = COUNT(*)
FROM
	@Product

Open in new window


Do I still need these parts?
0
 

Author Comment

by:metropia
Comment Utility
@RehanYousaf,

I think I was somehow able to modify the script you wrote. Unfortunately I am still struggling to make it work.

When an Item Number is contained in multiple orders, lets say 5 orders (numbers prefixed SB), I get duplicate results, based on the number of orders, in the screen shot example, this is five times.

Do you know any recommendation on how to work this issue out?  And, how would I be able to avoid displaying dates that are zeroes and interpreted by SQL server as 1900-01-01 00:00:00.000?

I have attached the revised query and a screen shot of the results after the modifications.

duplicate results
Thank you very much for your help and patience.
invplan-formatted-using-dinamyc-.sql
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Hi Metropia,

Do you have a seperate table where you store all item details?
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Try the following script

-------------------------------------------------------------------------------
DECLARE @Result TABLE 
(
	[Item]	VARCHAR(20)
,	[Description]	VARCHAR(50)
,	[DocumentNo]	VARCHAR(20)
,	[Location] VARCHAR(10)
,	[DueDate]	DATETIME
,	[ShipDate]	DATETIME
,	[SellTo]	VARCHAR(20)
,	[Customer]	VARCHAR(50)
,	[Quantity]	VARCHAR(50)
,	[SortDate] DATETIME
)
DECLARE @ItemNumber VARCHAR(20)
DECLARE @x INT
DECLARE @y INT

-------------------------------------------------------------------------------
--	@SalesLine				=	[nav].[vStd_SalesLineWithCustomerName]
--	@FirmPlannedProdOrders	=	[nav].[vStd_FirmPlannedProductionOrders]

SELECT DISTINCT
	 [ItemNumber]
	,[Description]
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#SalesLineItem
FROM
	[nav].[vStd_SalesLineWithCustomerName]
	
SET @x = 1
SELECT 
	@y = COUNT(*)
FROM
	[nav].[vStd_SalesLineWithCustomerName]

WHILE @x <= @y
BEGIN
	SELECT 
		@ItemNumber = ItemNumber
	FROM	
		#SalesLineItem
	WHERE
		RowNo = @x
		
	INSERT INTO
		@Result
	SELECT
		ItemNumber
	,	[Description]
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	FROM
		[nav].[vStd_SalesLineWithCustomerName]
	WHERE
		ItemNumber = @ItemNumber

	;WITH cteResult 
	AS 
	(
		(
		SELECT 
			'' AS ItemNumber
		,	'' AS [Description]
		,	[FirmPlannedNumber]
		,	[FirmPlannedLocation]
		,	[FirmPlannedDueDate] AS [DueDate]
		,	'' AS [ShipDate]
		,	'' AS [SellTo]
		,	'' AS [Customer]
		,	[FirmPlannedQty]
		,	[FirmPlannedDueDate] AS [SortDate]
		FROM
			[nav].[vStd_FirmPlannedProductionOrders]
		WHERE
			ItemNumber = @ItemNumber
		)
		UNION
		(
		SELECT 
			''
		,	''
		,	SalesOrderNumber
		,	LocationCode
		,	''
		,	[ShipmentDate]
		,	CustomerNumber
		,	CustomerName
		,	OrderQty
		,	ShipmentDate
		FROM
			[nav].[vStd_SalesLineWithCustomerName]
		WHERE
			ItemNumber = @ItemNumber
		)	
	)
	INSERT INTO 
		@Result
	SELECT 
		*
	FROM 
		cteResult
	ORDER BY
		SortDate
		
	SET @x = @x + 1
END
-------------------------------------------------------------------------------		
SELECT 
	[Item]
,	[Description]
,	[DocumentNo]
,	[Location]
,	[DueDate]
,	[ShipDate]
,	[SellTo]
,	[Customer]
,	[Quantity]
FROM
	@Result	
-------------------------------------------------------------------------------			
DROP TABLE #SalesLineItem

Open in new window

0
 

Author Comment

by:metropia
Comment Utility
Hi,

The Item details come from the view [nav].[vStd_SalesLineWithCustomerName]

I tried your new script but I see the same results

10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
		S10828	20	1900-01-01 00:00:00.000	2012-05-02 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15201	20	2012-05-04 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10829	20	1900-01-01 00:00:00.000	2012-05-09 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15202	20	2012-05-11 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10830	20	1900-01-01 00:00:00.000	2012-05-16 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15203	20	2012-05-18 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10831	20	1900-01-01 00:00:00.000	2012-05-23 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
		S10828	20	1900-01-01 00:00:00.000	2012-05-02 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15201	20	2012-05-04 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10829	20	1900-01-01 00:00:00.000	2012-05-09 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15202	20	2012-05-11 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10830	20	1900-01-01 00:00:00.000	2012-05-16 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15203	20	2012-05-18 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10831	20	1900-01-01 00:00:00.000	2012-05-23 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
		S10828	20	1900-01-01 00:00:00.000	2012-05-02 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15201	20	2012-05-04 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10829	20	1900-01-01 00:00:00.000	2012-05-09 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15202	20	2012-05-11 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10830	20	1900-01-01 00:00:00.000	2012-05-16 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15203	20	2012-05-18 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10831	20	1900-01-01 00:00:00.000	2012-05-23 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
10202	DARK CHOC LIQUID CHIP BULK			1900-01-01 00:00:00.000	1900-01-01 00:00:00.000			
		S10828	20	1900-01-01 00:00:00.000	2012-05-02 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15201	20	2012-05-04 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10829	20	1900-01-01 00:00:00.000	2012-05-09 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15202	20	2012-05-11 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10830	20	1900-01-01 00:00:00.000	2012-05-16 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15203	20	2012-05-18 00:00:00.000	1900-01-01 00:00:00.000			45000.00000000000000000000
		S10831	20	1900-01-01 00:00:00.000	2012-05-23 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000

Open in new window


The correct results should show ideally the same way you showed me with the hard coded values, like (the spaces in between each column due to my poor text editing):

10202	DARK CHOC LIQUID CHIP BULK			
		S10828	20			2012-05-02 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15201	20	2012-05-04 00:00:00.000					45000.00000000000000000000
		S10829	20	2012-05-09 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15202	20	2012-05-11 00:00:00.000					45000.00000000000000000000
		S10830	20	2012-05-16 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000
		FP15203	20	2012-05-18 00:00:00.000					45000.00000000000000000000
		S10831	20	2012-05-23 00:00:00.000	10316	PECAN DELUXE-BULK 	45000.00000000000000000000

Open in new window


Thank you much for your help.
0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 500 total points
Comment Utility
My apologies ... forgot to replace first table .. try the script now

-------------------------------------------------------------------------------
DECLARE @Result TABLE 
(
	[Item]	VARCHAR(20)
,	[Description]	VARCHAR(50)
,	[DocumentNo]	VARCHAR(20)
,	[Location] VARCHAR(10)
,	[DueDate]	DATETIME
,	[ShipDate]	DATETIME
,	[SellTo]	VARCHAR(20)
,	[Customer]	VARCHAR(50)
,	[Quantity]	VARCHAR(50)
,	[SortDate] DATETIME
)
DECLARE @ItemNumber VARCHAR(20)
DECLARE @x INT
DECLARE @y INT

-------------------------------------------------------------------------------
--	@SalesLine				=	[nav].[vStd_SalesLineWithCustomerName]
--	@FirmPlannedProdOrders	=	[nav].[vStd_FirmPlannedProductionOrders]

SELECT DISTINCT
	 [ItemNumber]
	,[Description]
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#SalesLineItem
FROM
	[nav].[vStd_SalesLineWithCustomerName]
	
SET @x = 1
SELECT 
	@y = COUNT(*)
FROM
	[nav].[vStd_SalesLineWithCustomerName]

WHILE @x <= @y
BEGIN
	SELECT 
		@ItemNumber = ItemNumber
	FROM	
		#SalesLineItem
	WHERE
		RowNo = @x
		
	INSERT INTO
		@Result
	SELECT
		ItemNumber
	,	[Description]
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	FROM
		#SalesLineItem
	WHERE
		ItemNumber = @ItemNumber

	;WITH cteResult 
	AS 
	(
		(
		SELECT 
			'' AS ItemNumber
		,	'' AS [Description]
		,	[FirmPlannedNumber]
		,	[FirmPlannedLocation]
		,	[FirmPlannedDueDate] AS [DueDate]
		,	'' AS [ShipDate]
		,	'' AS [SellTo]
		,	'' AS [Customer]
		,	[FirmPlannedQty]
		,	[FirmPlannedDueDate] AS [SortDate]
		FROM
			[nav].[vStd_FirmPlannedProductionOrders]
		WHERE
			ItemNumber = @ItemNumber
		)
		UNION
		(
		SELECT 
			''
		,	''
		,	SalesOrderNumber
		,	LocationCode
		,	''
		,	[ShipmentDate]
		,	CustomerNumber
		,	CustomerName
		,	OrderQty
		,	ShipmentDate
		FROM
			[nav].[vStd_SalesLineWithCustomerName]
		WHERE
			ItemNumber = @ItemNumber
		)	
	)
	INSERT INTO 
		@Result
	SELECT 
		*
	FROM 
		cteResult
	ORDER BY
		SortDate
		
	SET @x = @x + 1
END
-------------------------------------------------------------------------------		
SELECT 
	[Item]
,	[Description]
,	[DocumentNo]
,	[Location]
,	[DueDate]
,	[ShipDate]
,	[SellTo]
,	[Customer]
,	[Quantity]
FROM
	@Result	
-------------------------------------------------------------------------------			
DROP TABLE #SalesLineItem

Open in new window


P.S. Keep all the columns as string, text or varchar and you will get blanks rather than 1900-01-01 00:00:00.000
0
 

Author Comment

by:metropia
Comment Utility
I am not sure what I am doing wrong, but I see no changes after the second time you posted the latest script.

here is another item to illustrate the point:

item 10264 contains two order numbers (order numbers are the ones that start with SB)
and the same results is shown two times, as per each order that there is. That is my assumption.

how can i make sure to display blanks instead that the 1900-01-01 00:00:00.000 date?
I am not sure how to do this.

latest result
0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 500 total points
Comment Utility
Replace
DECLARE @Result TABLE 
(
	[Item]	VARCHAR(20)
,	[Description]	VARCHAR(50)
,	[DocumentNo]	VARCHAR(20)
,	[Location] VARCHAR(10)
,	[DueDate]	DATETIME
,	[ShipDate]	DATETIME
,	[SellTo]	VARCHAR(20)
,	[Customer]	VARCHAR(50)
,	[Quantity]	VARCHAR(50)
,	[SortDate] DATETIME
)

Open in new window

with
DECLARE @Result TABLE 
(
	[Item]	VARCHAR(20)
,	[Description]	VARCHAR(50)
,	[DocumentNo]	VARCHAR(20)
,	[Location]	VARCHAR(10)
,	[DueDate]	VARCHAR(20)
,	[ShipDate]	VARCHAR(20)
,	[SellTo]	VARCHAR(20)
,	[Customer]	VARCHAR(50)
,	[Quantity]	VARCHAR(50)
,	[SortDate] DATETIME
)

Open in new window



Also tell me what you get when you run the following script
SELECT DISTINCT
	 [ItemNumber]
	,[Description]
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
FROM
	[nav].[vStd_SalesLineWithCustomerName]

Open in new window

0
 

Author Comment

by:metropia
Comment Utility
regarding your second post, these are the results
results
0
 

Author Comment

by:metropia
Comment Utility
regarding the date columns. now i see an explicit date

Jan  1 1900 12:00AM      May  2 2012 12:00AM
May  4 2012 12:00AM      Jan  1 1900 12:00AM
Jan  1 1900 12:00AM      May  9 2012 12:00AM
May 11 2012 12:00AM      Jan  1 1900 12:00AM
Jan  1 1900 12:00AM      May 16 2012 12:00AM
May 18 2012 12:00AM      Jan  1 1900 12:00AM
Jan  1 1900 12:00AM      May 23 2012 12:00AM
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Hmm ... I think I know what is wrong ... just give me a minute
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Tel me what result do you get

;WITH cteSalesLineItem 
AS (
	SELECT DISTINCT
		 [ItemNumber]
		,[Description]
	FROM
		[nav].[vStd_SalesLineWithCustomerName]
)
SELECT 
	 *
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
FROM
	cteSalesLineItem

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:metropia
Comment Utility
these are the results

10202      DARK CHOC LIQUID CHIP BULK      1
10203      CALCIUM YOGURT FLVD WAFER      2
10217      MILK CHOC LIQUID CHIP BULK      3
10221      BULK DARK CANDY WAFER      4
10232      ORCHID WAFER      5
10233      NOBLE YOGURT FLVD WAFER      6
10240      DARK BLUE WAFER      7
10241      BLUE WAFER      8
10245      DARK CHOC MINT WAFER      9
10256      RS YOGURT W/ TIO2 RIBBON      10
10258      WHITE MINT WAFER      11
10264      PINK WAFER      12
10269      CARROT FLVD 260 CT REGAL WAFER      13
10288      ALPINE WHITE W/TIO2 WAFER      14
10292      ORANGE WAFER      15
10302      YELLOW WAFER      16
10307      DARK ROAST PEANUT WAFER      17
10318      HI PRO MALTITOL YOGURT RIBBON      18
10328      HI PRO MALTITOL PEANUT RIBBON      19
10330      RS MILK CHOC FLVD RIBBON      20
0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 500 total points
Comment Utility
Replace
SELECT DISTINCT
	 [ItemNumber]
	,[Description]
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#SalesLineItem
FROM
	[nav].[vStd_SalesLineWithCustomerName]

Open in new window

with
;WITH cteSalesLineItem 
AS (
	SELECT DISTINCT
		 [ItemNumber]
		,[Description]
	FROM
		[nav].[vStd_SalesLineWithCustomerName]
)
SELECT 
	 *
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#SalesLineItem
FROM
	cteSalesLineItem

Open in new window

0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
And for the date lets try this

Replace
	INSERT INTO
		@Result
	SELECT
		ItemNumber
	,	[Description]
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	,	''
	FROM
		#SalesLineItem
	WHERE
		ItemNumber = @ItemNumber

Open in new window

With
	INSERT INTO
		@Result
	SELECT
		ItemNumber
	,	[Description]
	,	'-'
	,	'-'
	,	'-'
	,	'-'
	,	'-'
	,	'-'
	,	'-'
	,	'-'
	FROM
		#SalesLineItem
	WHERE
		ItemNumber = @ItemNumber

Open in new window

0
 

Author Comment

by:metropia
Comment Utility
these are the results:

improved ressults!

would you be able to help me fix the "fake" dates?

thank you very much.
0
 

Author Comment

by:metropia
Comment Utility
about the dates.

I got several of these warnings:

(138 row(s) affected)
Msg 242, Level 16, State 3, Line 54
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

(7 row(s) affected)
Msg 242, Level 16, State 3, Line 54
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

(1 row(s) affected)
Msg 242, Level 16, State 3, Line 54
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.


and the results are now different. altered by the '-' modification
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Ignore the last change ... let me try something different
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Replace
		(
		SELECT 
			'' AS ItemNumber
		,	'' AS [Description]
		,	[FirmPlannedNumber]
		,	[FirmPlannedLocation]
		,	[FirmPlannedDueDate] AS [DueDate]
		,	'' AS [ShipDate]
		,	'' AS [SellTo]
		,	'' AS [Customer]
		,	[FirmPlannedQty]
		,	[FirmPlannedDueDate] AS [SortDate]
		FROM
			[nav].[vStd_FirmPlannedProductionOrders]
		WHERE
			ItemNumber = @ItemNumber
		)
		UNION
		(
		SELECT 
			''
		,	''
		,	SalesOrderNumber
		,	LocationCode
		,	''
		,	[ShipmentDate]
		,	CustomerNumber
		,	CustomerName
		,	OrderQty
		,	ShipmentDate
		FROM
			[nav].[vStd_SalesLineWithCustomerName]
		WHERE
			ItemNumber = @ItemNumber
		)	

Open in new window

with
		(
		SELECT 
			'' AS ItemNumber
		,	'' AS [Description]
		,	[FirmPlannedNumber]
		,	[FirmPlannedLocation]
		,	CONVERT(VARCHAR(10),[FirmPlannedDueDate]) AS [DueDate]
		,	'' AS [ShipDate]
		,	'' AS [SellTo]
		,	'' AS [Customer]
		,	[FirmPlannedQty]
		,	[FirmPlannedDueDate] AS [SortDate]
		FROM
			[nav].[vStd_FirmPlannedProductionOrders]
		WHERE
			ItemNumber = @ItemNumber
		)
		UNION
		(
		SELECT 
			''
		,	''
		,	SalesOrderNumber
		,	LocationCode
		,	''
		,	CONVERT(VARCHAR(10),[ShipmentDate]) AS [ShipmentDate]
		,	CustomerNumber
		,	CustomerName
		,	OrderQty
		,	ShipmentDate
		FROM
			[nav].[vStd_SalesLineWithCustomerName]
		WHERE
			ItemNumber = @ItemNumber
		)	

Open in new window

0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Basically its all about

CONVERT(VARCHAR(10),[FirmPlannedDueDate]) AS [DueDate]

and

CONVERT(VARCHAR(10),[ShipmentDate]) AS [ShipmentDate]
0
 

Author Comment

by:metropia
Comment Utility
it looks great, the results.
is there a technique that displaying 1900-01-01 can be fixed but at the same time be able to see the date as mm/dd/yyyy format?
awesome results
0
 

Author Comment

by:metropia
Comment Utility
SELECT 
			'' AS ItemNumber
		,	'' AS [Description]
		,	[FP_Number]
		,	[FP_Location]
		,	CONVERT(VARCHAR(10),[FP_DueDate]) AS [DueDate]
		,	'' AS [ShipDate]
		,	'' AS [SellTo]
		,	'' AS [Customer]
		,	[FP_Qty]
		,	[FP_DueDate] AS [SortDate]
		FROM
			[nav].[vStd_FirmPlannedProductionOrders]
		WHERE
			FP_ItemNumber = @ItemNumber
		)
		UNION
		(
		SELECT 
			''
		,	''
		,	SalesOrderNumber
		,	LocationCode
		,	''
		,	CONVERT(VARCHAR(10), [ShipmentDate]) AS [ShipmentDate]
		,	CustomerNumber
		,	CustomerName
		,	OrderQty
		,	ShipmentDate
		FROM
			[nav].[vStd_SalesLineWithCustomerName]
		WHERE
			ItemNumber = @ItemNumber

Open in new window

0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 500 total points
Comment Utility
For date format you can try

CONVERT(VARCHAR(10),[FirmPlannedDueDate], 101) AS [DueDate]

and

CONVERT(VARCHAR(10),[ShipmentDate], 101) AS [ShipmentDate]



for more date formats you can check the link : http://www.sql-server-helper.com/tips/date-formats.aspx


once you have updated ... can you send me your final script to review ... I will try to fix default date issue
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Also can you send me the screenshot of result when you run the following script
-------------------------------------------------------------------------------
DECLARE @SalesLine TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[Description]	VARCHAR(50)
	,[SalesOrderNumber]	VARCHAR(50)
	,[LocationCode]	VARCHAR(50)
	,[ShipmentDate]	DATETIME
	,[CustomerNumber]	VARCHAR(50)
	,[OrderQty]	VARCHAR(50)
)
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10603','90','2012-05-01','10090','42000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10604','90','2012-05-04','10090','12000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10605','90','2012-05-04','10090','6000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10619','90','2012-05-04','10090','32000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10767','20','2012-05-08','10537','24000')
INSERT INTO @SalesLine VALUES ('10568','Wilton Cocoa','S10722','90','2012-06-01','10090','18000')

INSERT INTO @SalesLine VALUES ('10569','Wilton Cocoa','S10603','90','2012-05-01','10090','42000')
INSERT INTO @SalesLine VALUES ('10569','Wilton Cocoa','S10604','90','2012-05-04','10090','12000')

DECLARE @Customer TABLE (
	 [CustomerNumber]	VARCHAR(50)
	,[CustomerName]	VARCHAR(50)
)
INSERT INTO @Customer VALUES ('10090', 'Cano Packaging Corporation')
INSERT INTO @Customer VALUES ('10537', 'Create A Pack Foods')

DECLARE @Product TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[Description]	VARCHAR(50)
)
INSERT INTO @Product VALUES ('10568','Wilton Cocoa')
INSERT INTO @Product VALUES ('10569','Pepsi Max')

DECLARE @OrderLine TABLE (
	 [ItemNumber]	VARCHAR(50)
	,[FirmedPlannedQty]	VARCHAR(50)
	,[FirmedPlannedDueDate]	DATETIME
	,[FirmedPlannedNumber]	VARCHAR(50)
	,[FirmedPlannedLocation]	VARCHAR(50)
)
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-06','FP15309','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-08','FP15311','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-09','FP15313','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15337','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15339','20')
INSERT INTO @OrderLine VALUES ('10568','49000','2012-05-22','FP15341','20')

INSERT INTO @OrderLine VALUES ('10569','49000','2012-04-08','FP15311','20')
INSERT INTO @OrderLine VALUES ('10569','49000','2012-05-09','FP15313','20')

--SELECT * FROM @SalesLine
--SELECT * FROM @Customer
--SELECT * FROM @OrderLine

DECLARE @Result TABLE (
	 [Item]	VARCHAR(50)
	,[Description]	VARCHAR(50)
	,[DOCUMENT No.]	VARCHAR(50)
	,[Location] VARCHAR(50)
	,[Due DATE]	VARCHAR(50)
	,[Ship DATE]	VARCHAR(50)
	,[Sell-TO]	VARCHAR(50)
	,[Customer]	VARCHAR(50)
	,[Quantity]	VARCHAR(50)
	,[SortDate] DATETIME
)
DECLARE @ItemNumber VARCHAR(50)
DECLARE @x INT
DECLARE @y INT

-------------------------------------------------------------------------------
SELECT 
	*,
	ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#Product
FROM
	@Product
	
--SELECT *
--FROM #Product	

SET @x = 1
SELECT 
	@y = COUNT(*)
FROM
	@Product

WHILE @x <= @y
BEGIN
	SELECT 
		@ItemNumber = ItemNumber
	FROM	
		#Product
	WHERE
		RowNo = @x
		
	INSERT INTO
		@Result
	SELECT
		 ItemNumber
		,Description
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
	FROM
		@Product
	WHERE
		ItemNumber = @ItemNumber

	;WITH cteResult 
	AS 
	(
		(
			SELECT 
				 '' AS ItemNumber
				,'' AS Description
				,FirmedPlannedNumber
				,FirmedPlannedLocation
				,CONVERT(VARCHAR,FirmedPlannedDueDate,103) AS DueDate
				,'' AS ShipDate
				,'' AS SellTo
				,'' AS Customer
				,FirmedPlannedQty
				,FirmedPlannedDueDate AS 'SortDate'
			FROM
				@OrderLine
			WHERE
				ItemNumber = @ItemNumber
		)
		UNION
		(
		SELECT 
			 ''
			,''
			,SalesOrderNumber
			,LocationCode
			,''
			,CONVERT(VARCHAR,ShipmentDate,103)
			,c.CustomerNumber
			,CustomerName
			,OrderQty
			,ShipmentDate
		FROM
			@SalesLine s
			INNER JOIN @Customer c ON s.CustomerNumber = c.CustomerNumber
		WHERE
			ItemNumber = @ItemNumber
		)	
	)
	INSERT INTO 
		@Result
	SELECT 
		*
	FROM 
		cteResult
	ORDER BY
		SortDate
		
	SET @x = @x + 1
END

-------------------------------------------------------------------------------		
SELECT 
	 Item
	,Description
	,[DOCUMENT No.]
	,Location
	,[Due DATE]
	,[Ship DATE]
	,[Sell-TO]
	,Customer
	,Quantity
FROM
	@Result

	
-------------------------------------------------------------------------------			
DROP TABLE #Product	

Open in new window

0
 

Author Comment

by:metropia
Comment Utility
hi  RehanYousaf,

here is the screen shot you requested

screen shor requested
0
 

Author Comment

by:metropia
Comment Utility
@RehanYousaf,

Thank you very much for your help on this query. I truly appreciate and value the help I have received from you. It has been a lifesaver.

I need to add another thing to the result. I have that query ready, but this question is getting super long, and I have gotten the answer I was originally looking for already.

I will open a new question, and post the link to that question here. Hopefully I will be lucky and get your help again, it should not be too cumbersome.


At any rate, thank you very much for your kind help and instruction.
ee-sql-script.txt
0
 

Author Comment

by:metropia
Comment Utility
@RehanYousaf,

Here is the link to my new question. hop you can offer a hand.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27988494.html

I will go ahead and close this question.

Thank you very much.
0
 

Author Closing Comment

by:metropia
Comment Utility
Thank you. Excellent help. Skilled, and kind Expert.
0
 

Author Comment

by:metropia
Comment Utility
@RehanYousaf,

I think I spoke too soon, I am seeing duplicates in the results again :/

duplicates
I am using the script at the one I uploaded on comment id  38755345
0
 

Author Comment

by:metropia
Comment Utility
Seems like this Item Number is entered twice in the database and assigned to two different but at the same time similar items

10559      WILTON WHITE 1911-919x WAFER   < the only difference is the 'x'
10559      WILTON WHITE 1911-919 WAFER


when I look at the results outside your code, I see this:

item 10559

There is only one order to 10559      WILTON WHITE 1911-919x WAFER

Would this be throwing your code off? Is there a way to work around it? I see this same case happening to another item in the database.

thank you.
0
 

Author Comment

by:metropia
Comment Utility
I just verified, and the x next to the description, is not an error. It supposed to be there
0
 

Author Comment

by:metropia
Comment Utility
the problem is on this part I think,

;WITH cteSalesLineItem 
AS (
	SELECT DISTINCT
		 [ItemNumber]
		,[Description]
	FROM
		[nav].[vStd_SalesLineWithCustomerName]
)
SELECT 
	 *
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#SalesLineItem
FROM
	cteSalesLineItem

Open in new window



the description column is the problem. how could i retrieve the description although different but with same item number. i just care for the description that does not contain the 'x'
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Try this to remove duplicates

;WITH cteSalesLineItem 
AS (
	SELECT 
		 [ItemNumber]
		,MIN([Description]) AS [Description]
	FROM
		[nav].[vStd_SalesLineWithCustomerName]
	GROUP BY
		[ItemNumber]
)
SELECT 
	 *
	,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
	#SalesLineItem
FROM
	cteSalesLineItem

Open in new window

you can also try
MAX([Description]) AS [Description]
whichever suits you

as for the new question ... i will try to look at it tomorrow
0
 

Author Comment

by:metropia
Comment Utility
i am going to try your suggestion.

in the mean time what i did wat to joing to a new view "item" from which I get the description.


thank you very much.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now