locked
case when. sql statement shows the all record of table. Do not show "Ready to Sale" rows RRS feed

  • Question

  • hi

    select ProductId,ProductName,batch,	convert(varchar(10),dbo.ProductShopStock.Expairy,101) as Expairy,
    case
    when datediff(mm,getdate(), dbo.ProductShopStock.Expairy)<0 then 'Expired'
    when datediff(mm,getdate(), dbo.ProductShopStock.Expairy)<6 then 'Near To Expired'
    else 'Ready To Sale'
    END AS Status
    from dbo.ProductShopStock

    I want Status column not show "Ready To Sale" Records. How can i record selection and use where clause on status column.  status column not exist in my table. can some one guide me.


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Saturday, August 22, 2020 7:55 PM

All replies

  • Thanks

    Solution found

    select ProductId,ProductName,batch, convert(varchar(10),dbo.ProductShopStock.Expairy,101) as Expairy,
    case
    when datediff(mm,getdate(), dbo.ProductShopStock.Expairy)<0 then 'Expired'
    when datediff(mm,getdate(), dbo.ProductShopStock.Expairy)<6 then 'Near To Expired'
    else 'Ready To Sale'
    END 

    from dbo.ProductShopStock
    where  datediff(mm,getdate(), dbo.ProductShopStock.Expairy)<6  


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Saturday, August 22, 2020 8:25 PM
  • If I understand correctly you don't want ready to sale then eliminate it

    SELECT ProductId, 
           ProductName, 
           CONVERT(VARCHAR(10), Expiry, 101) AS Expairy,
           CASE
               WHEN DATEDIFF(mm, GETDATE(), Expiry) < 0
               THEN 'Expired'
               WHEN DATEDIFF(mm, GETDATE(), Expiry) < 6
               THEN 'Near To Expired'
               WHEN dbo.Products.Expiry IS NULL THEN 'Unknown'
               ---ELSE 'Ready To Sale'
           END AS STATUS
    FROM dbo.Products;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, August 22, 2020 8:56 PM
  • If you have time, then also consider this alternative:

    declare @d1 as date = getdate()

    declare @d2 as date = dateadd( mm, 6, getdate())

    select . . . ,

       case

          when dbo.ProductShopStock.Expairy < d1 then 'Expired'

          when dbo.ProductShopStock.Expairy < d2 then 'Near To Expired'

       end

    from dbo.ProductShopStock

    where dbo.ProductShopStock.Expairy < d2


    Such approach probably has sense in case of indices and large amounts.

    Make sure that dates do not include overlooked time parts and ‘<’ or ‘<=” are used correctly.


    • Edited by Viorel_MVP Sunday, August 23, 2020 7:53 AM
    Sunday, August 23, 2020 7:52 AM