if statment in Sql Server view

Question

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.SaleType, dbo.ItemStore.SpecialBuyFromDate AS SaleStartDate , 
dbo.ItemStore.SpecialBuyToDate AS SaleEndDate , dbo.ItemStore.AssignDate
FROM         dbo.ItemMainAndStoreView

score 3 · Accepted Answer · answered Jun 02 '11 at 18:47

3

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

answered Jun 02 '11 at 18:47

gbn

422,506
82
585
676

Interesting use of DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) to get the start of the day – lambacck Jun 02 '11 at 18:53
@lambacck: most efficient way to do it http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server/1177529#1177529 – gbn Jun 02 '11 at 18:55

score 1 · Answer 2 · answered Jun 02 '11 at 18:45

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

I think he need only direction ...i.e want to learn the thing rather than ready-made code thats y .... — Pranay Rana, Jun 02 '11 at 18:55

score 1 · Answer 3 · answered Jun 02 '11 at 18:46

1

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

answered Jun 02 '11 at 18:46

D'Arcy Rittich

167,292
40
290
283

if the SpecialBuyToDate is today 00:00 this fails because time is 20:56... no? – gbn Jun 02 '11 at 18:56
@gbn it depends on the business rules; sometimes the end date is meant to stop at 00:00 of that day, sometimes at 00:00 of following day, but that is good to point out. – D'Arcy Rittich Jun 02 '11 at 19:33

if statment in Sql Server view

3 Answers3