how to separate value columns my matching two flag

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to separate value columns my matching two flag

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2012-04-10 : 03:28:04

Hi SQL gurus,

I have a table below;


FLAG1   | FLAG2    | YEAR   | PERIOD | AMOUNT 
---------------------------------------------
aaa     | bbb      | 2012   | 01     | 100
aaa     | ccc      | 2012   | 02     | 50
bbb     | aaa      | 2012   | 01     | -100

How can I transform the source into the view below?


INBOUND   | AMOUNT    | OUTBOUND   | AMOUNT | YEAR | PERIOD | DIFF
------------------------------------------------------------------ 
aaa       | 100       | bbb        | -100   | 2012 | 01     | 0
aaa       | 50        | ccc        | 0      | 2012 | 02     | 50

Kindly advise. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-10 : 04:30:57

[code]DECLARE @Sample TABLE
(
Flag1 CHAR(3) NOT NULL,
Flag2 CHAR(3) NOT NULL,
[Year] SMALLINT NOT NULL,
Period CHAR(2) NOT NULL,
Amount INT NOT NULL
)

INSERT @Sample
VALUES ('aaa', 'bbb', 2012, '01', 100),
('aaa', 'ccc', 2012, '02', 50),
('bbb', 'aaa', 2012, '01', -100)

-- Solution by SwePeso
;WITH cteSource(Inbound, InAmount, Outbound, OutAmount, [Year], Period)
AS (
SELECT CASE
WHEN Amount >= 0 THEN Flag1
ELSE Flag2
END AS Inbound,
CASE
WHEN Amount >= 0 THEN Amount
ELSE 0
END AS InAmount,
CASE
WHEN Amount >= 0 THEN Flag2
ELSE Flag1
END AS Outbound,
CASE
WHEN Amount >= 0 THEN 0
ELSE Amount
END AS OutAmount,
[Year],
Period
FROM @Sample
)
SELECT Inbound,
SUM(InAmount) AS InAmount,
Outbound,
SUM(OutAmount) AS OutAmount,
[Year],
Period,
SUM(InAmount) + SUM(OutAmount) AS Diff
FROM cteSource
GROUP BY Inbound,
Outbound,
[Year],
Period[/code]

N 56°04'39.26"
E 12°55'05.63"

yingchai
Starting Member

33 Posts

Posted - 2012-05-04 : 04:40:44

Hi SwePeso,

I have a new requirements here to transform the table and need your advise on this.


FLAG1   | FLAG2    | YEAR   | PERIOD | DATA_TYPE | AMOUNT 
---------------------------------------------------------
aaa     | bbb      | 2012   | 01     | BASE      | 100
aaa     | ccc      | 2012   | 02     | BASE      | 50
bbb     | aaa      | 2012   | 01     | BASE      | -100
aaa     | bbb      | 2012   | 01     | INTERCO   | 90
aaa     | ccc      | 2012   | 02     | INTERCO   | 40
bbb     | aaa      | 2012   | 01     | INTERCO   | -70
ccc     | aaa      | 2012   | 02     | INTERCO   | -60

How can I transform the source into the view below?


INBOUND   | BASE_AMOUNT | INTERCO_AMOUNT | OUTBOUND   | BASE_AMOUNT | INTERCO_AMOUNT | YEAR | PERIOD 
----------------------------------------------------------------------------------------------------
aaa       | 100         | 90             | bbb        | -100        | -70            | 2012 | 01     
aaa       | 50          | 40             | ccc        | 0           | -60            | 2012 | 02

Below is the DDL and sample data for this:

DECLARE @Sample TABLE
(
Flag1 CHAR(3) NOT NULL,
Flag2 CHAR(3) NOT NULL,
[Year] SMALLINT NOT NULL,
Period CHAR(2) NOT NULL,
DATA_TYPE CHAR(8) NOT NULL,
Amount INT NOT NULL
)

INSERT @Sample
VALUES ('aaa', 'bbb', 2012, '01', 'BASE', 100),
('aaa', 'ccc', 2012, '02', 'BASE', 50),
('bbb', 'aaa', 2012, '01', 'BASE', -100),
('aaa', 'bbb', 2012, '01', 'INTERCO', 90),
('aaa', 'ccc', 2012, '02', 'INTERCO', 40),
('bbb', 'aaa', 2012, '01', 'INTERCO', -70),
('ccc', 'aaa', 2012, '02', 'INTERCO', -60)

Kindly advise. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-08 : 11:20:42

Something like this?

