Using SQL query how to check a certain value of a column along with the whole values in other column-CodePudding

I have a table with sample data

Inventory 
shop_code  product_id  date      QTY
10         20          20210101  5

**EDIT: Please add additional data above too**

I want to select shop_codes and product_ids for which the QTY was not 0 for all the dates in the last 60 days.

EDIT: The OP put this in an answer, It should be here

what I am trying to is:

select top 1000 
       SHOPCODE, 
       ProductId, 
       COUNT(DateKey) as dayNumber 
   from   
      Inventory 
   where  
          DateKey > (the date of 60 days ago) 
      and QTY > 0 
   group by 
      SHOPCODE, 
      ProductID 
   having 
      COUNT(DateKey) = ( select COUNT(distinct DateKey) 
                         from Inv where DateKey > (the date of 60 days ago)
                       )

I'm not sure that it is a suitable way and wonder if there is a way rather than counting the dates

CodePudding user response：

First, we calculate the date of the previous 60 days and put it in the variable numerically. Then we extract all the products, provided that their date is for 60 days and their number is above 0, and we group them according to the store and the product ID.

Use this snippet:

declare @dateInt int 
set @dateInt=cast(Convert(CHAR(8),DATEADD(day,-60,  getdate()) 1,112) as int)


select product_id
      ,shop_code
      ,sum(QTY) as sumQTY 
   from yourTable 
   where date>=@dateInt and QTY>0  
   group by product_id,shop_code

CodePudding user response：

Your question is not very clear, but from what I understand of it and from the comments on Sayeed's answer, I think this may be what you are after

select top 1000 
       i.SHOPCODE, 
       i.ProductId
from   Inventory i
where  not exists ( select 1 
                    from   inventory i2
                    where  i2.shopcode = i.shopcode
                    and    i2.productid = i.productid
                    and    i2.DateKey > datepart(day, -60, getdate())
                    and    i2.QTY = 0
                  )