Sql-server – Finding the first occurrence where id don’t match

sql servert-sql

I’m trying to find the first occurrence of SNum where the VerNum don’t match. In this case it should point to SNum = 300 as VerNum 12 is not present.

DROP TABLE #temp1
DROP TABLE #temp2

CREATE TABLE #temp1 (VerNum INT, SNum INT)

INSERT INTO #temp1 
VALUES (11,100), (12,100), (11,200), (12,200), (11,300)


CREATE TABLE #temp2 (VerNum INT)

INSERT INTO  #temp2 
VALUES (11), (12) 


SELECT * FROM #temp1
SELECT * FROM #temp2

A full join is not working, i can loop through the table and get the result I am after, but there must be a better way. Any help is appreciated.

Best Answer

How about something like this?

select top 1 snum 
from #temp1 
group by SNum
having count(*) < (select count(*) from #temp2) 
order by snum asc

This could work if you're sure that there are no duplicates vernum for the same snum

This by the way, i think is a more solid solution:

select top 1 coalesce(a.snum, b.snum) as result
from #temp1 a full join #temp1 b on a.SNum = b.SNum and a.VerNum <> b.VerNum
where a.VerNum is null or b.VerNum is null

I didn't test them much, only with the data you provided, so be careful!

Related Solutions

Sql-server – Script to query multiple instances

There's lots of options.

PowerShell
SSMS's Multi Server Query Functionality
Linked Servers
Batch File with a loop and sqlcmd variables

Sql-server – How to return data from a table where the data is the max date of the previous month’s data submissions (-1 of current date)

Expanding on James' answer

Try this code on SqlFiddle.

Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate

create table tracker_live (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live primary key (client_nbr, tracker_date, submit_date));

insert into tracker_live (client_nbr, tracker_date, submit_date, live, retainer, bonus) values
   (9,'2014-10-01','2014-10-08',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-08',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-08',45585.4029,3,3)
  ,(9,'2014-10-01','2014-10-17',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-17',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-17',45585.4029,3,3)
  ,(9,'2014-10-01','2014-11-13',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-13',907925.1072,2,2)
  ,(9,'2014-12-01','2014-11-13',392917.5237,3,3)
  ,(9,'2014-10-01','2014-11-18',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-18',795725.1072,2,2)
  ,(9,'2014-12-01','2014-11-18',725835.0474,3,3)

create table tracker_live_last_month (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live_last_month primary key (client_nbr, tracker_date));  

/* example query for procedure */


declare @test date;
  set @test = '2014-11-18';
  --set @test = convert(date,getdate());
declare @LastDayOfLastMonth date;
  set @LastDayOfLastMonth  = dateadd(day,-datepart(day,@test),@test);
declare @FirstDayOfLastMonth date;  
  set @FirstDayOfLastMonth = dateadd(day,1-datepart(day,@LastDayOfLastMonth),@LastDayOfLastMonth);

  truncate table tracker_live_last_month;

  /* inserting the data with this combined query should be quicker than creating the table and then updating values */
  insert into tracker_live_last_month (client_nbr, tracker_date, submit_date, live, retainer, bonus)
    select tl.client_nbr, tl.tracker_date, ca.submit_date, ca.live, ca.retainer, ca.bonus
      from tracker_live tl
        cross apply (select top 1 i.submit_date, i.live, i.retainer, i.bonus 
          from tracker_live i 
          where i.client_nbr=tl.client_nbr 
            and i.tracker_date=tl.tracker_date
          order by i.submit_date desc) as ca
      where tl.submit_date between @FirstDayOfLastMonth and @LastDayofLastMonth
      group by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus
      order by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus;

Best Answer

Related Solutions

Sql-server – Script to query multiple instances

Sql-server – How to return data from a table where the data is the max date of the previous month’s data submissions (-1 of current date)

Related Question