if statment in Sql Server view

I want to set the saletype to 0 if the sale date has expired or is not yet active but only if assignDate is true. how can I build it in the view?

SELECT     dbo.ItemStore.Sal开发者_高级运维eType, dbo.ItemStore.SpecialBuyFromDate AS SaleStartDate , 
dbo.ItemStore.SpecialBuyToDate AS SaleEndDate , dbo.ItemStore.AssignDate
FROM         dbo.ItemMainAndStoreView

---

Use a CASE expression

You'll need to allow for the time aspect of GETDATE() hence my DATEADD/DATEDIFF to remove the time component for correct date range checks.

For SQL Server 2008+ you can just use CAST(GETDATE() as date)

SELECT
     CASE
        WHEN DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))
                BETWEEN dbo.ItemStore.SpecialBuyFromDate AND dbo.ItemStore.SpecialBuyToDate
            THEN dbo.ItemStore.SaleType
            ELSE 0
     END AS SaleType
     dbo.ItemStore.SpecialBuyFromDate AS SaleStartDate , 
     dbo.ItemStore.SpecialBuyToDate AS SaleEndDate,
     CASE
 FROM    
     dbo.ItemMainAndStoreView

---

Make use of Case..When will resolve your issue easily

for example :

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Name, 'Price Range' = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO

---

SELECT case 
    when getdate() between dbo.ItemStore.SpecialBuyFromDate 
        and dbo.ItemStore.SpecialBuyToDate 
    then dbo.ItemStore.SaleType 
    else 0 end as SaleType,
    dbo.ItemStore.SpecialBuyFromDate AS SaleStartDate,  
    dbo.ItemStore.SpecialBuyToDate AS SaleEndDate 
FROM dbo.ItemMainAndStoreView