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
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?
Could it be that dtExpirationDate exists in both tblCLBrands and tblCLPermit tables, or in tblOnlineCLTempLab table itself?
0
dlavarAuthor Commented:
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.
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
0
dlavarAuthor Commented:
I had to set a grace period of 60 days in the stored procedure
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 --If today is 60 days before the expiration (before renewal time), charge 0. 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 GETDATE() < DATEADD(Day , -60, dtExpirationDate) ) 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
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.