DECLARE @Sample TABLE
(
Flag1 CHAR(3) NOT NULL,
Flag2 CHAR(3) NOT NULL,
[Year] SMALLINT NOT NULL,
Period CHAR(2) NOT NULL,
DATA_TYPE CHAR(8) NOT NULL,
Amount INT NOT NULL
)

INSERT @Sample
VALUES ('aaa', 'bbb', 2012, '01', 'BASE', 100),
('aaa', 'ccc', 2012, '02', 'BASE', 50),
('bbb', 'aaa', 2012, '01', 'BASE', -100),
('aaa', 'bbb', 2012, '01', 'INTERCO', 90),
('aaa', 'ccc', 2012, '02', 'INTERCO', 40),
('bbb', 'aaa', 2012, '01', 'INTERCO', -70),
('ccc', 'aaa', 2012, '02', 'INTERCO', -60)

-- SwePeso
;WITH cteSource(Inbound, InBaseAmount, InIntercoAmount, Outbound, OutBaseAmount, OutIntercoAmount, [Year], Period)
AS (
	SELECT	CASE WHEN Amount >= 0 THEN Flag1 ELSE Flag2 END AS Inbound,
		CASE WHEN Data_Type = 'BASE' AND Amount >= 0 THEN Amount ELSE 0 END AS InBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Amount >= 0 THEN Amount ELSE 0 END AS InIntercoAmount,
		CASE WHEN Amount >= 0 THEN Flag2 ELSE Flag1 END AS Outbound,
		CASE WHEN Data_Type = 'BASE' AND Amount < 0 THEN Amount ELSE 0 END AS OutBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Amount < 0 THEN Amount ELSE 0 END AS OutIntercoAmount,
		[Year],
		Period
	FROM	@Sample
)
SELECT		Inbound,
		SUM(InBaseAmount) AS InBaseAmount,
		SUM(InIntercoAmount) AS InIntercoAmount,
		Outbound,
		SUM(OutBaseAmount) AS OutBaseAmount,
		SUM(OutIntercoAmount) AS OutIntercoAmount,
		[Year],
		Period
FROM		cteSource
GROUP BY	Inbound,
		Outbound,
		[Year],
		Period

N 56°04'39.26"
E 12°55'05.63"

yingchai
Starting Member

33 Posts

Posted - 2012-05-08 : 23:32:46

quote:

Originally posted by SwePeso

Something like this?

DECLARE @Sample TABLE
(
Flag1 CHAR(3) NOT NULL,
Flag2 CHAR(3) NOT NULL,
[Year] SMALLINT NOT NULL,
Period CHAR(2) NOT NULL,
DATA_TYPE CHAR(8) NOT NULL,
Amount INT NOT NULL
)

INSERT @Sample
VALUES ('aaa', 'bbb', 2012, '01', 'BASE', 100),
('aaa', 'ccc', 2012, '02', 'BASE', 50),
('bbb', 'aaa', 2012, '01', 'BASE', -100),
('aaa', 'bbb', 2012, '01', 'INTERCO', 90),
('aaa', 'ccc', 2012, '02', 'INTERCO', 40),
('bbb', 'aaa', 2012, '01', 'INTERCO', -70),
('ccc', 'aaa', 2012, '02', 'INTERCO', -60)

-- SwePeso
;WITH cteSource(Inbound, InBaseAmount, InIntercoAmount, Outbound, OutBaseAmount, OutIntercoAmount, [Year], Period)
AS (
	SELECT	CASE WHEN Amount >= 0 THEN Flag1 ELSE Flag2 END AS Inbound,
		CASE WHEN Data_Type = 'BASE' AND Amount >= 0 THEN Amount ELSE 0 END AS InBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Amount >= 0 THEN Amount ELSE 0 END AS InIntercoAmount,
		CASE WHEN Amount >= 0 THEN Flag2 ELSE Flag1 END AS Outbound,
		CASE WHEN Data_Type = 'BASE' AND Amount < 0 THEN Amount ELSE 0 END AS OutBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Amount < 0 THEN Amount ELSE 0 END AS OutIntercoAmount,
		[Year],
		Period
	FROM	@Sample
)
SELECT		Inbound,
		SUM(InBaseAmount) AS InBaseAmount,
		SUM(InIntercoAmount) AS InIntercoAmount,
		Outbound,
		SUM(OutBaseAmount) AS OutBaseAmount,
		SUM(OutIntercoAmount) AS OutIntercoAmount,
		[Year],
		Period
FROM		cteSource
GROUP BY	Inbound,
		Outbound,
		[Year],
		Period

N 56°04'39.26"
E 12°55'05.63"

Hi SwePeso,

