Joshua Deshazer Joshua Deshazer - 18 days ago 6
SQL Question

T -Sql query trying to only select store numbers that contain both register numbers

I have a query now I want to only select the the store_numbers that contain only both 52,53 do not select the column if it only contains one or the other can anyone help please

DECLARE @Date_value date;
SET @Date_value = (SELECT MAX(REPORT_DATE) FROM sco_levels WHERE register_nbr BETWEEN 52 AND 54)


SELECT STORE_NBR, REGISTER_NBR, MAX(report_date), CASE When MAX(report_date) <> GETDATE() -1 THEN DateDiff(day, max(report_date), @Date_value) else 0 end as DiffDate
from sco_levels
where register_nbr between 52 and 53
AND report_date <> GetDate() -1
group by store_nbr, register_nbr
order by store_nbr, register_nbr

Answer

Here is one way

select STORE_NBR
from sco_levels
Where register_nbr in (52,53)
Group by STORE_NBR
Having count(distinct register_nbr) = 2

You can add the above query to your query to filter the STORE_NBR which has both 52 and 53

SELECT store_nbr, 
       register_nbr, 
       Max(report_date), 
       CASE 
         WHEN Max(report_date) <> Getdate() - 1 THEN 
         Datediff(day, Max(report_date), @Date_value) 
         ELSE 0 
       END AS DiffDate 
FROM   sco_levels s1 
WHERE  register_nbr BETWEEN 52 AND 53 
       AND report_date <> Getdate() - 1 
       AND EXISTS (SELECT 1 
                   FROM   sco_levels s2 
                   WHERE  s2.register_nbr IN ( 52, 53 ) 
                          AND s1.store_nbr = s2.store_nbr 
                   HAVING Count(DISTINCT s2.register_nbr) = 2) 
GROUP  BY store_nbr, 
          register_nbr 
Having Datediff(day, Max(report_date), @Date_value) > 7
ORDER  BY store_nbr, 
          register_nbr 
Comments