asked on

Compare Expiration dates

I have a stored procedure that assign a dollar amount based on an expiration date. Initially a user would pay a $1000 for each brand family. But if the user would like to add styles they can do so without being charged. Therefore I have the case statement below.

 SELECT l.vchTransNum, l.vchManufacturer, b.vchBrand AS 'Brand Family', 
      CASE
       
      --check online tables to see if brands have been submitted for this particular manu
      WHEN EXISTS (SELECT vchBrand, vchManufacturer 
      FROM tblOnlineCLBrands o  WHERE o.vchBrand = b.vchBrand and o.vchManufacturer =l.vchManufacturer) 
      THEN 0.00
      
      
       --checks in-house table to see if brands have been submitted for this manu and if this is renewal year
      WHEN EXISTS (SELECT vchBrand, vchManufacturer   
      FROM tblCLBrands bf
      INNER JOIN tblCLPermit p on bf.vchRecordID = p.vchRecordID 
      --WHERE bf.vchBrand = b.vchBrand and bf.vchManufacturer =l.vchManufacturer and Year(DATEADD (Year , 3, dtpermitDate))<= YEAR(GetDate()) )
      WHERE bf.vchBrand = b.vchBrand and bf.vchManufacturer =l.vchManufacturer and dtExpirationDate > GETDATE())
      THEN 0.00
     
      
      ELSE 1000
      END AS 'Cost of Brand'
            
      FROM tblOnlineCLTempLab l
      
      INNER JOIN (Select distinct vchBrand, vchTransNum  from tblOnlineCLTempBrands) b on l.vchTransNum  = b.vchTransNum 
      
      WHERE  l.vchTransNum = @TransNum
      GROUP BY l.vchTransNum , l.vchManufacturer, b.vchbrand

Open in new window

The second case line is giving me a hard time. If users want to renew before the expiration date, it still assigns it zero dollars instead of $1000. How can I program it to allow the user to pay before their date expires?

Koen Van Wielink

There's nothing obviously wrong with the query as far as I can tell, so is it possible that the first case statement always evaluates to True?

chaau

Could it be that dtExpirationDate exists in both tblCLBrands and tblCLPermit tables, or in tblOnlineCLTempLab table itself?

dlavar

ASKER

When I commented out the first case statement, it resulted in a $1000. The Expiration date is only in the Permit table. I'm thinking that I may have to set a grace period of when the can renew their permit.

Koen Van Wielink

In that case you'll have to check why the first case statement evaluates to True when you don't want it to. The problem is most likely not with the expiration date.

ASKER CERTIFIED SOLUTION

dlavar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlavar

ASKER

My solution was the one that worked and was most effective.