I forgotten to mention that the values for INTERCO data_type can be either positive and negative. So, if I were to change the amount to become all positive, then the result might not look correct.

Example from my source table below:


FLAG1   | FLAG2    | YEAR   | PERIOD | DATA_TYPE | AMOUNT 
---------------------------------------------------------
aaa     | bbb      | 2012   | 01     | BASE      | 100
aaa     | ccc      | 2012   | 02     | BASE      | 50
bbb     | aaa      | 2012   | 01     | BASE      | 80
aaa     | bbb      | 2012   | 01     | INTERCO   | 90
aaa     | ccc      | 2012   | 02     | INTERCO   | 40
bbb     | aaa      | 2012   | 01     | INTERCO   | 70
ccc     | aaa      | 2012   | 02     | INTERCO   | 60

The transformation will become into this view:


INBOUND   | BASE_AMOUNT | INTERCO_AMOUNT | OUTBOUND   | BASE_AMOUNT | INTERCO_AMOUNT | YEAR | PERIOD 
----------------------------------------------------------------------------------------------------
aaa       | 100         | 90             | bbb        | 80          | 70             | 2012 | 01     
aaa       | 50          | 40             | ccc        | 0           | 60             | 2012 | 02

Possible to transform that? Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-09 : 01:38:49

[code]
; with Flags as
(
select Flag1, Flag2
from
(
select Flag1, Flag2, row_no = row_number() over (partition by Flags order by Flags)
from
(
select Flag1, Flag2, Flags = case when Flag1 < Flag2 then Flag1 + Flag2 else Flag2 + Flag1 end
from @Sample
where DATA_TYPE = 'BASE'
) f
) f
where row_no = 1
)
select INBOUND = f.Flag1,
BASE_AMOUNT = a.Amount,
INTERCO_AMOUNT = b.Amount,
OUTBOUND = f.Flag2,
BASE_AMOUNT = c.Amount,
INTERCO_AMOUNT = d.Amount,
[YEAR] = a.[Year],
PERIOD = a.Period
from Flags f
inner join @Sample a on f.Flag1 = a.Flag1
and f.Flag2 = a.Flag2
left join @Sample b on f.Flag1 = b.Flag1
and f.Flag2 = b.Flag2
and b.DATA_TYPE = 'INTERCO'
left join @Sample c on f.Flag1 = c.Flag2
and f.Flag2 = c.Flag1
and c.DATA_TYPE = 'BASE'
left join @Sample d on f.Flag1 = d.Flag2
and f.Flag2 = d.Flag1
and d.DATA_TYPE = 'INTERCO'
where a.DATA_TYPE = 'BASE'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-09 : 03:06:27

The the question remain how you distinguish between Inbound and Outbound?

If Flag1 < Flag2 then it is Inbound? Otherwise Outbound?

;WITH cteSource(Inbound, InBaseAmount, InIntercoAmount, Outbound, OutBaseAmount, OutIntercoAmount, [Year], Period)
AS (
	SELECT	CASE WHEN Flag1 <= Flag2 THEN Flag1 ELSE Flag2 END AS Inbound,
		CASE WHEN Data_Type = 'BASE' AND Flag1 <= Flag2 THEN Amount ELSE 0 END AS InBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Flag1 <= Flag2 THEN Amount ELSE 0 END AS InIntercoAmount,
		CASE WHEN Flag1 > Flag2 THEN Flag1 ELSE Flag2 END AS Outbound,
		CASE WHEN Data_Type = 'BASE' AND Flag1 > Flag2 THEN Amount ELSE 0 END AS OutBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Flag1 > Flag2 THEN Amount ELSE 0 END AS OutIntercoAmount,
		[Year],
		Period
	FROM	@Sample
)
SELECT		Inbound,
		SUM(InBaseAmount) AS InBaseAmount,
		SUM(InIntercoAmount) AS InIntercoAmount,
		Outbound,
		SUM(OutBaseAmount) AS OutBaseAmount,
		SUM(OutIntercoAmount) AS OutIntercoAmount,
		[Year],
		Period
FROM		cteSource
GROUP BY	Inbound,
		Outbound,
		[Year],
		Period

N 56°04'39.26"
E 12°55'05.63"

yingchai
Starting Member

33 Posts

Posted - 2012-05-09 : 03:28:23

quote:

Originally posted by khtan


; with Flags as
(
	select	Flag1, Flag2
	from
	(
		select	Flag1, Flag2, row_no = row_number() over (partition by Flags order by Flags)
		from
		(
			select	Flag1, Flag2, Flags = case when Flag1 < Flag2 then Flag1 + Flag2 else Flag2 + Flag1 end
			from	@Sample
			where	DATA_TYPE = 'BASE'
		) f
	) f
	where	row_no	= 1
)
select	INBOUND		= f.Flag1,
	BASE_AMOUNT	= a.Amount,
	INTERCO_AMOUNT	= b.Amount,
	OUTBOUND	= f.Flag2,
	BASE_AMOUNT	= c.Amount,
	INTERCO_AMOUNT	= d.Amount,
	[YEAR]		= a.[Year],
	PERIOD		= a.Period
from	Flags f
	inner join @Sample a	on	f.Flag1		= a.Flag1
				and	f.Flag2		= a.Flag2
	left join @Sample b	on	f.Flag1		= b.Flag1
				and	f.Flag2		= b.Flag2
				and	b.DATA_TYPE	= 'INTERCO'
	left join @Sample c	on	f.Flag1		= c.Flag2
				and	f.Flag2		= c.Flag1
				and	c.DATA_TYPE	= 'BASE'
	left join @Sample d	on	f.Flag1		= d.Flag2
				and	f.Flag2		= d.Flag1
				and	d.DATA_TYPE	= 'INTERCO'
where	a.DATA_TYPE	= 'BASE'

KH
[spoiler]Time is always against us[/spoiler]

I had tested your script and found two scenario here.

Scenario 1:
If I add a pair of flag1 & flag2 of 'aaa' and 'ddd' for BASE data_type, the result is fine.


INSERT @Sample
VALUES ('aaa', 'bbb', 2012, '01', 'BASE', 100),
('aaa', 'ccc', 2012, '02', 'BASE', 50),
('bbb', 'aaa', 2012, '01', 'BASE', -100),
('aaa', 'bbb', 2012, '01', 'INTERCO', 90),
('aaa', 'ccc', 2012, '02', 'INTERCO', 40),
('bbb', 'aaa', 2012, '01', 'INTERCO', -70),
('ccc', 'aaa', 2012, '02', 'INTERCO', -60),
('aaa', 'ddd', 2012, '02', 'BASE', 44)

Scenario 2:
If I add a pair of flag1 & flag2 of 'aaa' and 'ddd' for INTERCO data_type, the result for Inbound & Outbound of 'aaa' and 'ddd' does not appear.


INSERT @Sample
VALUES ('aaa', 'bbb', 2012, '01', 'BASE', 100),
('aaa', 'ccc', 2012, '02', 'BASE', 50),
('bbb', 'aaa', 2012, '01', 'BASE', -100),
('aaa', 'bbb', 2012, '01', 'INTERCO', 90),
('aaa', 'ccc', 2012, '02', 'INTERCO', 40),
('bbb', 'aaa', 2012, '01', 'INTERCO', -70),
('ccc', 'aaa', 2012, '02', 'INTERCO', -60),
('aaa', 'ddd', 2012, '02', 'INTERCO', 44)

PLease advise.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-09 : 03:38:02

Of course they do...

DECLARE	@Sample TABLE
	(
		Flag1 CHAR(3) NOT NULL,
		Flag2 CHAR(3) NOT NULL,
		[Year] SMALLINT NOT NULL,
		Period CHAR(2) NOT NULL,
		DATA_TYPE CHAR(8) NOT NULL,
		Amount INT NOT NULL
	)

INSERT	@Sample
VALUES	('aaa', 'bbb', 2012, '01', 'BASE', 100),
	('aaa', 'ccc', 2012, '02', 'BASE', 50),
	('bbb', 'aaa', 2012, '01', 'BASE', -100),
	('aaa', 'bbb', 2012, '01', 'INTERCO', 90),
	('aaa', 'ccc', 2012, '02', 'INTERCO', 40),
	('bbb', 'aaa', 2012, '01', 'INTERCO', -70),
	('ccc', 'aaa', 2012, '02', 'INTERCO', -60),
	('aaa', 'ddd', 2012, '02', 'INTERCO', 44)

