October 22, 2018 at 5:11 pm
I have a table which is an output of full outer join:
create table temp( order_id1 varchar(20),line_id1 varchar(20),type1 varchar(20),date11 date,amount1 decimal(5,2), order2 varchar(20),line2 varchar(20),type2 varchar(20),date2 date,amount2 decimal(5,2) )
insert into temp select '1','11','Payment','1/21/18',100,'1','11','Payment','1/22/18',100 insert into temp select '1','11','Customer Refund','1/21/18',-54,'1','11','Payment','1/22/18',-54 insert into temp select '1','11','Customer Refund','1/31/18',-46,null,null,null,null,null insert into temp select null,null,null,null,null,'1','11','Customer Refund','1/31/18',-45.5
The above table yields 4 records. I need to convert those 4 records into 3 records as below:
select '1' as order_id,'11' as line_id,'Payment' as type,'1/21/18' as date1,'1/22/18' as date2, 100 as amount1, 100 as amount2 union all select '1' as order_id,'11' as line_id,'Customer Refund' as type,'1/21/18' as date1,'1/22/18' as date2, -54 as amount1, -54 as amount2 union all select '1' as order_id,'11' as line_id,'Customer Refund' as type,'1/31/18' as date1,'1/31/18' as date2, -46 as amount1, -45.5 as amount2
Can someone please help? I am running out of ideas 🙁
October 22, 2018 at 5:28 pm
rockstar283 - Monday, October 22, 2018 5:11 PMI have a table which is an output of full outer join:
create table temp( order_id1 varchar(20),line_id1 varchar(20),type1 varchar(20),date11 date,amount1 decimal(5,2), order2 varchar(20),line2 varchar(20),type2 varchar(20),date2 date,amount2 decimal(5,2) )insert into temp select '1','11','Payment','1/21/18',100,'1','11','Payment','1/22/18',100 insert into temp select '1','11','Customer Refund','1/21/18',-54,'1','11','Payment','1/22/18',-54 insert into temp select '1','11','Customer Refund','1/31/18',-46,null,null,null,null,null insert into temp select null,null,null,null,null,'1','11','Customer Refund','1/31/18',-45.5The above table yields 4 records. I need to convert those 4 records into 3 records as below:
select '1' as order_id,'11' as line_id,'Payment' as type,'1/21/18' as date1,'1/22/18' as date2, 100 as amount1, 100 as amount2 union all select '1' as order_id,'11' as line_id,'Customer Refund' as type,'1/21/18' as date1,'1/22/18' as date2, -54 as amount1, -54 as amount2 union all select '1' as order_id,'11' as line_id,'Customer Refund' as type,'1/31/18' as date1,'1/31/18' as date2, -46 as amount1, -45.5 as amount2Can someone please help? I am running out of ideas 🙁
Is your screen 20 feet wide? Here are those queries again, reformatted with a few tweaks:
DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp
(
order_id1 VARCHAR(20),
line_id1 VARCHAR(20),
type1 VARCHAR(20),
date11 DATE,
amount1 DECIMAL(5, 2),
order2 VARCHAR(20),
line2 VARCHAR(20),
type2 VARCHAR(20),
date2 DATE,
amount2 DECIMAL(5, 2)
);
INSERT #temp
(
order_id1,
line_id1,
type1,
date11,
amount1,
order2,
line2,
type2,
date2,
amount2
)
VALUES
('1', '11', 'Payment', '1/21/18', 100, '1', '11', 'Payment', '1/22/18', 100),
('1', '11', 'Customer Refund', '1/21/18', -54, '1', '11', 'Payment', '1/22/18', -54),
('1', '11', 'Customer Refund', '1/31/18', -46, NULL, NULL, NULL, NULL, NULL),
(NULL, NULL, NULL, NULL, NULL, '1', '11', 'Customer Refund', '1/31/18', -45.5);
SELECT *
FROM #temp t;
SELECT order_id = '1',
line_id = '11',
type = 'Payment',
date1 = '1/21/18',
date2 = '1/22/18',
amount1 = 100,
amount2 = 100
UNION ALL
SELECT order_id = '1',
line_id = '11',
type = 'Customer Refund',
date1 = '1/21/18',
date2 = '1/22/18',
amount1 = -54,
amount2 = -54
UNION ALL
SELECT order_id = '1',
line_id = '11',
type = 'Customer Refund',
date1 = '1/31/18',
date2 = '1/31/18',
amount1 = -46,
amount2 = -45.5;
There does not appear to be any link between the refund row and the OrderId to which it applies. Unless there is only a single OrderId in your results, this looks impossible.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 22, 2018 at 5:43 pm
Should not be to hard to format the sql but...
This gives the desire result for the supplied data - as no rules were defined I assumed the following
Link is between rows with values on the order 1 columns and nulls on the order 2 columns
linking onto rows with the reverse rule and where order 1 details are same as order 2 detail
Also assumes that only 1 paired row will exist.
--I have a table which is an output of full outer join:
if object_id('temp') is not null
drop table temp
create table temp
( order_id1 varchar(20)
, line_id1 varchar(20)
, type1 varchar(20)
, date11 date
, amount1 decimal(5, 2)
, order2 varchar(20)
, line2 varchar(20)
, type2 varchar(20)
, date2 date
, amount2 decimal(5, 2)
)
-- noformat
insert into temp select '1', '11', 'Payment', '1/21/18', 100, '1', '11', 'Payment', '1/22/18', 100
insert into temp select '1', '11', 'Customer Refund' , '1/21/18' , -54 , '1' , '11' , 'Payment' , '1/22/18' , -54
insert into temp select '1' , '11' , 'Customer Refund' , '1/31/18' , -46 , null , null , null , null , null
insert into temp select null , null , null , null , null , '1' , '11' , 'Customer Refund' , '1/31/18' , -45.5
-- endnoformat
--The above table yields 4 records. I need to convert those 4 records into 3 records as below:
select *
from temp
select '1' as order_id
, '11' as line_id
, 'Payment' as type
, '1/21/18' as date1
, '1/22/18' as date2
, 100 as amount1
, 100 as amount2
union all
select '1' as order_id
, '11' as line_id
, 'Customer Refund' as type
, '1/21/18' as date1
, '1/22/18' as date2
, -54 as amount1
, -54 as amount2
union all
select '1' as order_id
, '11' as line_id
, 'Customer Refund' as type
, '1/31/18' as date1
, '1/31/18' as date2
, -46 as amount1
, -45.5 as amount2
select t1.order_id1 as order_id
, t1.line_id1 as line_id
, t1.type1 as type
, t1.date11 as date1
, coalesce(t1.date2, t2.date2) as date2
-- on my system to get that output I need to cast to varchar with a particular format
--, convert(varchar(10), t1.date11, 1) as date1
--, convert(varchar(10), coalesce(t1.date2, t2.date2), 1) as date2
, convert(int, t1.amount1) as amount1
, convert(decimal(5, 1), coalesce(t1.amount2, t2.amount2)) as amount2
from temp t1
outer apply (select top 1 *
from temp t2
where t2.order2 = t1.order_id1
and t2.line2 = t1.line_id1
and t2.type2 = t1.type1
and t2.order_id1 is null
and t2.line_id1 is null
and t2.date11 is null
and t2.amount1 is null
) t2
where t1.order_id1 is not null
and t1.line_id1 is not null
and t1.date11 is not null
and t1.amount1 is not null
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply