Chaitanya V
asked on
Self join and get unique records between a date
I have my table and data as follows where I am trying to filter based on period and get results
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
CREATE TABLE testData
(
orgId int,
keyId int,
period date,
value varchar(100)
)
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
Last Comment
Pawan Kumar
What is the criteria to get records?
ASKER
I would like to get by date for each date there will be 2 entries, I would like subtract and display records uniquely
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks all
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
