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
-------------------------------------------------------------------------------
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
SELECT
*,
ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
#Product
FROM
@Product
SET @x = 1
SELECT
@y = COUNT(*)
FROM
@Product
-------------------------------------------------------------------------------
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
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
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
;WITH cteSalesLineItem
AS (
SELECT DISTINCT
[ItemNumber]
,[Description]
FROM
[nav].[vStd_SalesLineWithCustomerName]
)
SELECT
*
,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
FROM
cteSalesLineItem
INSERT INTO
@Result
SELECT
ItemNumber
, [Description]
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
FROM
#SalesLineItem
WHERE
ItemNumber = @ItemNumber
With
INSERT INTO
@Result
SELECT
ItemNumber
, [Description]
, '-'
, '-'
, '-'
, '-'
, '-'
, '-'
, '-'
, '-'
FROM
#SalesLineItem
WHERE
ItemNumber = @ItemNumber
(
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
)
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
)
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
-------------------------------------------------------------------------------
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
;WITH cteSalesLineItem
AS (
SELECT DISTINCT
[ItemNumber]
,[Description]
FROM
[nav].[vStd_SalesLineWithCustomerName]
)
SELECT
*
,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
#SalesLineItem
FROM
cteSalesLineItem
;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
you can also try
Open in new window