-- SwePeso
;WITH cteSource(Inbound, InBaseAmount, InIntercoAmount, Outbound, OutBaseAmount, OutIntercoAmount, [Year], Period)
AS (
	SELECT	CASE WHEN Flag1 <= Flag2 THEN Flag1 ELSE Flag2 END AS Inbound,
		CASE WHEN Data_Type = 'BASE' AND Flag1 <= Flag2 THEN Amount ELSE 0 END AS InBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Flag1 <= Flag2 THEN Amount ELSE 0 END AS InIntercoAmount,
		CASE WHEN Flag1 > Flag2 THEN Flag1 ELSE Flag2 END AS Outbound,
		CASE WHEN Data_Type = 'BASE' AND Flag1 > Flag2 THEN Amount ELSE 0 END AS OutBaseAmount,
		CASE WHEN Data_Type = 'INTERCO' AND Flag1 > Flag2 THEN Amount ELSE 0 END AS OutIntercoAmount,
		[Year],
		Period
	FROM	@Sample
)
SELECT		Inbound,
		SUM(InBaseAmount) AS InBaseAmount,
		SUM(InIntercoAmount) AS InIntercoAmount,
		Outbound,
		SUM(OutBaseAmount) AS OutBaseAmount,
		SUM(OutIntercoAmount) AS OutIntercoAmount,
		[Year],
		Period
FROM		cteSource
GROUP BY	Inbound,
		Outbound,
		[Year],
		Period

The result is

Inbound	InBaseAmount	InIntercoAmount	Outbound	OutBaseAmount	OutIntercoAmount	Year	Period
aaa	100		90		bbb		-100		-70			2012	01
aaa	50		40		ccc		0		-60			2012	02
aaa	0		44		ddd		0		0			2012	02

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-09 : 03:40:38

The cost for my algorithm is 0.0146456 and 1024 memory grant.
The other solution have cost 0.0455748 and 1218 memory grant.

N 56°04'39.26"
E 12°55'05.63"

yingchai
Starting Member

33 Posts

Posted - 2012-05-09 : 03:49:19

quote:
Originally posted by SwePeso

The the question remain how you distinguish between Inbound and Outbound?

If Flag1 < Flag2 then it is Inbound? Otherwise Outbound?
N 56°04'39.26"
E 12°55'05.63"

Hi SwePeso,

To answer your question earlier...
if the aaa & bbb pair appears first in FLAG1 and FLAG2 pair, then aaa is inbound, bbb is outbound.

if the bbb & aaa pair appears first in FLAG1 and FLAG2 pair, then bbb is inbound, aaa is outbound.

it all depends which flag values appear first. from my example, aaa & bbb pair appear first rather than bbb & aaa pair. therefore, aaa is inbound while bbb is outbound.

Based on your algorithm cost and memory grant comparison, which one query faster and consume less resources?

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-09 : 04:03:34

[code]
; with Flags as
(
select Flag1, Flag2
from
(
select Flag1, Flag2, row_no = row_number() over (partition by Flags order by Flags)
from
(
select Flag1, Flag2, Flags = case when Flag1 < Flag2 then Flag1 + Flag2 else Flag2 + Flag1 end
from @Sample
) f
) f
where row_no = 1
)
select INBOUND = f.Flag1,
BASE_AMOUNT = a.Amount,
INTERCO_AMOUNT = b.Amount,
OUTBOUND = f.Flag2,
BASE_AMOUNT = c.Amount,
INTERCO_AMOUNT = d.Amount,
[YEAR] = a.[Year],
PERIOD = a.Period
from Flags f
left join @Sample a on f.Flag1 = a.Flag1
and f.Flag2 = a.Flag2
and a.DATA_TYPE = 'BASE'
left join @Sample b on f.Flag1 = b.Flag1
and f.Flag2 = b.Flag2
and b.DATA_TYPE = 'INTERCO'
left join @Sample c on f.Flag1 = c.Flag2
and f.Flag2 = c.Flag1
and c.DATA_TYPE = 'BASE'
left join @Sample d on f.Flag1 = d.Flag2
and f.Flag2 = d.Flag1
and d.DATA_TYPE = 'INTERCO'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-09 : 04:05:57

Test!
Run both codes on your data to see which is fastest and uses less reads. Make use of "SET STATISTICS IO ON" and "SET STATISTICS TIME ON".

Also, if you want to be a professional programmer, there is no order in a table, unless you specify an logical order.
You cannot rely on the physical order of rows to be the same on all machines and not even between two executions on same machine due to parallellism.

So, what is the "order" of the rows in your table? What do you have to determine order of rows?

N 56°04'39.26"
E 12°55'05.63"

yingchai
Starting Member

33 Posts

Posted - 2012-05-09 : 04:53:33

What "order" do you mean? The order at the source table or the result table?

I am planning to give an "order" for the Inbound column at the result table. Will that be sufficient?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-09 : 05:40:58

Of course the order of the source table, when you talk about "first" and "last" row and which should be paired together.

N 56°04'39.26"
E 12°55'05.63"

yingchai
Starting Member

33 Posts

Posted - 2012-05-09 : 05:47:38

okay. understood.

initially, the source table (which i transformed to a view) is ordered by flag1 and DATA_TYPE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources