February 11, 2009 at 10:06 am (Query)
we will see some use ful queries on MySQL,
1.Finding 2nd Maximum Number,
“select column A from tableA order by columnA desc limit 1,1″
2.Finding 2nd Minimum Number,
“select column A from tableA order by columnA asc limit 1,1″
3.Finding Saturday and Sunday in a year, in below code you can a procedure to extract saturday and sunday in a year,
DROP PROCEDURE IF EXISTS `test`.`testa`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `testa`()
begin
declare l_functionReturnDate datetime;
declare i_TRFE_Start_Dt datetime;
declare i_TRFE_End_Dt datetime;
declare TripDate datetime;
declare l_curMonth varchar(2);
declare l_curyear varchar(4);
declare date_t char(10);
set i_TRFE_Start_Dt=now();
set i_TRFE_End_Dt=’2009-03-28 12:51:01′;
while i_TRFE_Start_Dt < i_TRFE_End_Dt do
set l_curyear=substring(i_TRFE_Start_Dt,1,4);
set l_curMonth=month(i_TRFE_Start_Dt);
set date_t=day(i_TRFE_Start_Dt);
set l_functionReturnDate=concat(l_curyear,’-',l_curMonth,’-',date_t);
select fc_GetWeekdayDate (l_functionReturnDate,’Saturday’,1);
set i_TRFE_Start_Dt = date_add(i_TRFE_Start_Dt, interval 7
day);
end while;
end$$
DELIMITER ;
******************************
DROP FUNCTION IF EXISTS `test`.`fn_GetWeekdayDate`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetWeekdayDate`(
TheDate Datetime,
DayName varchar(10),
DayNum Tinyint
) RETURNS datetime
BEGIN
DECLARE InitialDate Datetime;
DECLARE ReturnDate Datetime;
DECLARE Dow int;
SET InitialDate =
CASE WHEN Date_Format(TheDate, ‘%W’) = DayName THEN TheDate
WHEN Date_Format(Date_Add(TheDate, INTERVAL 1 DAY), ‘%W’) =
DayName THEN Date_Add(TheDate, INTERVAL 1 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 2 DAY), ‘%W’) =
DayName THEN Date_Add(TheDate, INTERVAL 2 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 3 DAY), ‘%W’) =
DayName THEN Date_Add(TheDate, INTERVAL 3 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 4 DAY), ‘%W’) =
DayName THEN Date_Add(TheDate, INTERVAL 4 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 5 DAY), ‘%W’) =
DayName THEN Date_Add(TheDate, INTERVAL 5 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 6 DAY), ‘%W’) =
DayName THEN Date_Add(TheDate, INTERVAL 6 DAY)
ELSE TheDate
END;
SET ReturnDate = CASE WHEN DayNum IS NULL or DayNum IN (0,1) Then
InitialDate
ELSE
Date_Add(InitialDate, INTERVAL (7*(DayNum-1)) DAY)
END;
/* uncomment this if you want to return null instead
of allowing crossover months
SET ReturnDate = CASE WHEN Month(TheDate) = Month(ReturnDate) THEN
ReturnDate
ELSE NULL
END;
*/
RETURN ReturnDate;
END$$
DELIMITER ;
Weather
