Sql-server – Trying to return empty rows even if data does not exsist in SQL

sql server

I am trying to return the last changed time for a list of users, but i would like to return a row even if the a last changed time does not exist

select  user_id, 
        last_changed_time = MAX(ISNULL(change_date_time,0))     
from audit_log_tx
where user_id in (users) 
group by user_id
order by user_id asc

Best Answer

SELECT u.user_id, 
  column_name = COALESCE(MAX(change_date_time), '19000101')
  FROM dbo.users AS u
  LEFT OUTER JOIN dbo.audit_log_tx AS o
  ON u.user_id = o.user_id
  GROUP BY u.user_id;

Though I really don't like the use of token dates for missing data (especially 0).

Related Solutions

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;

Sql-server – Return even empty groups when grouping by date

Move the condition from the WHERE clause to a conditional count:

SELECT
    COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END),
    CD_Carrier,
    DATEPART(DAY, DT_Arriving) as [Date]
FROM TB_AGIL 
WHERE
    DT_Arriving >= @date
    AND DT_Arriving < DATEADD(MONTH,+1,@date)
GROUP BY 
    CD_Carrier, 
    DATEPART(DAY, DT_Arriving)
ORDER BY 
    [Date];

Other approaches to implement the deprecated GROUP BY ALL syntax can be found here.

Best Answer

Related Solutions

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)

Sql-server – Return even empty groups when grouping by date

Related Question