Click here to Skip to main content
15,890,724 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
USE [Prom_test]
GO
/****** Object:  StoredProcedure [dbo].[usp_USER_LEAVE_GetLeaveDetail]    Script Date: 10/03/2013 17:04:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_USER_LEAVE_GetLeaveDetail]
					@user_id  INT = NULL,
					@year     INT = NULL,
					@month	  INT = NULL
AS
BEGIN

		DECLARE @startMonth			DATETIME

		BEGIN			
			SET @startMonth= DATEADD(MONTH, (@year-1900)*12 + @month - 1, 0)
		END

		SET NOCOUNT ON

		DECLARE @full_name VARCHAR(256)
		SELECT @full_name = FullName FROM [dbo].[TM_User] WHERE [TM_UserID] = @user_id

		 
		 	 
		SELECT rc.ref_name,
			 rc.[ref_cat_code],
			 @user_id AS [userid],
			 @full_name AS [fullname],
			CASE WHEN count_days IS NULL THEN 0 ELSE count_days END AS count_days,
			[active_rec_in]
		FROM 
			  (  SELECT  
					  ref_name,
					  [ref_cat_code],
					  ref_type	
				  FROM 
					  [dbo].[Reference_Categories] rc 
					WHERE ref_type = 'LEAVE' and active_rec_in = 1
			  )rc 
		LEFT JOIN
			(
				SELECT 
					  sum(case when user_leave_type=2 then 0.5 else 1 end) AS count_days,
					  ul.[ref_cat_id],
					  ul.[user_id],
					  [active_rec_in]						  	
				  FROM 
					[dbo].[User_Leave] ul  
				   INNER JOIN 
					  [dbo].[Timesheet] ts
				  ON
					ul.[user_leave_id] = ts.[user_leave_id] 
				  WHERE 
					((@Month IS NULL AND (YEAR([worked_dte]) = @year and  ul.[user_id] = @user_id)) OR(@Month IS NOT NULL AND ([worked_dte] >= @startMonth AND [worked_dte] < DATEADD (MONTH, 1, @startMonth))))
					AND ul.[user_id] = @user_id
					AND ul.[active_rec_in] = 1
				  GROUP BY 
					  ul.[ref_cat_id],
					  ul.[user_id],
					  ul.[active_rec_in]	
			) T 
		ON 
			rc.[ref_cat_code] =  T.[ref_cat_id]  

			
END
Posted
Updated 3-Oct-13 1:44am
v3
Comments
Member 10256268 3-Oct-13 7:35am    
executed out put is
count_days
0
0
0 like this i want to display
ZurdoDev 3-Oct-13 7:42am    
What? This is not clear. You want to remove everything past a period but then you say you want 0 0 0?
Member 10256268 3-Oct-13 7:43am    
in output count_days column displaying 0.0 i want to remove the value after point symbol
ZurdoDev 3-Oct-13 7:44am    
Why is it showing 0.0. None of us can execute your SQL and it isn't a simple SELECT statement so you need to be real clear as to where the issue is.
Member 10256268 3-Oct-13 7:48am    
in that 0.0 how to remove .0

1 solution

Hey there,

Here is what I think you want to do.
When there is .0 in count_days in this line
SQL
CASE WHEN count_days IS NULL THEN 0 ELSE count_days END AS count_days
then you want to remove .0 from it.

Try replacing this line:
SQL
CASE WHEN count_days IS NULL THEN 0 ELSE count_days END AS count_days
with this one:
SQL
CASE WHEN count_days IS NULL THEN 0 ELSE 
CASE WHEN  CharIndex( '.0',cast(count_days as varchar)) > 0  THEN  SUBSTRING(cast(count_days as varchar), 0, CharIndex( '.0',cast(count_days as varchar)))  ELSE  cast(count_days as varchar) END 
END AS count_days


if it gives a conversion error then try the 0 in this part of CASE
SQL
CASE WHEN count_days IS NULL THEN 0 ELSE
as '0'

I really hope it works.
Do let me know, if this is not what you were looking for.

Good luck.

Azee...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900