SQLTeam.com | Weblogs | Forums

Multiple Discounts - Edit Query


#1

Before , how to edit this query for show the after result ?
item discount_type discount_name
4895177031614 AYC'17-PA CLEARANCE SALE
4895177031614 GET10%DISCO AYC'17-BUY 3 OR MORE GET 10% EXTRA
4895177031560 AYC'17-PA CLEARANCE SALE
4895177031560 GET10%DISCO AYC'17-BUY 3 OR MORE GET 10% EXTRA
4895177033618 GET10%DISCO AYC'17-BUY 3 OR MORE GET 10% EXTRA
4895177033618 AYC'17-PA CLEARANCE SALE

After result
item discount_type discount_name
4895177031614 GET10%DISCO AYC'17-BUY 3 OR MORE GET 10% EXTRA / Clear sales
4895177031560 GET10%DISCO AYC'17-BUY 3 OR MORE GET 10% EXTRA / Clear Sales
4895177033618 GET10%DISCO AYC'17-BUY 3 OR MORE GET 10% EXTRA / Clear Sales

SELECT
itembarcode,
discountcode,
discountname
FROM
DW_PURE01.dbo.AX_DailySales_v1_2
WHERE
transactionid='0000000084' and SaleDate = '2017-06-08'

Please email to me : yiuwanlungfelix@gmail.com


#2

Please post consumable SQL CODE including create table statements or declare table variable and inserts of sample data.
Thanks


#3

Is this what you're after?

IF OBJECT_ID('tempdb..#DiscountsApplied', 'U') IS NOT NULL 
DROP TABLE #DiscountsApplied;

CREATE TABLE #DiscountsApplied (
	item BIGINT NOT NULL,
	discount_type VARCHAR(20) NULL,
	discount_name VARCHAR(100) NOT NULL 
	);

INSERT #DiscountsApplied(item, discount_type, discount_name) VALUES
('4895177031614', NULL, 'AYC''17-PA CLEARANCE SALE'),
('4895177031614', 'GET10%DISCO', 'AYC''17-BUY 3 OR MORE GET 10% EXTRA'),
('4895177031560', NULL, 'AYC''17-PA CLEARANCE SALE'),
('4895177031560', 'GET10%DISCO', 'AYC''17-BUY 3 OR MORE GET 10% EXTRA'),
('4895177033618', 'GET10%DISCO', 'AYC''17-BUY 3 OR MORE GET 10% EXTRA'),
('4895177033618', NULL, 'AYC''17-PA CLEARANCE SALE');

SELECT * FROM #DiscountsApplied da;

--==========================================================

SELECT 
	da1.item, 
	da1.discount_type, 
	discount_name = CONCAT(da1.discount_name, ' / ', SUBSTRING(da2.discount_name, CHARINDEX(' ', da2.discount_name, 1), 100))
FROM 
	#DiscountsApplied da1
	JOIN #DiscountsApplied da2
		ON da1.item = da2.item
WHERE
	da1.discount_type = 'GET10%DISCO'
	AND da2.discount_type IS NULL;

#4

Dear Sir,
Please help to write the mysql group-concat function convert to ms sql statement.

I want to check every sales order information by each item have total discount name and show the discount name in single row cell,, if buy 2 item is same style then sum the qty and grouped it (only display single discount name ).

Data

Sale number client item styleno Qty Discount name

Sale trx number s01 | ANDY | Apple | 001 2 Vip 10%

Sale trx number s01 |ANDY | Book | 002 1 item discount 15%

Sale trx number s01 |Andy | Book | 002 1 item discount 15%

Sale trx number s01 |Andy | cake | 003 3 father day discount 20%

Sale trx number s02 | tom | A510 | 004 1 item discount 15%

Sale trx number s02 | tom | A510 | 004 1 item discount 15%

Sale trx number s02 | tom | A511 | 005 2 vip 20%


Returns 1:

Sale number client item styleno Qty Discount name

Sale trx number s01 | ANDY | Apple | 001 2 Vip 10%

Sale trx number s01 |ANDY | Book | 002 2 item discount 15%

Sale trx number s01 |Andy | cake | 003 3 father day discount 20%

Sale trx number s02 | tom | A510 | 004 2 item discount 15%

Sale trx number s02 | tom | A511 | 005 2 vip 20%


Returns 2:
Sale number client item styleno Qty Discount name

Sale trx number s01 | ANDY | Apple | 001 7 Vip 10% + book item discount 15% + cake father day discount 20%
Sale trx number s02 | tom | A510 | 004 2 item discount 15%
Sale trx number s02 | tom | A511 | 005 2 vip 20%