May 27, 2014 at 2:15 am
I have a SQL question as follows and I have difficulty gettting the correct result:
Tables:
F_Discount
Item_IDDiscount_Period_StartDiscount_Period_EndDiscount_Percentage
1001 2011-01-01 2011-01-05 0.2
1002 2011-01-15 2011-01-20 0.1
1003 2011-02-01 2011-02-05 0.1
D_Item
Item_IDItem_DescriptionUnit_Price
1001 HDD 80
1002 SSD 60
1003 RAM 30
F_SalesMaster
Invoice_NoInvoice_DateAmount
A2011012011-01-01 124
A2011022011-01-10 145
F_SalesDetail
Invoice_NoInvoice_DateItem_NoAmount
A2011012011-01-01 1001 64
A2011012011-01-01 1002 60
A2011022011-01-10 1001 64
A2011022011-01-10 1002 54
A2011022011-01-10 1003 27
Question: Write your SQL statement to check which transaction(s) (Invoice_No) has incorrect Sales amount against discount
Anyone would help me with it? Much appreciated.
May 27, 2014 at 2:28 am
Can you show us what you've got so far?
May 27, 2014 at 7:53 am
bigcj (5/27/2014)
I have a SQL question as follows and I have difficulty gettting the correct result:Tables:
F_Discount
Item_IDDiscount_Period_StartDiscount_Period_EndDiscount_Percentage
1001 2011-01-01 2011-01-05 0.2
1002 2011-01-15 2011-01-20 0.1
1003 2011-02-01 2011-02-05 0.1
D_Item
Item_IDItem_DescriptionUnit_Price
1001 HDD 80
1002 SSD 60
1003 RAM 30
F_SalesMaster
Invoice_NoInvoice_DateAmount
A2011012011-01-01 124
A2011022011-01-10 145
F_SalesDetail
Invoice_NoInvoice_DateItem_NoAmount
A2011012011-01-01 1001 64
A2011012011-01-01 1002 60
A2011022011-01-10 1001 64
A2011022011-01-10 1002 54
A2011022011-01-10 1003 27
Question: Write your SQL statement to check which transaction(s) (Invoice_No) has incorrect Sales amount against discount
Anyone would help me with it? Much appreciated.
I would suggest calculating the correct sales amount and comparing to the amount in F_SalesMaster
May 29, 2014 at 1:15 pm
DDL is always helpful for getting a good answer faster.
Below is the DDL and solution to your problem. Note that some columns are not necessary; I included them so you can better understand my solution. This should give you something to start with. Let us know if this helps.
--F_Discount
DECLARE @F_DiscountTABLE
(
Item_ID int primary key,
Discount_Period_Start date not null,
Discount_Period_End date not null,
Discount_Percentage decimal (3,2) not null
);
INSERT @F_Discount values
(1001, '2011-01-01', '2011-01-05', 0.2),
(1002, '2011-01-15', '2011-01-20', 0.1),
(1003, '2011-02-01', '2011-02-05', 0.1);
--D_Item
DECLARE @D_ItemTABLE
(
Item_ID int primary key,
Item_Description varchar(10) not null,
Unit_Price int not null
);
INSERT @D_Item values
(1001, 'HDD', 80),
(1002, 'SSD', 60),
(1003, 'RAM', 30);
--F_SalesMaster
DECLARE @F_SalesMaster TABLE
(
Invoice_No varchar(10) primary key,
Invoice_Date date not null,
Amount int not null
);
INSERT @F_SalesMaster values
('A201101', '2011-01-01', 124),
('A201102', '2011-01-10', 145);
--F_SalesDetail
DECLARE @F_SalesDetail TABLE
(
Invoice_No varchar(10) not null,
Invoice_Date date not null,
Item_Noint not null,
Amount int not null
);
INSERT @F_SalesDetail values
('A201101', '2011-01-01', 1001, 64),
('A201101', '2011-01-01', 1002, 60),
('A201102', '2011-01-10', 1001, 64),
('A201102', '2011-01-10', 1002, 54),
('A201102', '2011-01-10', 1003, 27);
/*
--each table:
SELECT * FROM @F_Discount
SELECT * FROM @D_Item
SELECT * FROM @F_SalesMaster
SELECT * FROM @F_SalesDetail
-- item details query:
SELECT i.Item_ID,
i.Item_Description,
i.Unit_Price,
d.Discount_Period_Start,
d.Discount_Period_End,
d.Discount_Percentage
FROM @D_Item i
JOIN @F_Discount d ON i.Item_ID = d.Item_ID;
-- invoice dtails query:
SELECTsm.Invoice_No,
sm.Invoice_Date,
sm.Amount,
sd.Item_No,
sd.Amount
FROM @F_SalesMaster sm
JOIN @F_SalesDetail sd ON sm.Invoice_No = sd.Invoice_No;
*/
-- the final query
WITH items_sales AS
(
SELECTi.Item_ID,
i.Item_Description,
i.Unit_Price,
d.Discount_Period_Start,
d.Discount_Period_End,
s.Invoice_Date,
is_discountPeriod =
case
when s.Invoice_Date between d.Discount_Period_Start and d.Discount_Period_End
then 'yes' else 'no'
end,
Discount_Percentage =
case when s.Invoice_Date between d.Discount_Period_Start and d.Discount_Period_End
then d.Discount_Percentage else 0
end,
-- s.master_amt, -- don't understand what this is, not needed
s.detail_amt,
correct_price = i.Unit_Price *
case when s.Invoice_Date between d.Discount_Period_Start and d.Discount_Period_End
then 1-d.Discount_Percentage else 1
end
FROM @D_Item i
JOIN @F_Discount d ON i.Item_ID = d.Item_ID
JOIN
(
SELECTsm.Invoice_No,
sm.Invoice_Date,
master_amt = sm.Amount,
sd.Item_No,
detail_amt = sd.Amount
FROM @F_SalesMaster sm
JOIN @F_SalesDetail sd ON sm.Invoice_No = sd.Invoice_No
) s ON i.Item_ID = s.Item_No
)
SELECT *, correct_or_no =
case
when detail_amt = correct_price then 'correct' else 'wrong'
end
FROM items_sales;
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply