SQL works but I need to make a change to it

I had this question after viewing Different between these 2 sql statements.

The two solutions in the related question above work. But now, I need to add a "shipdate" where clause to it.

Side note: If this is not doable, I'll think of a workaround....maybe use the original SQL solution and dump the results into a temp table and then filter by shipdate (maybe)

This is sample tables and data.  If I add the "shipdate", it brings back data but it shouldn't. It should NOT because at least one of the statues is 4. If ALL statues are 4, then I should bring back the data.

This is SQl 2014

create table #commsales
(
  commsalesId int ,
  SellerCode varchar(20)

)



Create table #loads
(
  CommSalesId int,
  loadid int,
  shipdate datetime,
  loadStatus varchar(1)

)

insert into #commsales
select  81972, 'XYZ'


insert into #loads
select  81972,2480699,'2016-06-27 08:01:00.000',4

insert into #loads
select  81972,2480700,'2016-05-30 08:01:00.000',1

insert into #loads
select  81972,2480701,'2016-05-30 08:01:00.000',1

-----------Both these sql statements work as far as bringing back data. But when I add the "shipdate" clause...that's when it brings back data. But it shouldn't if there's at least one status that's not 4

 SELECT
      c.commsalesId, c.SellerCode
FROM (
      SELECT
            CommSalesId

      FROM #loads
	 where shipdate between  '2016-06-01 00:00:00.000' AND '2016-06-30 23:59:59.000'
      GROUP BY
            CommSalesId
      HAVING MIN(loadStatus) = 4 AND MAX(loadStatus) = 4
) AS l
      INNER JOIN #commsales AS c ON c.commsalesId = l.CommSalesId


	  ----------------- Or this one

	   WITH cteLoadS AS
(
	SELECT  CommSalesId, loadStatus, shipdate, COUNT(CommSalesId) OVER(PARTITION BY CommSalesId, loadStatus) AS CountByStatus,
		COUNT(CommSalesId) OVER(PARTITION BY CommSalesId) AS CountByCommSalesId
	FROM #loads
	where shipdate between  '2016-06-01 00:00:00.000' AND '2016-06-30 23:59:59.000'
)
SELECT c.commsalesId  --, c.SellerCode --loadstatus
FROM cteLoadS AS cte
INNER JOIN #commsales AS C
	ON cte.commsalesId = C.commsalesId
	
WHERE cte.CountByCommSalesId = cte.CountByStatus
	AND cte.loadStatus = 4
	
group by c.commsalesId


	 

Select allOpen in new window