Re: convert mssql function to mysql function
Thanks for your kind replay..
But i want to convert this mssql code to mysql any one please help me sir. please am new for mysql
use test;
CREATE TABLE timesheet
(
Task VARCHAR(10),
employee INT,
StartDate DATETIME,
endDate DATETIME
)
select * from timesheet;
INSERT INTO timesheet (Task, employee, StartDate, endDate) VALUES ('Task1',10,'2011-5-10 17:00:00','2011-5-10 20:00:00' )
INSERT INTO timesheet (Task, employee, StartDate, endDate) VALUES ('Task1', 10, '2011-5-12 17:00:00', '2011-5-12 20:00:00' )
INSERT INTO timesheet (Task, employee, StartDate, endDate) VALUES ('Task2',10,'2011-5-12 17:00:00','2011-5-12 20:00:00' )
INSERT INTO timesheet (Task, employee, StartDate, endDate) VALUES ('Task1', 12, '2011-5-08 17:00:00', '2011-5-08 20:00:00' )
select * from timesheet;
CREATE FUNCTION GetWeekDayNameOfDate
(
@Date datetime
)
RETURNS nvarchar(50)
BEGIN
DECLARE @DayName nvarchar(50)
SELECT
@DayName =
CASE (DATEPART(dw, @Date) + @@DATEFIRST) % 7
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 0 THEN 'Saturday'
END
RETURN @DayName
END
GO
SELECT timesheet.Task,
timesheet.employee,
MIN(timesheet.StartDate) AS [Start Date],
MAX(timesheet.endDate) AS [End Date],
SUM(DATEDIFF(n, timesheet.StartDate, timesheet.endDate)) / 60 AS [Hours],
SUM(DATEDIFF(n, timesheet.StartDate, timesheet.endDate)) % 60 AS [Minutes]
FROM timesheet
GROUP BY timesheet.Task,
timesheet.employee
SELECT Task,
Employee,
ISNULL([Sunday], 0) AS Sunday,
ISNULL([Monday], 0) AS Monday,
ISNULL([Tuesday], 0) AS Tuesday,
ISNULL([Wednesday], 0) AS Wendesday,
ISNULL([Thursday], 0) AS Thursday,
ISNULL([Friday], 0) AS Friday,
ISNULL([Saturday], 0) AS Saturday
FROM ( SELECT timesheet.Task,
timesheet.employee,
dbo.GetWeekDayNameOfDate(timesheet.StartDate) AS [Day],
SUM(DATEDIFF(n, timesheet.StartDate, timesheet.endDate))
/ 60 AS [Hours],
SUM(DATEDIFF(n, timesheet.StartDate, timesheet.endDate))
% 60 AS [Minutes]
FROM timesheet
GROUP BY timesheet.Task,
timesheet.employee,
dbo.GetWeekDayNameOfDate(timesheet.StartDate)
) p PIVOT ( SUM(Hours) FOR [DAY] IN ( [Sunday], [Monday], [Tuesday],
[Wednesday], [Thursday],
[Friday], [Saturday] ) ) AS PivotTable
SELECT summary.Task,
summary.employee,
summary.[Start Date],
summary.[End Date],
details.Sunday,
details.Monday,
details.Tuesday,
details.Wendesday,
details.Thursday,
details.Friday,
details.Saturday,
summary.Hours,
summary.Minutes
FROM ( SELECT timesheet.Task,
timesheet.employee,
MIN(timesheet.StartDate) AS [Start Date],
MAX(timesheet.endDate) AS [End Date],
SUM(DATEDIFF(n, timesheet.StartDate, timesheet.endDate))
/ 60 AS [Hours],
SUM(DATEDIFF(n, timesheet.StartDate, timesheet.endDate))
% 60 AS [Minutes]
FROM timesheet
GROUP BY timesheet.Task,
timesheet.employee
) summary
INNER JOIN ( SELECT Task,
Employee,
ISNULL([Sunday], 0) AS Sunday,
ISNULL([Monday], 0) AS Monday,
ISNULL([Tuesday], 0) AS Tuesday,
ISNULL([Wednesday], 0) AS Wendesday,
ISNULL([Thursday], 0) AS Thursday,
ISNULL([Friday], 0) AS Friday,
ISNULL([Saturday], 0) AS Saturday
FROM ( SELECT timesheet.Task,
timesheet.employee,
dbo.GetWeekDayNameOfDate(timesheet.StartDate) AS [Day],
SUM(DATEDIFF(n, timesheet.StartDate,
timesheet.endDate)) / 60 AS [Hours],
SUM(DATEDIFF(n, timesheet.StartDate,
timesheet.endDate)) % 60 AS [Minutes]
FROM timesheet
GROUP BY timesheet.Task,
timesheet.employee,
dbo.GetWeekDayNameOfDate(timesheet.StartDate)
) p PIVOT ( SUM(Hours) FOR [DAY] IN ( [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday] ) ) AS PivotTable
) details ON summary.Task = details.Task
AND summary.Employee = details.Employee
With regards,
A.Romilton Fernando....
Edited 1 time(s). Last edit at 12/28/2011 02:39AM by Romilton Fernando.