How to make SQL Query to get Birthday & Anniversary Alert

RAM RAM Friends

How to get list of clients from Database whose Birthday or Anniversary would come within the next few days

When I got this problem, first I thought it would be normal & not tough.
But when I start working on it, I found it very very difficult & like anyone else I tried to find its solution on the Internet using Google etc, but after searching a lot for few days, I feel very upset as I didn't get the correct solution neither the hint to solve it.
But after getting tired from net, I never gave up & decided to solve this problem by my own way,
so after a lot of hard work I found the way & moreover when writing this blog, I also found other ways to make the whole procedure very simple.

Below is the summary of the whole procedure which I implemented in the project

Note: Please note that whole procedure is written in VB6.0  & MS SQL Server 2000


    Dim S As String, IsShow As Boolean
    Dim UserDays As Integer
    Dim AlertDt As Date, CurrentDt As Date, CurrentNextYrDt As Date

                CurrentDt = Format$(Now, "DD/MMM/YYYY")

                UserDays = Val(IIf(IsNull(GDREC!settingvalue) = False, GDREC!settingvalue, "0"))

                

                AlertDt = CurrentDt + UserDays

                CurrentNextYrDt = DateAdd("yyyy", 1, CurrentDt)

Note: In below SQL Query, 

• All capital words are Keywords of SQL Server
• CurrentDt="26Oct2010"
• AlertDt="01Mar2011"  ,  (UserDays ="01Mar2011" - "26Oct2010")
• CurrentNextYrDt="26Oct2011"

      SELECT  Bday,ClientId,ClientName,Phone,Mobile,AlterBDay
      FROM (SELECT Bday,ClientId,ClientName,Phone,Mobile,
                     CASE WHEN MONTH(BDay) < MONTH(GETDATE()) THEN
                                             DATEADD(YEAR,DATEDIFF(YEAR,BDay,'26Oct2011'),BDay)
                                 Else
                                            DATEADD(YEAR,DATEDIFF(YEAR,BDay,'26Oct2010'),BDay)
                                 END As AlterBDay
                       FROM ClientMaster WHERE BDay IS NOT NULL AND NOT(BDay='01Jan1900')
                    ) AS NewClientMaster
      WHERE AlterBDay BETWEEN '26Oct2010' AND '01Mar2011'
      ORDER BY AlterBDay

• Explanation of above Query:
• Here I have used one kind of Nested query with Case, When Else End As clause
• Here I used nested query to return a table (view) from which main query extract desired data
• In the nested query, I created one new column "AlterBDay" which use value of  BDay (Birthday) column &  have modified date by making year part to either current year or next year of BDay column according to AlertDt ("01Mar2011") value.
• After studying & understanding the above query & you would certainly get lot of ideas to make this query more short & also easy to understand, as I think, this query would be more easy if you use/create Views instead of nested query.

• Below is the use of above query in VB6 project
• by passing the whole query in the string variable named "S"

                S = " SELECT Bday,ClientId,ClientName,Addr1,Phone,Mobile,AlterBDay "
                S = S & " FROM (SELECT Bday,ClientId,ClientName,Addr1,Phone,Mobile, "
                S = S & " CASE WHEN MONTH(BDay) < MONTH(GETDATE()) THEN "
                S = S & " DATEADD(YEAR,DATEDIFF(YEAR,BDay,'" & Format$(CurrentNextYrDt, "DD/MMM/YYYY") & "'),BDay) "
                S = S & " Else "
                S = S & " DATEADD(YEAR,DATEDIFF(YEAR,BDay,'" & Format$(CurrentDt, "DD/MMM/YYYY") & "'),BDay) "
                S = S & " END As AlterBDay "
                S = S & " FROM ClientMaster WHERE BDay IS NOT NULL AND NOT(BDay='01Jan1900') "
                S = S & " ) AS NewClientMaster WHERE AlterBDay BETWEEN '" & Format$(CurrentDt, "DD/MMM/YYYY") & "' AND '" & Format$(AlertDt, "DD/MMM/YYYY") & "' "
                S = S & " ORDER BY AlterBDay "

I hope this query would help you a lot in case you get this kind of problem.

Happy Programming.

I uses programming as a way to help people to make their life more comfortable.