Self join and get unique records between a date

Chaitanya V
Chaitanya V used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial