BIRTHDAY PROMPT PROGRAM DESIGN

Have been trying to write a simple SQL statement that can select a list of people from a table based on their birthdates. Thus if you are within some set days to your birthday you will be among the list selected.

I realized that, to get the query right, i had to test for some conditions, which means writing a function or a stored procedure.

I opted for the function and below are the queries written in both Microsoft SQL Server and MySql

This is the design, to check if  someone’s birthday let say 31-12-1990 is within a set range say 7 ( thus it is seven or less days to his or her birthday), these are the steps taken

1. We bring the birth date year to the current year, so we get 31-12-2010.

2. We then compare if the current birth date is greater than the current date

3. If it is greater we find the difference  in days between the two dates (birthdate-currentdate) and find out if the difference is between the set range

4. If current date is greater than birth date ( which means the guys birthdate is in the next year) we change the birth date year to the next year and find the difference as done in step 3. For example if the guy’s birthday is 2nd Jan and the current date is 30 Dec and the set days is 7, the guy should be in the list selected, so this condition takes care of this.

Below are the sql queries enjoy.

IN MSSQL

   1: CREATE FUNCTION dbo.getDaysPrompt (@bdate datetime, @curDate datetime)

   2: RETURNS int AS  

   3: BEGIN 

   4:  

   5: declare @mDate as datetime

   6: declare @days as int 

   7: select @mDate=dateadd(year,datediff(year,@bdate,@curDate),@bdate)

   8: if @curDate> @mdate

   9:  select @days=datediff(day,@curDate,dateadd(year,1,@mDate))

  10: else

  11:  select @days=datediff(day,@curDate,@mDate)

  12: return @days

  13:  

  14: END

IN MYSQL

   1: DELIMITER $$

   2: DROP FUNCTION IF EXISTS `dboffman`.`bdayPrompt`$$

   3: CREATE DEFINER=`root`@`localhost` FUNCTION `bdayPrompt`(bdate date,cDate date) RETURNS int(11)

   4: BEGIN

   5:  declare mDate  date;

   6:  declare days  int default 0;

   7:  set mDate=makedate(year(cdate),dayofyear(bdate));

   8:  if cDate > mdate then

   9:  set days=datediff(date_add(mDate, interval 1 year),cDate);

  10:  else

  11:  set days=datediff(mDate,cDate);

  12:  end if;

  13:  return days;

  14:  END$$

  15: DELIMITER ;

With this function we can then write our sql query for example

Select * from students where bdaypromt(birthday, curDate)>=0 and bdaypromt(birthday, curDate)<=@setdays

Enjoy !!!!!!

Advertisements

PROMPTING EXPIRY DATES OF PRODUCTS

In this post I’m writing about how to prompt users about the expiry dates of products without the use of any hardware (sensors). In this example, expiry dates are stored in the database and the user is prompted (based on configurable settings) that a product has some set days to expiry.

A very big assumption is made here

  • 1. Products purchased first, are sold first
  • 2. Quantity of products bought in one purchase have the same expiry date. For example, if you are buying 30 cartons of milk, the expiry date of all the cartons are the same

Now, lets go on with the design. We are using three (3) tables for our design namely, products table, purchase table and purchasedetail table. Note that the columns of the tables are just for the purpose of the expiry dates implementation

1) Products Table

ProductID ProductName Unitprice UnitsInStock

2)Purchase Table

PurchaseID SupplierID DatePurchased EmployeeID

3) PurchaseDetails Table

PurchaseID ProductID QuantityBought UnitPrice ExpiryDate

What we will be doing here is, we record the expiry date of a products when making the purchase. thus one product can have as many expiry dates as possible in the purchase details table based on the purchases made.

Note:when products are purchased , the quantity bought is added to the unitsinstock in the products table. Also when sales are made the neccesary deductions are made.

To prompt for expiry dates the key columns we will be using are the “unitsinStock” column in “products” table and the “quantitybought” column in “purchasedetails” table.

First we write a query to select records from the purchase details table. we select products with expiry dates greater than today and within the range of today + the set number of days to remind user

Example “select * from purchasedetails where Expirydates > today() and expirydate<=enddate” (enddate=today + the set number of days to give prompt)

Form the list of record (s) selected, using a loop , we first compare the last purchase record’s(purchaseID is auto generated number, so the highest PurchaseID value) quantity bought with the quantity in stock of that product.

IF the quantity bought (purchases details) is greater than the units in stock (products) , prompt that the number of that product(using units in stock) is expiring. You can add the date the purchase was made, from which of suppliers and the employee that handled the transaction from the Purchases table to the prompt and you end.

ELSE IF the units in stock (product) is greater than quantity bought (purchases details), this time prompt with the quantity bought as the number of products expiring. Then subtract the quantity bought from the units in stock with the remainder becoming the units in stock. mathematically unitsinstock=unitsinstock-quantitybought. Then move the the next  purchase record from the list.The loop start again.

Note: another termination condition is you can loop until the unitinstock is less or equal to zero.

Note: you can build a string of the prompts in the loop and show it after the loop

Please let me have your comments……….