Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
Solved
structure columns in query
Posted on 2012-12-27
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:
the results:
how I need it to be (mock up with excel):
Thank you very much for your help.
If more details need to be added, please let me know.
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
the results:
how I need it to be (mock up with excel):
Thank you very much for your help.
If more details need to be added, please let me know.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
23
18
2
43 Comments
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
if you attach SQL script to create the tables with data I can help you to get closer to what you want
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
@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.
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.
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
I will paste the solution soon
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
@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.
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.
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
P.S. @Result and #Product are extra tables to process data
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
P.S. @Result and #Product are extra tables to process data
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:
Do I still need these parts?
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
Do I still need these parts?
@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.
Thank you very much for your help and patience.
invplan-formatted-using-dinamyc-.sql
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.
Thank you very much for your help and patience.
invplan-formatted-using-dinamyc-.sql
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
Hi,
The Item details come from the view [nav].[vStd_SalesLineWithC
I tried your new script but I see the same results
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):
Thank you much for your help.
The Item details come from the view [nav].[vStd_SalesLineWithC
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
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
Thank you much for your help.
My apologies ... forgot to replace first table .. try the script now
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
-------------------------------------------------------------------------------
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
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
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.
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.
Replace
Also tell me what you get when you run 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 @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
)
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]
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
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
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
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
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
Replace
SELECT DISTINCT
[ItemNumber]
,[Description]
,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
#SalesLineItem
FROM
[nav].[vStd_SalesLineWithCustomerName]
;WITH cteSalesLineItem
AS (
SELECT DISTINCT
[ItemNumber]
,[Description]
FROM
[nav].[vStd_SalesLineWithCustomerName]
)
SELECT
*
,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
#SalesLineItem
FROM
cteSalesLineItem
And for the date lets try this
Replace
Replace
INSERT INTO
@Result
SELECT
ItemNumber
, [Description]
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
FROM
#SalesLineItem
WHERE
ItemNumber = @ItemNumber
INSERT INTO
@Result
SELECT
ItemNumber
, [Description]
, '-'
, '-'
, '-'
, '-'
, '-'
, '-'
, '-'
, '-'
FROM
#SalesLineItem
WHERE
ItemNumber = @ItemNumber
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
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
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
)
(
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
)
Basically its all about
CONVERT(VARCHAR(10),[FirmP
and
CONVERT(VARCHAR(10),[Shipm
CONVERT(VARCHAR(10),[FirmP
and
CONVERT(VARCHAR(10),[Shipm
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?
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?
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
For date format you can try
CONVERT(VARCHAR(10),[FirmP
and
CONVERT(VARCHAR(10),[Shipm
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
CONVERT(VARCHAR(10),[FirmP
and
CONVERT(VARCHAR(10),[Shipm
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
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
@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
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
@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.
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.
@RehanYousaf,
I think I spoke too soon, I am seeing duplicates in the results again :/
I am using the script at the one I uploaded on comment id 38755345
I think I spoke too soon, I am seeing duplicates in the results again :/
I am using the script at the one I uploaded on comment id 38755345
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:
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.
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:
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.
the problem is on this part I think,
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'
;WITH cteSalesLineItem
AS (
SELECT DISTINCT
[ItemNumber]
,[Description]
FROM
[nav].[vStd_SalesLineWithCustomerName]
)
SELECT
*
,ROW_NUMBER() OVER(ORDER BY ItemNumber) AS 'RowNo'
INTO
#SalesLineItem
FROM
cteSalesLineItem
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'
Try this to remove duplicates
MAX([Description]) AS [Description]
whichever suits you
as for the new question ... i will try to look at it tomorrow
;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
MAX([Description]) AS [Description]
whichever suits you
as for the new question ... i will try to look at it tomorrow
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll
650 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.