1
Answer

i Will try to add current month only

Guest User

Guest User
2y
486
1 
USE [BiometricDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetTop3VolunteerReport]    Script Date: 30/11/2022 11:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetTop3VolunteerReport]
AS
BEGIN
-- Top 3 Volunteer Report
SELECT 
	TOP 3 v.[displayfullname] AS [Full Name], 
	
	SUM(CASE when T.shift = 'Morning' then T.points else 0 end)	 as MPoints,
	SUM(CASE when T.shift = 'Evening' then T.points else 0 end) as EPoints,
	SUM(CASE when T.shift = 'Special' then T.points else 0 end) as ExPoints,
	SUM(isnull(T.points, 0)) as TotalPoints,
	COUNT(CASE when T.shift = 'Morning' then T.points end) as MDays,
	COUNT(CASE when T.shift = 'Evening' then T.points end) as EDays,
	COUNT(CASE when T.shift = 'Special' then T.points end) as ExDays,
	COUNT(T.points) as TotalDays,
	CAST( ( SUM( isnull( duration, 0 ) )/60 ) + ( ( SUM( isnull( duration, 0 ) )%60 )/100.0 ) as DECIMAL(16, 2) ) as TotalDuration,
	CAST(v.[profilephoto] as binary) AS [Profile Photo]
FROM 
	tblattendancelog t INNER JOIN 
	tblvolunteer v ON v.[volunteerid] = t.[volunteerid] 
	 
WHERE
	t.[type] = 'Present' 
GROUP BY
	v.[volunteerid], v.[displayfullname], CAST(v.[profilephoto] as binary) 
ORDER BY 
	SUM(duration) DESC

END

Also current month find auto with store proc without parameter

i Have image store from < binary Data>  call me on this store proc

i will try this binary data in stroc proc 

??????

Answers (1)
Next Recommended Forum