declare
@start_date datetime,
@end_date datetime, 
@sales_type varchar(max),
@item_id varchar(max),
@tenant_id varchar(50),
@salesman varchar(max),
@branch varchar(max),
@region varchar(max),
@channel varchar(max)

select
@start_date='2024-02-01 00:00:00',
@end_date='2024-02-26 00:00:00',
@sales_type=N'Cash Outlet,Credit Outlet',
@item_id=N'e02a3d5f-4ba3-460a-8d15-7c4b6c72ae52,B59D4760CA6C4BB281C398FB6DEB2B4E,6C3018993A1C42819587CB8EE32A7DEB,21FF5E9012B342EC9657569987ACDE74,A3111C696D5743CB94F60B9D7AF03453,79c8e25d-793e-4a41-b5a9-1f1fba9bdc9b,24ddd3bd-2232-4b25-9920-9424db8c8aaa,b0cf3eae-d3e0-4f71-9bf2-7c701b1ff2a7,f6730f64-228e-48b2-88b1-c79c222d1424,f5ad6a67-d4cd-4dda-b1de-1ba882201461,202f7533-0ba1-404e-94dd-1c7517360fcc,c7c29290-1f2a-4d74-9bd6-60277f214937,3c123143-83d6-4f79-a0a6-e7a5ecf7c9c5,9aafc093-518f-43f3-b19e-1ecc6f142e24,15a5d436-7e1d-4644-bb68-13147b02f0da,3717bb53-5a84-4a49-bc0c-76f8a49876e0,b53e692e-009c-470b-9628-7ce3cb3a71f0,b82b3d02-bf86-418e-b086-9195e315d01d,3892b082-042a-4772-aedf-bac1045a55c5,995423e4-5016-428a-80f4-76e29f19d40c,b8088e1d-60cd-4d09-8733-f01ff3d98511,beaba696-7d11-4ccf-91dc-0dec079b086c,5d62cfaa-7a06-49f6-ab98-e449fcdeea82,79d2ac58-f674-459e-898b-d33b25c1762b,e0e23881-3abd-4de5-9e1d-d7f82f93b0f1,6813e7a8-40f3-4bd5-8ff0-61d103c4f08d,402120D582E44D5E9D84CE413D29EF79,920c3403-8498-4e70-85e2-bc97fadd4db7,10c19d6e-d962-4a54-932e-114207ce5ae5,b1591a98-81d0-4855-a76b-30ce706e0e4e,aa3ce849-864a-4480-889c-386d18393608,d7e85a46-4ba3-4b10-b57d-adacac9ce2ae,7892bf73-b31d-431d-87af-65940ccfd57f,d3010a66-0c17-4ea9-8c07-32b0b60024b4,0cd74f11-5134-40ae-83b0-657811975c11,8d797878-dd62-40d4-9d82-e1cf2f3fdffe,80890fbc-8fd7-4121-a19f-3a44e255525c,022F4D8A4DD44621A4CEF142717116AD,20aa97ce-2615-4403-8174-63a1e7f8bc38,1b43fbfe-2bca-46a5-800b-41f0ebaf10e5,093799b8-3d28-4339-8205-8fb7d4a0cef6,9594c88d-2599-4a99-81fc-2f3848042890,cfb0119a-efa6-43b2-a09e-33b10d7f47f2,a4a3d1de-bd0a-43d4-b381-bff211332174,d0fd26f5-2e81-40fb-bcad-6419a9f2d41f,d129b8f9-a515-462a-9ce3-be20df87ff45,f16a7759-a602-42aa-9ac6-e27110245130,a07f1074-faea-4b18-9b61-4e44d38021b7',
@tenant_id=N'A000003',
@salesman=N'8949db2c1b2c4d27a69d14893d09a47a,A000003-admin,C833C056-0C6F-4807-BAEA-95DDE8896E59,2221C2DD-BDB4-4F06-A66D-A33E5D4C9178,3BF827F2-A389-4D52-B66C-0E8B32F55103,A4BC94F4-B024-48A0-A903-0F289CAEB224,4C157E7C-3AFB-4BBE-9931-2EF40BD9C470,0E40E164-1372-433B-8227-60C44DBAD7A0,2D5D6445-C68E-48DE-A6BF-0CE2A9C9C982,E3A1FE2A-CE95-45D0-ADB1-FFF5EA3FA973,DA6475F6-257E-4BC3-A292-CC4002EBE50C,80260F10-3641-4A33-8AD8-D78802AF5E3F,9AAF8D5E-0C1B-423E-9878-E3236B5726CB,9C991F6E-66BE-49A2-B0D5-F9C64E19590F,536A6E70-D246-4FBF-AF89-2387BA3ED808,BC9752FB-CE69-40D1-838D-CF18B2600498,F2C01F78-60F4-41FB-9FA0-6F8A0845EDA0,ECD07AEB-D7FF-4A18-90A0-A12A3F8B3824,2A957D60-AC26-410F-BBC5-03405C7B5BF5,0E854A27-F2AC-4331-90BA-140A1177119A,23947927-9B66-4A47-ABA2-236DC781B790,620fba2c1362403aa7b0dd41a93b17de,EA51A421-59D0-49C9-8C42-785AB05F102C,68A6FA5B-21B5-48E0-9211-590D0D4B1EF3,2A592DC2-1B07-4B77-928C-9BB46D34B7CE,ECEDDA2C-2337-431D-91DE-77B1C334EAA4,6ED67647-44F7-46DC-AEFD-4C803C75D6C3,e25e5d3c3f894820a884d85d67345e0f,2F3A4D62-554B-48D7-86DA-2901FA880895,64FD9FC7-496A-4FF0-B98C-BA350BD92AE7,00FD7280-0EBA-4559-8327-197756D10F66,82384340-A664-47E1-97A1-EEA0F6691F25,D02649FE-5FD5-4988-BAC0-263665876528,e23c5396e74349efa90424856e5d8904,38BCB24A-DEAF-4A8E-8CAF-75EB45E5D7EA,9D74826B-9023-4349-BDAB-08228045C088,595508EF-9372-46A2-9175-13087E442FC9,42ED5D84-AF1F-4859-B792-165AED114C35,3CF59B15-DC20-4398-BCF0-3D0CF7350F23,8123D517-5714-47AD-B9DA-35472020BC96,B6A3443C-5718-4E5E-9152-0B1BAC227EE2,DCF66768-922D-4A21-96F0-E2E5C5C8CC01,421A6E00-C93D-4D86-B66D-6B5118149C8E,3BD74B37-B5E4-42AD-972C-A8816560F41D,5E485267-6041-4360-8AE9-8F805F033495,7B291602-BE1B-425E-B332-3BBCECD1E809,A3FF16E2-EEFC-46A1-967E-61C4D799EEAB,F7467CEA-63B6-4DD2-8463-91DA1DBC116F,083758C2-A88F-4CB3-90F9-37ABA89CB82C,F955579D-AA63-45F0-991D-8ECC96CA1CE5,5665C89F-CAA9-4463-8CC9-2ADDC7FE4A8B,1E78BB9C-6A26-4722-A225-206CA84A0A77,0DC382EF-6042-47E1-A990-D509741CB691,84F4EE3B-AE54-4FB0-BE89-902B02ABF161,FA94B7FD-3D30-4A39-B3EA-C067E38E2551,E4E0BC74-450B-430A-8E63-430669A1F623,A6638C6C-912A-4882-BB8E-280AA3E89F6E,21551170-F7DF-4517-9770-9B62E2A2D62F,44C2C51C-6A50-47BF-A088-306DE52E1234,4ECF9E99-91A2-43F3-BD35-FAE9F1419B4E,1039B5A3-E711-408A-A05F-868AF45F272F,BDEB46EA-5E99-42F6-BCAF-70FF41383215,27DF6EBE-3509-42F4-AACD-77FC700C7846,5BF1EC30-B7E2-4121-A807-3E275E45C3BF,6E1C18E0-2715-4D94-95F7-CAEB686F549E,D78C299D-984D-424B-92CF-95EF4BE2F15B,C61C3EDB-DB36-43CB-97AD-A81B7B43CE51,DA985559-F296-4EC2-9075-52B54E0497C6,F605A4B0-9D5E-46C8-A647-326EA8922C37,09BE8CB2-C26F-458D-B586-952AFBC80173,E1EF072A-5CC1-4C9E-9D73-BEB2002A1CDF,C8760099-A2C2-48AD-A7C1-9872645E1501,4DBC3AB4-4BC5-4617-8570-F310206A4E5B,E08CE99A-B230-45F4-952F-5F33DFCC21C8,1BE59CA7-A528-4DE4-8FF7-0EB5875488DD,85475EEB-18CD-4468-9B37-8689753280C1,9DD23979-EE15-49AA-9E47-9B6A2D324394,5DD7A882-56C3-46E0-A9F9-4B1BDB424941,13A26606-6F1A-4A4A-B236-A34E43368854,FC93CCD0-A8A7-45A4-99B5-DD663BC84C25,887F2022-E97C-4EB9-BACF-8777AEED8A17,2518ED5B-0A6B-4DD8-B1C4-911E3B3A02F9,3F2BF26B-CABE-49D6-8505-FD12DB0012F6,F1A084C6-6D2D-4060-B754-360B176C5163,6E693B7A-303F-4BCF-A6B0-2FC90B14BF78,a1a9b9f9e04e4ba99677048e60097244,824038C2-AA93-4E79-9DF4-440D8CACAF29,29E3F065-5DF3-4666-8813-055E5C9B1CE0,FF28F78E-F52F-4CB2-AEEA-308340845D49,980939C9-E5DE-47E3-BF20-66DD116B7EFC,201B47D4-2E1B-4B7B-9A96-CA17E529B65D,C929477C-93EA-4833-B59C-11A1BBC20DAD,7803E2FE-05A6-48AE-BACD-220C1318CE58,B1FAD843-E594-4198-BE04-742F94458DD8,C6018F65-475C-4668-A443-0443EA3EC101,F2EBCABF-1883-492B-95C8-1587C9308398,3CF77CA9-7890-4833-B88C-D1F31DEBA723,36AB8D89-0DBB-4142-A8C4-436183A1B0D8,9557C1BD-1713-41A8-A3D3-B82C4ABE4DF5,66037AD5-198A-4230-9C8E-95854ABD4F4E,64BA7741-D802-4F7B-88A3-205F5762EA1D,747735D8-D79D-4987-AA4D-C02B4B715B12,3D16BF23-CB9B-4CB7-B4BB-DEE929101BDA,84E9B138-ECE9-45B8-BC1B-EE4E227C7DE0,16A256C5-9886-458E-890C-8CC74033452A,DDCE6C1A-72E6-4DA5-B9A6-D8147FBAD483,708DEE0C-8D8B-40C4-B13A-E039BAC6B224,813927C5-178F-428D-8365-6841AF465B01,EFE8D604-74BB-406F-8A09-ED92FA59549B,7279466A-BDD9-4189-A607-C93547F89EF6,24DCC8C3-EE18-455B-9FFC-26A6AC6E9EBE,F8E4A55B-070B-4AE5-BF73-6AABFD7623F1,5e237240fc87420dbcd89fdaed40ffbb,0750377A-69A4-4C55-9043-CED6DC88BC8D,ACA4DD6D-2DD2-404A-8C43-84FAF776E88D,9481EB21-A1F4-4F5C-80B2-9C5B49FCFA24,7D8EE357-0951-4736-99C2-AA2BC076D770,EA312294-F848-46C7-94C0-794964FC33E4,C9EAB508-2EDC-414B-A1FB-1A5D6DDBE85F,B24C2D48-7139-4331-8B67-0EBEACEF33D2,643D68E5-43FE-4D35-912F-4D8C6B4BEEA7,F7AAAA1D-058F-4FD2-AC13-11B3FE41B692,D2939FAC-EE3C-430F-81C7-9BC82CFBA561,216E97FF-59BC-4E2B-ABF1-E7CF5FB90F16,1EA2FA93-89EA-45CC-B187-7577F42A895F,DB684936-2289-4B90-ADC5-0DE0C281F7D9,b0b05f7bda7345809f2491b3ba7aca0b,8578AB2E-D4BF-43E3-B95C-A3E29043042A,394edf617cb14fdeaca257290692494f,27880270-65E3-45C5-8FB0-2738EEC24572,5FEADCE3-3347-4237-999F-70C50A6FFABB,41A9178A-2303-4AC7-B0DE-686D5822E640,C86F03A8-9D9B-4772-B5AB-A63EABB25C03,f88d95d953794817b018ac3c0dff0564,7d64bbad504e4ca498cb2ef9dc18ba73,6E586A1E-62A3-433F-921A-191D65E5ED15,3720102A-B0A0-4AD6-AE80-469C1227DF34,224D083F-76BA-49E8-A70B-0F1C56F67E78,463F5897-AE00-4F15-B89B-8CD87A189021,4064E897-99E0-49A1-922B-C7A66D342D94,6A00BFD2-0125-47AA-BA3F-283E4BA0AD9D,08C3AAA3-55DD-44E9-AA72-CB05F90A8359,A4D7D171-B462-42F9-B240-0CC5B496C8C7,635AACA2-98BE-43A7-A3F1-81ED4378BEAD,D8AE2EEB-5770-4066-BF8F-ECF6AF6836D0,D320D101-FF44-4EFE-96B1-8B0AA36FE10B',
@branch=N'5D2C27CB-5C4C-4830-991B-FF1A07E8E763,90B3EA6E-12E4-41B0-9B99-582C4B87FE49,DCE84655-784C-490C-BDAC-4AD0E9E72DB2,3502D1DE-C8BE-4D66-86EF-162BF82E8C75,8C605309-BE36-4942-AFE2-9ED64FFF99DD,C701B8F9-E6B1-4173-B930-B729E6419609,9AC42A94-C5A4-4DF9-AC6B-267B0BEFB4B7,9A05388A-5F42-4465-8E96-11A391B6FAE9,AEBD0297-5F6E-4485-B7DF-34DF466B8AA8,A0288389-2F1F-4C3A-BC0A-8E31EB42DDF6,673F6892-1449-46CD-9743-C2E4B73E7DF6,142F3C54-A02B-4A71-8648-4C9A41E199B1,E42F3139-6968-48B6-B54B-B55F9EEC3463,F38018EC-E842-435F-9617-6353D94EF32D,24522F6B-A264-4243-9405-1F5730AC9242,DEF42CF3-AB19-4281-9D2A-7C24F22AB334,BA59752E-E13B-469B-8201-7B6A148BF1DD,EF79C9D1-88C4-4449-82A7-AFE4460BE4D7,0CE7FC9C-8D96-47CF-8073-266B6C843FEA,455E1506-0CA7-4D44-AE26-2E67DC6F4B54,ECA11890-27BD-4A2B-AFDE-BFD376D39232',
@region=N'(Empty),AA23C861526E493D9FCBF85FE941F6AB,88AF658A345E4AF78A52CE971DC98B2F,FCBD72CE-3ADA-442E-98F5-53914F406BB5,1C2FBC20C0B345388ADF756B60FB5199,7D9BE394ED344EAAB9240F44644CE7E2',
@channel=N'(Empty),DA00B52E-C4BD-45D1-8C13-1505E282764C,36BF5535-9E81-43B0-889A-5775340171D5'


create table #branch
(
branch_id varchar (50) collate DATABASE_DEFAULT
)
INSERT into #branch
select * from mcc_fn_splitMVParam(@branch,',')

create table #region
(
region_id varchar (50) collate DATABASE_DEFAULT
)
INSERT into #region
select * from mcc_fn_splitMVParam(@region,',')
INSERT into #region
select '' where exists (select 1 from #region where region_id = '(Empty)')

create table #channel
(
channel_id varchar (50) collate DATABASE_DEFAULT
)
INSERT into #channel
select * from mcc_fn_splitMVParam(@channel,',')
INSERT into #channel
select '' where exists (select 1 from #channel where channel_id = '(Empty)')

create table #salesman
(
salesman_id varchar (50) collate DATABASE_DEFAULT
)
INSERT INTO #salesman 
exec sp_rpt_split_salesman_value @salesman
--select * from mcc_fn_splitMVParam(@salesman,',')

create table #sales_type
(
sales_type varchar (50) collate DATABASE_DEFAULT
)
INSERT INTO #sales_type 
select * from mcc_fn_splitMVParam(@sales_type,',')

create table #item
(
item_id varchar (50) collate DATABASE_DEFAULT
)
INSERT INTO #item select * from mcc_fn_splitMVParam(@item_id,',')

BEGIN

-- Pull Invoice data
WITH Sales
AS (
	SELECT b.branch_id
		,b.branch_code
		,h.customer_id
		,c.customer_name
		,h.invoice_no
		,mr.region_code
		,ch.channel_code
		,STUFF((
				SELECT ', ' + pc.product_category_code
				FROM mst_chain_product_category cpc
				INNER JOIN mst_product_category pc ON cpc.product_category_id = pc.product_category_id
				WHERE c.chain_id = cpc.chain_id
				ORDER BY pc.product_category_code
				FOR XML PATH('')
					,TYPE
				).value('.', 'varchar(max)'), 1, 1, '') AS product_category
		,convert(VARCHAR, h.invoice_date, 103) AS invoice_date
		,m.member_cd
		,m.first_name
		,h.currency_value AS amount
		,m.member_id
		,c.customer_type
		,'Sales' AS sales_type
		,c.customer_code
		,mh.txn_id
		,CONVERT(date, h.invoice_date) as sorting_date
	FROM txn_invoice_history_header h
	INNER JOIN txn_sales_order_header sh ON sh.ord_hdr_id = h.sales_order_id
	INNER JOIN txn_mobile_header mh ON mh.txn_id = sh.mobile_hdr_id
	INNER JOIN customer c ON h.customer_id = c.customer_id
	INNER JOIN mst_branch b ON h.branch_id = b.branch_id
	INNER JOIN member m ON h.member_id = m.member_id
	INNER JOIN txn_invoice_history_detail d ON h.invoice_id = d.invoice_id
	LEFT JOIN mst_customer_contact_address cca ON c.customer_id = cca.customer_id
		AND cca.address_type = 'Contact'
	LEFT JOIN mst_region mr ON mr.region_id = cca.region_id
	LEFT JOIN channel ch ON c.channel_id = ch.channel_id
	WHERE EXISTS (
			SELECT 1
			FROM #branch tb
			WHERE b.branch_id = tb.branch_id
			)
		AND convert(date, h.invoice_date) BETWEEN @start_date
			AND @end_date
		AND EXISTS (
			SELECT 1
			FROM #salesman ts
			WHERE ts.salesman_id = m.member_id
			)
		AND EXISTS (
			SELECT 1
			FROM #sales_type tst
			WHERE tst.sales_type = c.customer_type
			)
		AND EXISTS (
			SELECT 1
			FROM #item ti
			WHERE ti.item_id = d.item_id
			)
		AND EXISTS (
			SELECT 1
			FROM mst_customer_contact_address ca
			INNER JOIN #region r ON isnull(ca.region_id, '(Empty)') = r.region_id
			WHERE ca.address_type = 'Contact'
				AND c.customer_id = ca.customer_id
			)
		AND EXISTS (
			SELECT 1
			FROM #channel ch
			WHERE isnull(ch.channel_id, '(Empty)') = c.channel_id
			)
		AND h.tenant_id = @tenant_id
		AND h.invoice_status <> 'Cancelled' AND h.invoice_status <> 'Rejected'
	GROUP BY mh.txn_id
		,b.branch_id
		,b.branch_code
		,h.customer_id
		,c.customer_name
		,h.invoice_no
		,mr.region_code
		,ch.channel_code
		,convert(VARCHAR, h.invoice_date, 103)
		,m.member_cd
		,m.first_name
		,h.currency_value
		,m.member_id
		,c.customer_type
		,c.customer_code
		,c.chain_id
		,CONVERT(date, h.invoice_date)
	)

-- Pull Credit Note data
	,SalesReturn
AS (
	SELECT b.branch_id
		,b.branch_code
		,h.customer_id
		,c.customer_name
		,h.credit_note_no
		,mr.region_code
		,ch.channel_code
		,STUFF((
				SELECT ', ' + pc.product_category_code
				FROM mst_chain_product_category cpc
				INNER JOIN mst_product_category pc ON cpc.product_category_id = pc.product_category_id
				WHERE c.chain_id = cpc.chain_id
				ORDER BY pc.product_category_code
				FOR XML PATH('')
					,TYPE
				).value('.', 'varchar(max)'), 1, 1, '') AS product_category
		,convert(VARCHAR, h.credit_note_date, 103) AS return_date
		,m.member_cd
		,m.first_name
		,h.currency_value AS amount
		,m.member_id
		,c.customer_type
		,'Return' AS sales_type
		,c.customer_code
		,mh.txn_id
		,CONVERT(date, h.credit_note_date) as sorting_date
	FROM txn_credit_note_history_header h
	INNER JOIN txn_mobile_header mh ON mh.txn_id = h.txn_id
	INNER JOIN customer c ON h.customer_id = c.customer_id
	INNER JOIN mst_branch b ON c.branch_id = b.branch_id
	INNER JOIN member m ON h.member_id = m.member_id
	INNER JOIN txn_credit_note_history_detail d ON h.credit_note_header_id = d.credit_note_id
	INNER JOIN product_item i ON d.item_id = i.item_id
	LEFT JOIN mst_product_uom u1 ON d.uom1_id = u1.product_uom_id
	LEFT JOIN mst_product_uom u2 ON d.uom2_id = u2.product_uom_id
	LEFT JOIN mst_product_uom u3 ON d.uom3_id = u3.product_uom_id
	LEFT JOIN mst_condition co ON d.condition = co.condition_id
	LEFT JOIN mst_reason r ON d.reason_id = r.reason_id
	LEFT JOIN warehouse w ON d.warehouse_id = w.warehouse_id
	LEFT JOIN mst_customer_contact_address cca ON c.customer_id = cca.customer_id
		AND cca.address_type = 'Contact'
	LEFT JOIN mst_region mr ON mr.region_id = cca.region_id
	LEFT JOIN channel ch ON c.channel_id = ch.channel_id
	WHERE EXISTS (
			SELECT 1
			FROM #branch tb
			WHERE b.branch_id = tb.branch_id
			)
		AND convert(date, h.credit_note_date) BETWEEN @start_date
			AND @end_date
		AND EXISTS (
			SELECT 1
			FROM #salesman ts
			WHERE ts.salesman_id = m.member_id
			)
		AND EXISTS (
			SELECT 1
			FROM #sales_type tst
			WHERE tst.sales_type = c.customer_type
			)
		AND EXISTS (
			SELECT 1
			FROM #item ti
			WHERE ti.item_id = d.item_id
			)
		AND EXISTS (
			SELECT 1
			FROM mst_customer_contact_address ca
			INNER JOIN #region r ON isnull(ca.region_id, '(Empty)') = r.region_id
			WHERE ca.address_type = 'Contact'
				AND c.customer_id = ca.customer_id
			)
		AND EXISTS (
			SELECT 1
			FROM #channel ch
			WHERE isnull(ch.channel_id, '(Empty)') = c.channel_id
			)
		AND h.tenant_id = @tenant_id
		AND h.STATUS <> 'Cancelled' and h.status <> 'Rejected'
	GROUP BY mh.txn_id
		,b.branch_id
		,b.branch_code
		,h.customer_id
		,c.customer_name
		,h.currency_value
		,h.credit_note_no
		,mr.region_code
		,ch.channel_code
		,convert(VARCHAR, h.credit_note_date, 103)
		,m.member_cd
		,m.first_name
		,m.member_id
		,c.customer_type
		,c.customer_code
		,c.chain_id
		,CONVERT(date, h.credit_note_date)
	)


-- Join Sales data and Sales Return data
	,CombinedData
AS (
	SELECT *
	FROM Sales
	
	UNION ALL
	
	SELECT *
	FROM SalesReturn
	)

-- pull record from CombinedData and aggregate the invoice_no that has similar general info (branch, customer, region, channel...)
	select sum(total) as  Total_salesVaule from (
	SELECT MAX(txn_id) as txn_id 
	,MAX(branch_id) AS branch_id
	,MAX(branch_code) AS branch_code
	,MAX(customer_id) AS customer_id
	,MAX(customer_name) AS customer_name
	,STRING_AGG(invoice_no, '/ ')WITHIN GROUP (ORDER BY invoice_no desc) AS invoice_no
	,MAX(region_code) AS region
	,MAX(channel_code) AS channel
	,MAX(product_category) AS product_category
	,MAX(invoice_date) AS invoice_date
	,MAX(sorting_date) AS sorting_date
	,MAX(member_cd) AS member_cd
	,MAX(first_name) AS first_name
	,MAX(member_id) AS member_id
	,MAX(customer_type) AS customer_type
	,sum(CASE 
			WHEN customer_type = 'Cash Outlet'
				AND sales_type = 'Sales'
				THEN amount
			ELSE 0.00
			END) AS cash_sales
	,sum(CASE 
			WHEN customer_type = 'Credit Outlet'
				AND sales_type = 'Sales'
				THEN amount
			ELSE 0.00
			END) AS credit_sales
	,sum(CASE 
			WHEN sales_type = 'Return'
				THEN amount
			ELSE 0.00
			END) * - 1 AS return_sales
	,sum(CASE 
			WHEN customer_type = 'Cash Outlet'
				AND sales_type = 'Sales'
				THEN amount
			WHEN customer_type = 'Credit Outlet'
				AND sales_type = 'Sales'
				THEN amount
			ELSE 0.00
			END) - sum(CASE 
			WHEN sales_type = 'Return'
				THEN amount
			ELSE 0.00
			END) AS total
	,customer_code
FROM CombinedData
GROUP BY txn_id
	,branch_id
	,branch_code
	,customer_id
	,customer_name
	,region_code
	,channel_code
	,product_category
	,invoice_date
	,member_cd
	,first_name
	,member_id
	,customer_type
	,customer_code
	,sorting_date
--ORDER BY sorting_date
--	,invoice_date
--	,branch_code
--	,region_code
--	,member_cd
--	,txn_id
--	,first_name
--	,customer_code
--	,customer_name
--	,channel_code
--	,customer_type

)x  

END

 
--drop table #region
--drop table #channel