ranking query with nulls

Question

ranking query with nulls

rash3554

SSCommitted

Points: 1984
More actions
October 7, 2016 at 7:00 am

#400847

I have a table
create table temp(Companyid varchar(10) not null,companyname varchar(100) not null,DateFrom datetime not null, DateTo datetime null);
INSERT INTO temp values('A1','A Company','2000-04-11',null);
INSERT INTO temp values('A1','A1 Company','2000-04-11','2004-12-31');
INSERT INTO temp values('A1','A2 Company','2004-12-31',null);
This is Company names database where we Archive change in Company names. As you see from above the first record is an error.
There are many such records in the database and I want to rank them and remove them.
First we start by DateFrom ASC. We get first two records when we do this. Now I want to rank record if DateTo is Null as RankCompany=2 and if DateTo is not null then RankCompany=1,
This is my expected output
create table temp2(Companyid varchar(10) not null,companyname varchar(100) not null,DateFrom datetime not null, DateTo datetime null,RankCompany int);
INSERT INTO temp2 values('A1','A Company','2000-04-11',null,2);
INSERT INTO temp2 values('A1','A1 Company','2000-04-11','2004-12-31',1);
INSERT INTO temp2 values('A1','A2 Company','2004-12-31',null,1);
How can I do this
Thanks
M

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Your request is almost certainly possible, however your own difficulty with it hints that there may be a simpler option. What about this?

SELECT t.*

FROM #temp2 t

WHERE t.DateTo IS NULL

AND EXISTS (

SELECT 1

FROM #temp2 ti

WHERE ti.Companyid = t.Companyid

AND ti.DateFrom = t.DateFrom

AND ti.DateTo IS NOT NULL)

Change the SELECT to a DELETE and you're done.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

Quick suggestion

😎

USE TEEST;

GO

IF OBJECT_ID(N'dbo.temp') IS NOT NULL DROP TABLE dbo.temp;

create table dbo.temp(Companyid varchar(10) not null,companyname varchar(100) not null,DateFrom datetime not null, DateTo datetime null);

INSERT INTO temp values('A1','A Company','2000-04-11',null);

INSERT INTO temp values('A1','A1 Company','2000-04-11','2004-12-31');

INSERT INTO temp values('A1','A2 Company','2004-12-31',null);

SELECT

DT.Companyid

,DT.companyname

,DT.DateFrom

,DT.DateTo

,DENSE_RANK() OVER

(

PARTITION BY DT.Companyid

ORDER BY ISNULL(DT.DateTo,DT.DateFrom) DESC

) AS COMP_RANK

FROM dbo.temp DT;

Output

Companyid companyname DateFrom DateTo COMP_RANK

---------- ------------- ----------------------- ----------------------- ----------

A1 A1 Company 2000-04-11 00:00:00.000 2004-12-31 00:00:00.000 1

A1 A2 Company 2004-12-31 00:00:00.000 NULL 1

A1 A Company 2000-04-11 00:00:00.000 NULL 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Eirikur Eiriksson (10/7/2016)
Quick suggestion
😎
USE TEEST;
GO
IF OBJECT_ID(N'dbo.temp') IS NOT NULL DROP TABLE dbo.temp;
create table dbo.temp(Companyid varchar(10) not null,companyname varchar(100) not null,DateFrom datetime not null, DateTo datetime null);
INSERT INTO temp values('A1','A Company','2000-04-11',null);
INSERT INTO temp values('A1','A1 Company','2000-04-11','2004-12-31');
INSERT INTO temp values('A1','A2 Company','2004-12-31',null);
SELECT
DT.Companyid
,DT.companyname
,DT.DateFrom
,DT.DateTo
,DENSE_RANK() OVER
(
PARTITION BY DT.Companyid
ORDER BY ISNULL(DT.DateTo,DT.DateFrom) DESC
) AS COMP_RANK
FROM dbo.temp DT;
Output
Companyid companyname DateFrom DateTo COMP_RANK
---------- ------------- ----------------------- ----------------------- ----------
A1 A1 Company 2000-04-11 00:00:00.000 2004-12-31 00:00:00.000 1
A1 A2 Company 2004-12-31 00:00:00.000 NULL 1
A1 A Company 2000-04-11 00:00:00.000 NULL 2

Give a man a rod, some bait, and his first two fish :-D:-D

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.allen SSC Guru Points: 76997 More actions · Answer 4

I think that this is what you are looking for.

SELECT *, ROW_NUMBER() OVER(PARTITION BY Companyid, DateFrom ORDER BY DateTo DESC)

FROM #temp

ORDER BY CompanyID, DateFrom, DateTo

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA