May 3, 2012 at 6:26 am
Could you please specify what results should look like exactly from sample data you posted.
I cannot see where you are trying to group by category2_id in your query ...
May 3, 2012 at 7:04 am
Eugene Elutin (5/3/2012)
Could you please specify what results should look like exactly from sample data you posted.I cannot see where you are trying to group by category2_id in your query ...
Thanks for your reply.
Apart from the columns that the query creates, I need to create another 4 new columns after summing the cat2_hours as per the sample data as there are 4 different category2_id values (S,M,B,R) for e.g. :
For Graham it should be displayed as :
1. S_cat2hours = 15:30:00 (Grouped by category2_id = S)
2. M_cat2hours = 18:00:00 (Grouped by category2_id = M)
3. B_cat2hours = 18:30:00 (Grouped by category2_id = B)
4. R_cat2hours = 35:00:00 (Grouped by category2_id = R)
For Paul:
1. S_cat2hours = 16:35:00 (Grouped by category2_id = S)
2. M_cat2hours = 16:35:00 (Grouped by category2_id = M)
3. B_cat2hours = 23:35:00 (Grouped by category2_id = B)
4. R_cat2hours = 33:45:00 (Grouped by category2_id = R)
I tried to achieve this through this code and also by adding category2_id in the group by but that didnt work. :
case when category2_id = 'S' then SUM(cat2hours) end as S_cat2hours
case when category2_id = 'M' then SUM(cat2hours) end as M_cat2hours
case when category2_id = 'B' then SUM(cat2hours) end as B_cat2hours
case when category2_id = 'R' then SUM(cat2hours) end as R_cat2hours
I need to do the grouping somewhere in between the query but I dont know how to do that.
Am I able to explain the scenario clearly ?
May 3, 2012 at 8:13 am
pwalter83 (5/3/2012)
Eugene Elutin (5/3/2012)
Could you please specify what results should look like exactly from sample data you posted.I cannot see where you are trying to group by category2_id in your query ...
Thanks for your reply.
Apart from the columns that the query creates, I need to create another 4 new columns after summing the cat2_hours as per the sample data as there are 4 different category2_id values (S,M,B,R) for e.g. :
For Graham it should be displayed as :
1. S_cat2hours = 15:30:00 (Grouped by category2_id = S)
2. M_cat2hours = 18:00:00 (Grouped by category2_id = M)
3. B_cat2hours = 18:30:00 (Grouped by category2_id = B)
4. R_cat2hours = 35:00:00 (Grouped by category2_id = R)
For Paul:
1. S_cat2hours = 16:35:00 (Grouped by category2_id = S)
2. M_cat2hours = 16:35:00 (Grouped by category2_id = M)
3. B_cat2hours = 23:35:00 (Grouped by category2_id = B)
4. R_cat2hours = 33:45:00 (Grouped by category2_id = R)
I tried to achieve this through this code and also by adding category2_id in the group by but that didnt work. :
case when category2_id = 'S' then SUM(cat2hours) end as S_cat2hours
case when category2_id = 'M' then SUM(cat2hours) end as M_cat2hours
case when category2_id = 'B' then SUM(cat2hours) end as B_cat2hours
case when category2_id = 'R' then SUM(cat2hours) end as R_cat2hours
I need to do the grouping somewhere in between the query but I dont know how to do that.
Am I able to explain the scenario clearly ?
Could anyone please help me in this regard ?
May 3, 2012 at 8:25 am
Your requirements are complicated enough, coding without style adds up...
Do you remember my joke about noodles? So, code-noodles good for lunch, but it's hard to work with.
First what I have done is re-formatted your query, so I could read it.
You may go even further by creating time format user defined scalar function, so you don't need to format time for every of your time column - it will make this code even more readable.
The main change I've done is the way you calculating your time sums. Instead of summing up hours, minutes and seconds separately then combining them into one, i've just "converted" the time to seconds (using datediff) and summing it up. You will see:
;WITH WorkSchedule
AS
(
SELECT Staff_Id, LEFT(WeekDayName,3) WeekDayName, WorkHours
FROM
(SELECT Staff_Id, MonHours, TueHours, WedHours, ThuHours, FriHours
FROM Staff) p
UNPIVOT
(WorkHours FOR WeekDayName IN (MonHours, TueHours, WedHours, ThuHours, FriHours)
)AS unpvt
)
, Summary -- calculate all time sums in seconds!
AS
(
SELECT MAX(st.Cardholder_name) AS [Employee Name]
,MAX(DATENAME(MONTH,s.[Date])) AS [Month]
,MAX(YEAR(s.[Date])) AS [Year]
,SUM(DATEDIFF(SECOND,'00:00:00',b.WorkHours)) AS WH
,SUM(DATEDIFF(SECOND,'00:00:00',s.Timenetin) +
DATEDIFF(SECOND,'00:00:00',ISNULL(s.cat2_hours, '00:00:00'))) AS AH
-- calculate per category
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'S'
THEN s.cat2_hours
END, '00:00:00'))) AS S_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'M'
THEN s.cat2_hours
END, '00:00:00'))) AS M_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'B'
THEN s.cat2_hours
END, '00:00:00'))) AS B_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'R'
THEN s.cat2_hours
END, '00:00:00'))) AS R_Cat2
FROM StaffDay AS s
JOIN WorkSchedule AS b
ON b.staff_id = s.staff_id
AND b.WeekDayName = s.[Dayname]
JOIN Staff st
ON st.staff_id = s.staff_id
WHERE s.Dayname NOT IN ('Sun','Sat')
AND DATEPART(yyyy,s.[Date]) IN ('2012')
AND DATENAME(MONTH,s.[Date])IN ('April')
GROUP BY s.Cardholder_name, DATEPART(MONTH,s.[Date])
)
SELECT [Employee Name] AS [Employee Name]
,[Month] AS [Month]
,[Year] AS [Year]
,CAST(WH/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(WH%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(WH%60 AS VARCHAR), 2) AS [Working Hours]
,CAST(AH/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(AH%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(AH%60 AS VARCHAR), 2) AS [Actual Hours]
,(AH - WH)/3600 AS [Hrs_Diff]
,(AH - WH)%3600/60 AS [Min_Diff]
,CAST(S_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(S_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(S_Cat2%60 AS VARCHAR), 2) AS
,CAST(M_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(M_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(M_Cat2%60 AS VARCHAR), 2) AS [M Cat2 Hours]
,CAST(B_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(B_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(B_Cat2%60 AS VARCHAR), 2) AS
,CAST(R_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(R_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(R_Cat2%60 AS VARCHAR), 2) AS [R Cat2 Hours]
FROM Summary
ORDER BY [Employee Name]
,[Year]
,[Month]
If you create formatting function your final query will look even nicer, something like that:
...
SELECT [Employee Name] AS [Employee Name]
,[Month] AS [Month]
,[Year] AS [Year]
,dbo.f_FormatTime(WH) AS [Working Hours]
,dbo.f_FormatTime(AH) AS [Actual Hours]
,(AH - WH)/3600 AS [Hrs_Diff]
,(AH - WH)%3600/60 AS [Min_Diff]
,dbo.f_FormatTime(S_Cat2) AS
,dbo.f_FormatTime(M_Cat2) AS [M Cat2 Hours]
,dbo.f_FormatTime(B_Cat2) AS
,dbo.f_FormatTime(R_Cat2) AS [R Cat2 Hours]
FROM Summary
ORDER BY [Employee Name]
,[Year]
,[Month]
Also, you can leave formatting to Reporting tool...
May 3, 2012 at 8:37 am
Eugene Elutin (5/3/2012)
Your requirements are complicated enough, coding without style adds up...Do you remember my joke about noodles? So, code-noodles good for lunch, but it's hard to work with.
First what I have done is re-formatted your query, so I could read it.
You may go even further by creating time format user defined scalar function, so you don't need to format time for every of your time column - it will make this code even more readable.
The main change I've done is the way you calculating your time sums. Instead of summing up hours, minutes and seconds separately then combining them into one, i've just "converted" the time to seconds (using datediff) and summing it up. You will see:
;WITH WorkSchedule
AS
(
SELECT Staff_Id, LEFT(WeekDayName,3) WeekDayName, WorkHours
FROM
(SELECT Staff_Id, MonHours, TueHours, WedHours, ThuHours, FriHours
FROM Staff) p
UNPIVOT
(WorkHours FOR WeekDayName IN (MonHours, TueHours, WedHours, ThuHours, FriHours)
)AS unpvt
)
, Summary -- calculate all time sums in seconds!
AS
(
SELECT MAX(st.Cardholder_name) AS [Employee Name]
,MAX(DATENAME(MONTH,s.[Date])) AS [Month]
,MAX(YEAR(s.[Date])) AS [Year]
,SUM(DATEDIFF(SECOND,'00:00:00',b.WorkHours)) AS WH
,SUM(DATEDIFF(SECOND,'00:00:00',s.Timenetin) +
DATEDIFF(SECOND,'00:00:00',ISNULL(s.cat2_hours, '00:00:00'))) AS AH
-- calculate per category
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'S'
THEN s.cat2_hours
END, '00:00:00'))) AS S_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'M'
THEN s.cat2_hours
END, '00:00:00'))) AS M_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'B'
THEN s.cat2_hours
END, '00:00:00'))) AS B_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'R'
THEN s.cat2_hours
END, '00:00:00'))) AS R_Cat2
FROM StaffDay AS s
JOIN WorkSchedule AS b
ON b.staff_id = s.staff_id
AND b.WeekDayName = s.[Dayname]
JOIN Staff st
ON st.staff_id = s.staff_id
WHERE s.Dayname NOT IN ('Sun','Sat')
AND DATEPART(yyyy,s.[Date]) IN ('2012')
AND DATENAME(MONTH,s.[Date])IN ('April')
GROUP BY s.Cardholder_name, DATEPART(MONTH,s.[Date])
)
SELECT [Employee Name] AS [Employee Name]
,[Month] AS [Month]
,[Year] AS [Year]
,CAST(WH/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(WH%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(WH%60 AS VARCHAR), 2) AS [Working Hours]
,CAST(AH/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(AH%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(AH%60 AS VARCHAR), 2) AS [Actual Hours]
,(AH - WH)/3600 AS [Hrs_Diff]
,(AH - WH)%3600/60 AS [Min_Diff]
,CAST(S_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(S_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(S_Cat2%60 AS VARCHAR), 2) AS
,CAST(M_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(M_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(M_Cat2%60 AS VARCHAR), 2) AS [M Cat2 Hours]
,CAST(B_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(B_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(B_Cat2%60 AS VARCHAR), 2) AS
,CAST(R_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(R_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(R_Cat2%60 AS VARCHAR), 2) AS [R Cat2 Hours]
FROM Summary
ORDER BY [Employee Name]
,[Year]
,[Month]
If you create formatting function your final query will look even nicer, something like that:
...
SELECT [Employee Name] AS [Employee Name]
,[Month] AS [Month]
,[Year] AS [Year]
,dbo.f_FormatTime(WH) AS [Working Hours]
,dbo.f_FormatTime(AH) AS [Actual Hours]
,(AH - WH)/3600 AS [Hrs_Diff]
,(AH - WH)%3600/60 AS [Min_Diff]
,dbo.f_FormatTime(S_Cat2) AS
,dbo.f_FormatTime(M_Cat2) AS [M Cat2 Hours]
,dbo.f_FormatTime(B_Cat2) AS
,dbo.f_FormatTime(R_Cat2) AS [R Cat2 Hours]
FROM Summary
ORDER BY [Employee Name]
,[Year]
,[Month]
Also, you can leave formatting to Reporting tool...
Are you some Genius or what ? Thanks a lot once again, hopefully I wont bother you again.
May 3, 2012 at 9:09 am
Eugene Elutin (5/3/2012)
Your requirements are complicated enough, coding without style adds up...Do you remember my joke about noodles? So, code-noodles good for lunch, but it's hard to work with.
First what I have done is re-formatted your query, so I could read it.
You may go even further by creating time format user defined scalar function, so you don't need to format time for every of your time column - it will make this code even more readable.
The main change I've done is the way you calculating your time sums. Instead of summing up hours, minutes and seconds separately then combining them into one, i've just "converted" the time to seconds (using datediff) and summing it up. You will see:
;WITH WorkSchedule
AS
(
SELECT Staff_Id, LEFT(WeekDayName,3) WeekDayName, WorkHours
FROM
(SELECT Staff_Id, MonHours, TueHours, WedHours, ThuHours, FriHours
FROM Staff) p
UNPIVOT
(WorkHours FOR WeekDayName IN (MonHours, TueHours, WedHours, ThuHours, FriHours)
)AS unpvt
)
, Summary -- calculate all time sums in seconds!
AS
(
SELECT MAX(st.Cardholder_name) AS [Employee Name]
,MAX(DATENAME(MONTH,s.[Date])) AS [Month]
,MAX(YEAR(s.[Date])) AS [Year]
,SUM(DATEDIFF(SECOND,'00:00:00',b.WorkHours)) AS WH
,SUM(DATEDIFF(SECOND,'00:00:00',s.Timenetin) +
DATEDIFF(SECOND,'00:00:00',ISNULL(s.cat2_hours, '00:00:00'))) AS AH
-- calculate per category
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'S'
THEN s.cat2_hours
END, '00:00:00'))) AS S_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'M'
THEN s.cat2_hours
END, '00:00:00'))) AS M_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'B'
THEN s.cat2_hours
END, '00:00:00'))) AS B_Cat2
,SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(CASE WHEN category2_id = 'R'
THEN s.cat2_hours
END, '00:00:00'))) AS R_Cat2
FROM StaffDay AS s
JOIN WorkSchedule AS b
ON b.staff_id = s.staff_id
AND b.WeekDayName = s.[Dayname]
JOIN Staff st
ON st.staff_id = s.staff_id
WHERE s.Dayname NOT IN ('Sun','Sat')
AND DATEPART(yyyy,s.[Date]) IN ('2012')
AND DATENAME(MONTH,s.[Date])IN ('April')
GROUP BY s.Cardholder_name, DATEPART(MONTH,s.[Date])
)
SELECT [Employee Name] AS [Employee Name]
,[Month] AS [Month]
,[Year] AS [Year]
,CAST(WH/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(WH%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(WH%60 AS VARCHAR), 2) AS [Working Hours]
,CAST(AH/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(AH%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(AH%60 AS VARCHAR), 2) AS [Actual Hours]
,(AH - WH)/3600 AS [Hrs_Diff]
,(AH - WH)%3600/60 AS [Min_Diff]
,CAST(S_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(S_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(S_Cat2%60 AS VARCHAR), 2) AS
,CAST(M_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(M_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(M_Cat2%60 AS VARCHAR), 2) AS [M Cat2 Hours]
,CAST(B_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(B_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(B_Cat2%60 AS VARCHAR), 2) AS
,CAST(R_Cat2/3600 AS VARCHAR) + ':' +
RIGHT('0' + CAST(R_Cat2%3600/60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(R_Cat2%60 AS VARCHAR), 2) AS [R Cat2 Hours]
FROM Summary
ORDER BY [Employee Name]
,[Year]
,[Month]
If you create formatting function your final query will look even nicer, something like that:
...
SELECT [Employee Name] AS [Employee Name]
,[Month] AS [Month]
,[Year] AS [Year]
,dbo.f_FormatTime(WH) AS [Working Hours]
,dbo.f_FormatTime(AH) AS [Actual Hours]
,(AH - WH)/3600 AS [Hrs_Diff]
,(AH - WH)%3600/60 AS [Min_Diff]
,dbo.f_FormatTime(S_Cat2) AS
,dbo.f_FormatTime(M_Cat2) AS [M Cat2 Hours]
,dbo.f_FormatTime(B_Cat2) AS
,dbo.f_FormatTime(R_Cat2) AS [R Cat2 Hours]
FROM Summary
ORDER BY [Employee Name]
,[Year]
,[Month]
Also, you can leave formatting to Reporting tool...
Eugene,
Your query runs okay, its just this code which does not add up the 2 columns:
,SUM(DATEDIFF(SECOND,'00:00:00',s.Timenetin) +
DATEDIFF(SECOND,'00:00:00',ISNULL(s.cat2_hours, '00:00:00'))) AS AH
For e.g. the result should be
15:12:09 + 14:00:00 = 29:12:09
but I get only 15:12:09. However, I could not find anything wrong with your code and I am really baffled that it still doesnt add up. Any ideas you got ?
May 3, 2012 at 9:18 am
Can you post exact underlying time data which doesn't add up when summed?
May 3, 2012 at 9:24 am
This is the example from SQL:
Bhasin, VishalMay2012161:00:0015:12:09-145-470:00:000:00:0014:00:000:00:00
Eugene Elutin (5/3/2012)
Can you post exact underlying time data which doesn't add up when summed?
May 3, 2012 at 9:31 am
I need underlying data, not the results.
Can you give me data insert statements for Staff and StaffDate tables for this gentlemen and month May2012.
May 3, 2012 at 9:38 am
Eugene Elutin (5/3/2012)
I need underlying data, not the results.Can you give me data insert statements for Staff and StaffDate tables for this gentlemen and month May2012.
Really Sorry Eugene, actually I quite new to SQL.pls find the sample data below:
----------------------------------
INSERT INTO [Staff]
VALUES('30', 'Vishal', '07:00:00', '07:00:00', '07:00:00', '07:00:00', '07:00:00')
INSERT INTO [StaffDay]
VALUES('30', 'Vishal', '2012-05-10', 'Mon', '00:00:00.0000000','H','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Vishal', '2012-05-11', 'Tue', '00:00:00.0000000','H','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Vishal', '2012-05-12', 'Wed', '07:46:00.0000000','','00:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Vishal', '2012-05-13', 'Thu', '07:25:00.0000000','','00:00:00.0000000')
----------------------------------
Thanks
May 3, 2012 at 9:49 am
Based on data provided there are no discrepancies in a query results:
Employee NameMonthYearWorking HoursActual HoursHrs_DiffMin_DiffS Cat2 HoursM Cat2 HoursB Cat2 HoursR Cat2 Hours
VishalMay201228:00:0029:11:001110:00:000:00:000:00:000:00:00
Working Hours are correct: 28:00:00 (as per 7:00:00 * 4)
Actual Hours are correct: 29:11:00 (as per TimeNetin of 07:25:00 + TimeNetin of 07:46:00 + Cat2 of 7:00:00 and another Cat2 of 7:00:00
Cat2 for S, M, B and R are 0:00:00 as no such cat2 type records found.
May 3, 2012 at 9:59 am
Eugene Elutin (5/3/2012)
Based on data provided there are no discrepancies in a query results:
Employee NameMonthYearWorking HoursActual HoursHrs_DiffMin_DiffS Cat2 HoursM Cat2 HoursB Cat2 HoursR Cat2 Hours
VishalMay201228:00:0029:11:001110:00:000:00:000:00:000:00:00
Working Hours are correct: 28:00:00 (as per 7:00:00 * 4)
Actual Hours are correct: 29:11:00 (as per TimeNetin of 07:25:00 + TimeNetin of 07:46:00 + Cat2 of 7:00:00 and another Cat2 of 7:00:00
Cat2 for S, M, B and R are 0:00:00 as no such cat2 type records found.
Oh really , I dont know why it shows up Actual hours as 15:11:00 when I run it in SQL. Also I am unable to get the months format from January to December format now.
May 3, 2012 at 10:04 am
Eugene Elutin (5/3/2012)
Based on data provided there are no discrepancies in a query results:
Employee NameMonthYearWorking HoursActual HoursHrs_DiffMin_DiffS Cat2 HoursM Cat2 HoursB Cat2 HoursR Cat2 Hours
VishalMay201228:00:0029:11:001110:00:000:00:000:00:000:00:00
Working Hours are correct: 28:00:00 (as per 7:00:00 * 4)
Actual Hours are correct: 29:11:00 (as per TimeNetin of 07:25:00 + TimeNetin of 07:46:00 + Cat2 of 7:00:00 and another Cat2 of 7:00:00
Cat2 for S, M, B and R are 0:00:00 as no such cat2 type records found.
I just summed them individually and it worked:
SUM(DATEDIFF(SECOND,'00:00:00',s.Timenetin)) + SUM(DATEDIFF(SECOND,'00:00:00',ISNULL(s.cat2_hours, '00:00:00'))) AS AH
However, I am still unable to get back the year format display from January to December, it displaying in ascending order format from April to September now.
Would you know how to get it to display like that ?
May 3, 2012 at 10:12 am
...
Oh really , I dont know why it shows up Actual hours as 15:11:00 when I run it in SQL. Also I am unable to get the months format from January to December format now.
Let me guess.
I think it's because the underlying data you gave me are not exactly the same as you have when you run the query at your place.
You need to look into underlying data and ranges carefully.
May 3, 2012 at 10:15 am
...
However, I am still unable to get back the year format display from January to December, it displaying in ascending order format from April to September now.
Would you know how to get it to display like that ?
Sorry I don't get it. Display like what? May be you need to change the filter in your Where clause?
I'm off for today. Hopefully, it will be someone else able to help here.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply