Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 82 - Find elements that uniquely make up a group

The challenge is to find the Tax Code based on Price. There is a table called "LineItems" that have Invoice Number and Price. There is another table "Sales" that contains Invoice Number, Amount and Tax Code. Each sales amount is uniquely made up by the sum of one or more prices. Based on this you should assign the tax code to each price.

"Share database changes fast
Share SQL code updates, roll back changes you don’t want, inspect line-level differences between object versions, and check a full log of changes, all in SQL Server Management Studio. Start now with a 28-day free trial."

Sample Data

LineItems Table

ID InvoiceNum	 Price	
-- ------------- ------
1  IN-121-112123 20.00	
2  IN-121-112123 151.00	
3  IN-121-112123 250.00	
4  IN-121-112123 20.00	
5  IN-121-112123 15.00	
6  IN-121-112123 149.00	
7  IN-121-112123 100.00	
8  IN-121-112123 95.00

Sales Table

  
ID InvoiceNum	 Amount	TaxCode
-- ------------- ------ -------
1  IN-121-112123 500.00	A9
2  IN-121-112123 300.00	A3

Expected Results

ID InvoiceNum	 Price	TaxCode
-- ------------- ------ -------
1  IN-121-112123 20.00	A9
2  IN-121-112123 151.00	A3
3  IN-121-112123 250.00	A9
4  IN-121-112123 20.00	A9
5  IN-121-112123 15.00	A9
6  IN-121-112123 149.00	A3
7  IN-121-112123 100.00	A9
8  IN-121-112123 95.00  A9

Rules

  1. The output should be ordered by ID.
  2. For a particular Invoice Number, sum of Amount from Sales table will always same as sum of Price of LineItems table.
  3. The sample data will always be such that there is only one possible solution for each invoice.
  4. There can be more than one invoice number.
  5. You should make no assumptions on the maximum number of tax codes or the maximum number of items per invoice.

Sample Script

Use the TSQL Script given below to generate the source table and fill them with sample data.

    
IF OBJECT_ID('TC82_LineItems','U') IS NOT NULL BEGIN
  DROP TABLE TC82_LineItems
END 
GO

CREATE TABLE TC82_LineItems(
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[InvoiceNum] [VARCHAR](20) NOT NULL,
	[Price] [DECIMAL](18,2)  NOT NULL DEFAULT(0),	
) ON [PRIMARY]

GO

INSERT INTO TC82_LineItems(InvoiceNum,Price) 
SELECT 'IN-121-112123', 20 UNION ALL
SELECT 'IN-121-112123', 151 UNION ALL
SELECT 'IN-121-112123', 250 UNION ALL
SELECT 'IN-121-112123', 20  UNION ALL
SELECT 'IN-121-112123', 15  UNION ALL
SELECT 'IN-121-112123', 149 UNION ALL
SELECT 'IN-121-112123', 100 UNION ALL
SELECT 'IN-121-112123', 95

SELECT * FROM TC82_LineItems
GO

IF OBJECT_ID('TC82_Sales','U') IS NOT NULL BEGIN
  DROP TABLE TC82_Sales
END 
GO

CREATE TABLE TC82_Sales(
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[InvoiceNum] [VARCHAR](20) NOT NULL,
	[Amount] [DECIMAL](18,2) NOT NULL DEFAULT(0),
	[TaxCode] [VARCHAR](5) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO TC82_Sales(InvoiceNum,Amount,TaxCode) 
SELECT 'IN-121-112123', 500, 'A9' UNION ALL
SELECT 'IN-121-112123', 300, 'A3'

SELECT * FROM TC82_Sales
GO

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
  3. Your solution can use features from all versions up to and including SQL Server 2012.
"Share database changes fast
Share SQL code updates, roll back changes you don’t want, inspect line-level differences between object versions, and check a full log of changes, all in SQL Server Management Studio. Start now with a 28-day free trial."

puzzles
13 · 13% · 4057
Submit a Solution
Previous Challenge
Next Challenge
5
Liked
20
Comments
47
Solutions



Submit

TSQL Challenge 82 - Find elements that uniquely make up a group

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.