Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Header and Detailed Data Puzzle

In this puzzle we have to show the header data and the detailed data from shipment in header and detail fashion. Please check the sample input and expected output.

Sample Input

Shipments

Shipment Number DB Source Weight
24584 MGXT 35000
24969 MGXT 100
26341 MGXT 15000

[Billing Info]

Shipment Number Charge Type Description Billing Line Billing Amount
24584 HNDL Handling       1 200
24584 SVC Service Fee       2 20
24969 MISC Miscellaneous Charge 1 50
26341 TRAN Transportation     1 365
26341 PUC Pickup Charge     2 25
26341 LTX Local Tax       3 5

[Invoice Table]

Shipment Number Invoice Number Invoice Date Invoice Currency
24584 50012A 2017-05-08 00:00:00.000 USD
24969 50489A 2017-06-09 00:00:00.000 USD
26341 50773A 2017-07-10 00:00:00.000 USD

Expected Output

Type Shipment Number Invoice Number Invoice Date InvoiceWeight DB Source
Header 24584 50012A 2017-05-08 00:00:00.000 35000 MGXT
Charges USD 200 HNDL Handling        
Charges USD 20 SVC Service Fee        
Header 24969 50489A 2017-06-09 00:00:00.000 100 MGXT
Charges USD 50 MISC Miscellaneous Charge  
Header 26341 50773A 2017-07-10 00:00:00.000 15000 MGXT
Charges USD 365 TRAN Transportation      
Charges USD 25 PUC Pickup Charge      
Charges USD 5 LTX Local Tax      

Script

Use below script to create table and insert sample data into it.

--

Shipments

CREATE TABLE Shipments
(
	 [Shipment Number]	BIGINT
	,[DB Source] VARCHAR(10)
	,[Weight] BIGINT
)
GO

INSERT INTO Shipments VALUES
(24584,'MGXT',35000	 ),
(24969,'MGXT',100	 ),
(26341,'MGXT',15000	 )
GO

[Billing Info]

CREATE TABLE [Billing Info]
(
	 [Shipment Number]	BIGINT	
	,[Charge Type] VARCHAR(10)
	,[Description] VARCHAR(25)
	,[Billing Line] INT
	,[Billing Amount] INT
)
GO

INSERT INTO [Billing Info] VALUES
(24584,'HNDL','Handling			',1,	200			),
(24584,'SVC'	,'Service Fee			',2,	20	),
(24969,'MISC','Miscellaneous Charge',1,	50			),
(26341,'TRAN','Transportation		',1,	365		),
(26341,'PUC'	,'Pickup Charge		',2,	25		),
(26341,'LTX'	,'Local Tax			',3,	5.5		)
GO

[Invoice Table]

CREATE TABLE [Invoice Table]		
(
	 [Shipment Number]	BIGINT	
	,[Invoice Number] VARCHAR(10)
	,[Invoice Date] DATETIME
	,[Invoice Currency] VARCHAR(3)
)
GO

INSERT INTO [Invoice Table] VALUES
(24584,'50012A','2017/05/08','USD'),
(24969,'50489A','2017/06/09','USD'),
(26341,'50773A','2017/07/10','USD')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


SELECT Type,[Shipment Number],[Invoice Number],[Invoice Date],[InvoiceWeight],[DB Source]
FROM 
(
	SELECT 'Header' Type , CAST(s.[Shipment Number] AS sql_variant) [Shipment Number] ,CAST(I.[Invoice Number] AS sql_variant) [Invoice Number]
		,CAST(I.[Invoice Date] AS sql_variant) [Invoice Date] ,CAST(s.[Weight] AS sql_variant) [InvoiceWeight]
		,s.[DB Source]  ,s.[Shipment Number] Ord , 1 Ord1
	FROM Shipments s
	FULL JOIN [Invoice Table] I ON I.[Shipment Number] = s.[Shipment Number]

	UNION ALL

	SELECT Type , u.[Invoice Currency],t.[Billing Amount] [InvoiceAmount], t.[Charge Type],t.[Description]
		, '' [DB Source], u.[Shipment Number] Ord  , 2 Ord1
	FROM 
	(
		SELECT s.*,I.[Invoice Currency],I.[Invoice Date],I.[Invoice Number]
		FROM Shipments s
		FULL JOIN [Invoice Table] I ON I.[Shipment Number] = s.[Shipment Number]
	)u
	CROSS APPLY
	(
		SELECT 'Charges' Type , b.* 
		FROM [Billing Info] b 
		WHERE b.[Shipment Number] = u.[Shipment Number]
	)t
)k
ORDER BY Ord,Ord1

--

Output

--

/*------------------------
OUTPUT
------------------------*/
Type    Shipment Number   Invoice Number   Invoice Date                    InvoiceWeight          DB Source
------- ---------------------------------- ------------------------------- ---------------------- ----------
Header  24584             50012A           2017-05-08 00:00:00.000         35000                  MGXT
Charges USD               200              HNDL                            Handling			            
Charges USD               20               SVC                             Service Fee			          
Header  24969             50489A           2017-06-09 00:00:00.000         100                    MGXT
Charges USD               50               MISC                            Miscellaneous Charge   
Header  26341             50773A           2017-07-10 00:00:00.000         15000                  MGXT
Charges USD               365              TRAN                            Transportation		        
Charges USD               25               PUC                             Pickup Charge		         
Charges USD               5                LTX                             Local Tax			            

(9 row(s) affected)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements