CODEWITHSUNDEEP
sql-server-2012
ravsun
ravsun

Reputation: 95

Query Items which were resold within 1 year of last selling date

I have a table with details of sold cars. Some of these cars have been resold within last 1, 2 or 3 years. The table looks like this:

Car_Type ||  Car_Reg_No  ||      Sold_Date    ||  Listing_No
 Hatch   ||     23789    ||  2017-02-03 11:26 ||  X6529
 Coupe   ||     16723    ||  2016-11-07 09:40 ||  N8156
 Sedan   ||     35216    ||  2016-05-23 10:34 ||  M8164
 Hatch   ||     23789    ||  2016-09-16 04:30 ||  O7361

Now, I need to query records (cars) which were re-sold within 1 year of their latest sold date and how many times were they sold. So, my output would be like this:

Car_Type ||  Car_Reg_No  ||  Sold_Count  || Latest_Sold_Date
 Hatch   ||     23789    ||      2       || 2017-02-03 11:26

In essence, How do I check for re-sold records within a specific time frame of their latest sold date?

Upvotes: 2

Views: 57

Answers (2)

Pugal
Pugal

Reputation: 549

By my understanding..,

select sd1.Car_Type, sd1.Car_Reg_No,
count(sd1.Car_Reg_No) + 1 'no of sales in last one year', --1 is added because, see the last condition
sd1.Sold_Date 'Last sold date'
from(
select *,ROW_NUMBER() over(partition by Car_Reg_No order by sold_date desc) as rn from #Table) as sd1
join 
(select * from #Table) as sd2
on sd1.Car_Type = sd2.Car_Type 
and DATEDIFF(dd,sd2.Sold_Date,sd1.Sold_Date) < 366
and sd1.rn = 1 
and sd1.Sold_Date <> sd2.Sold_Date -- here last sold is eliminated. so count is added by one.
group by sd1.Car_Type,sd1.Sold_Date, sd1.Car_Reg_No
order by sd1.Car_Reg_No

Upvotes: 0

S3S
S3S

Reputation: 25112

You can do this by finding the max, and joining based on your conditions.

declare @TableA table (Car_Type varchar(64)
                      ,Car_Reg_No int
                      ,Sold_Date datetime
                      ,Listing_No varchar(6))

insert into @TableA
values
insert into @TableA
values
('Hatch',23789,'2017-02-03 11:26','X6529'),
('Coupe',16723,'2017-11-07 09:40','N8156'),
('Sedan',35216,'2017-05-23 10:34','M8164'),
('Hatch',23789,'2016-09-16 04:30','O7361'),
('Coupe',16723,'2014-11-07 09:40','N8156')

;with cte as(
select
    Car_Type
    ,Car_Reg_No
    ,Latest_Sold_Date = max(Sold_Date)
from
    @TableA
group by 
    Car_Type
    ,Car_Reg_No)

select 
    a.Car_Type
    ,a.Car_Reg_No
    ,Sold_Count = count(b.Listing_No) + 1
    ,a.Latest_Sold_Date
from cte a
    inner join
    @TableA b on 
    b.Car_Reg_No = a.Car_Reg_No
    and b.Sold_Date != a.Latest_Sold_Date
    and datediff(day,b.Sold_Date,a.Latest_Sold_Date) < 366
    --if you want only cars which were sold within last year too, uncomment this
    --and datediff(day,a.Latest_Sold_Date,getdate()) < 366
group by
    a.Car_Type
    ,a.Car_Reg_No
    ,a.Latest_Sold_Date

Upvotes: 1

Related Questions