We help IT Professionals succeed at work.

Self join and get unique records between a date

Chaitanya V
Chaitanya V asked
on
I have my table and data as follows where I am trying to filter based on period and get results
CREATE TABLE testData
(
    orgId int,
    keyId int,
    period date,
    value varchar(100)
)

Open in new window


I have a fiddle which is giving results but not as expected you can check fiddle here
http://sqlfiddle.com/#!6/df7cf/2
What I need is
 
            1	2001-08-01	400
            1	2001-09-01	100
            1	2001-10-01	100
            1	2001-11-01	100

Open in new window

Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
What is the criteria to get records?

Author

Commented:
I would like to get by date for each date there will be 2 entries, I would like subtract and display records uniquely
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please find the solution below. Please try and let us know in case of any issues.

CREATE TABLE testData
(
    orgId int,
    keyId int,
    period date,
    value varchar(100)
)

INSERT INTO testData values
(1,1,'2001-08-01','400'),
(1,1,'2001-09-01','400'),
(1,1,'2001-10-01','400'),
(1,1,'2001-11-01','400'),
(1,2,'2001-09-01','300'),
(1,2,'2001-10-01','300'),
(1,2,'2001-11-01','300')

Open in new window


SOLUTION

;WITH CTE1 AS
(
	SELECT
		orgId,period,MIN(value) Value, ROW_NUMBER() OVER (ORDER BY period)	rnk	
	FROM TestData
	GROUP BY orgId,period
)
SELECT orgId,period , CASE WHEN rnk > 1 THEN u.m- CAST(Value AS INT) ELSE u.m END Value
FROM CTE1 c1
CROSS JOIN
	(SELECT TOP 1 CAST(Value AS INT) m FROM CTE1 WHERE rnk = 1)u
ORDER BY period

Open in new window


Output

OUTPUT

/*------------------------
OUTPUT
------------------------*/
orgId       period     Value
----------- ---------- -----------
1           2001-08-01 400
1           2001-09-01 100
1           2001-10-01 100
1           2001-11-01 100

(4 row(s) affected)

Open in new window

Ryan ChongSoftware Team Lead
Commented:
what if you got 3 entries?
Ryan ChongSoftware Team Lead

Commented:
will this work for you?

declare  @testData table
(
    orgId int,
    keyId int,
    period date,
    value varchar(100)
)

INSERT INTO @testData values
(1,1,'2001-08-01','400'),
(1,1,'2001-09-01','400'),
(1,1,'2001-10-01','400'),
(1,1,'2001-11-01','400'),
(1,2,'2001-09-01','300'),
(1,2,'2001-10-01','300'),
(1,2,'2001-11-01','300')

;with cte as
(
	select a.orgId, a.Period, CAST(a.Value AS DECIMAL(10, 4)) value,
	ROW_NUMBER() OVER(PARTITION BY a.orgId, a.Period ORDER BY a.Period, a.keyId) SeqNo
	from @testData a
)
select a.orgId, a.Period, sum(case when a.SeqNo = 1 then a.Value else a.value *-1 end) value
from cte a
group by a.orgId, a.Period

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
You can also use below -

/*------------------------
;WITH CTE1 AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY period) rnk1 FROM 
	(
		SELECT
			orgId,period,Value, ROW_NUMBER() OVER (PARTITION BY period ORDER BY VALUE)	rnk	
		FROM TestData
	)r WHERE rnk = 1
)
SELECT c1.orgId, c1.period, CASE WHEN rnk = rnk1 THEN m ELSE m- Value END Value
FROM CTE1 c1
CROSS JOIN
	(SELECT TOP 1 CAST(Value AS INT) m FROM CTE1 WHERE rnk = 1)u
ORDER BY period
------------------------*/
orgId       period     Value
----------- ---------- -----------
1           2001-08-01 400
1           2001-09-01 100
1           2001-10-01 100
1           2001-11-01 100

(4 row(s) affected)

Open in new window

Author

Commented:
Thanks all