asked
query sql view
I have a table with 15 certification and expiration dates I have a view that looks like this Create view [dbo].[regexping] as select r.BUSINESSNAME, r.CONTACT, r.ADDRESS, r.CITY, r.STATE, r.ZIPCODE, r.PHONE, r.FAX, r.EMAIL, r.CITYCSBCERT, r.CSBxDate, r.CITYFBECERT, r.FBExDate, r.CITYMBECERT, r.MBExDATE, r.EDGECERT, r.EdgeExDate, r.DBE, r.DBExDate, r.SBE, r.SBExDate, r.CMHA, r.CMHAExp, r.OTHER, r.OxDate, r.OtherDocs0, r.OtherDoc0Exp, r.OtherDocs1, r.OtherDocs1Exp from Registration r where CMHAExp between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or r.CSBxDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.FBExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.MBExDATE between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.DBExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.EdgeExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.STATEMBExDATE between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.OtherDoc0Exp between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.OtherDocs1Exp between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.OxDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) and r.SBExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) GO which works fine I have created two input text fields startdate and enddate with a go button. I am now trying to query between the dates. Now I have this written DECLARE @startdate smalldatetime DECLARE @enddate smalldatetime select r.BUSINESSNAME, r.CONTACT, r.ADDRESS, r.CITY, r.STATE, r.ZIPCODE, r.PHONE, r.FAX, r.EMAIL, r.CITYCSBCERT, r.CSBxDate, r.CITYFBECERT, r.FBExDate, r.CITYMBECERT, r.MBExDATE, r.EDGECERT, r.EdgeExDate, r.DBE, r.DBExDate, r.SBE, r.SBExDate, r.CMHA, r.CMHAExp, r.OTHER, r.OxDate, r.OtherDocs0, r.OtherDoc0Exp, r.OtherDocs1, r.OtherDocs1Exp from Registration r where CMHAExp between @startdate and @enddate or r.CSBxDate between @startdate and @enddate or r.FBExDate between @startdate and @enddate or r.MBExDATE between @startdate and @enddate or r.DBExDate between @startdate and @enddate or r.EdgeExDate between @startdate and @enddate or r.STATEMBExDATE between @startdate and @enddate or r.OtherDoc0Exp between @startdate and @enddate or r.OtherDocs1Exp between @startdate and @enddate or r.OxDate between @startdate and @enddate or r.SBExDate between @startdate and @enddate best with sproc or view for speed? If view, do I need to change anything?
Personally proc over view. If you are truly looking for performance gains, take the date columns out of the Registration table and store them as rows in a RegistrationCertification table instead. In other words, each Registration would have many Certifications, each Certification is held by many Registrations, and each Certification would have an expiry date, but each one is only unique to the combination of Registration and Certification Then have a foreign key from Registration to a many-to-many resolution table RegistrationCertification that stores the primary key from Registration and the primary key from the new Certification table. The your query would be select --registration cols r.BUSINESSNAME, r.CONTACT, etc, etc, -- cert cols c.CertificationName, c.........., -- cert dates rc.ExpiryDate from Registration R join RegistrationCertification RC on join condition join Certification C on join condition where RC.ExpiryDate between @startdate and @enddate much better use of indexes!
