How to get Current Year Data month wise

Question

hello  i am not get any idea how to do i have one table then and i want to show data year 2011 like month wise count entry Res_Name Jan feb mar apr...........................................DEC A 1 2 3 1 .....................................................5 . . . . B 3 1 2 1 .........................................................5 and tabel is ID res_id Menu Res_name Coment Date 1 2 4 A test 2011-12-23 16:51:07.587 5 2 2 11 A testing 2011-12-24 14:23:16.050 5 3 2 11 B test test test test 2011-12-24 14:23:42.523 5 4 2 11 C test test test test 2011-12-24 14:23:57.800 3 5 2 12 D testeasdasdasdasd 2011-12-24 16:10:18.713 5 so i am not geting any solution i try but i am not get month wise please give me some sql code thanks

Prabhu Raja · Answer

Hi Hope it will be useful one. SELECT YEAR(CreatedDate) as Years Month(CreatedDate) as MonthInNumbersCount(Month(CreatedDate)) as MonthCount FROM Table_2 GROUP BY YEAR(CreatedDate)Month(CreatedDate) If this post helps you then mark as Correct Answer Thank You

Dharmesh sharma · Answer

hello i am doing some diif this is my SP -- ============================================= -- Author: Dharmesh -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[USP_getComment_rating] @res_owner_id int --@menuid int AS BEGIN CREATE TABLE #temp( [Menuname] [varchar](max) NULL [Jan] [varchar](max) NULL [Feb] [varchar](max) NULL [Mar] [varchar](max) NULL [Apr] [varchar](max) NULL [May] [varchar](max) NULL [Jun] [varchar](max) NULL [Jul] [varchar](max) NULL [Aug] [varchar](max) NULL [Sep] [varchar](max) NULL [Oct] [varchar](max) NULL [Nov] [varchar](max) NULL [Dec] [varchar](max) NULL ) DECLARE @menuname varchar(max)@menu_id int Delete [foodpoodle].[softtech].[temp] where rest_id=@res_owner_id Delete [foodpoodle].[softtech].[temp1] where rest_owner_id=@res_owner_id DECLARE @menuid CURSOR SET @menuid=CURSOR FOR SELECT id from menu_item where restaurant_owner_id=@res_owner_id OPEN @menuid FETCH NEXT FROM @menuid INTO @menu_id WHILE @@FETCH_STATUS = 0 BEGIN select @menuname=menu_item from menu_item where id=@menu_id Insert Into [foodpoodle].[softtech].[temp1]([id][rest_id][menu][userid][username][comments][date][rating][rest_owner_id]) SELECT *@res_owner_id FROM [foodpoodle].[softtech].[comments] where menu=@menu_id AND Year([date])=YEAR(getdate()) --Insert Into temp1([id][rest_id][menu][userid][username][comments][date][rating]) SELECT * from temp where Month(date)=@month INSERT INTO #temp SELECT @menuname as 'Menuname' [softtech].UDF_Get_month_wise_rate(1@menu_id) as 'Jan'  [softtech].UDF_Get_month_wise_rate(2@menu_id) as 'Feb'  [softtech].UDF_Get_month_wise_rate(3@menu_id) as 'Mar'  [softtech].UDF_Get_month_wise_rate(4@menu_id) as 'Apr'  [softtech].UDF_Get_month_wise_rate(5@menu_id) as 'May'  [softtech].UDF_Get_month_wise_rate(6@menu_id) as 'Jun'  [softtech].UDF_Get_month_wise_rate(7@menu_id) as 'jul'  [softtech].UDF_Get_month_wise_rate(8@menu_id) as 'Aug'  [softtech].UDF_Get_month_wise_rate(9@menu_id) as 'Sep'  [softtech].UDF_Get_month_wise_rate(10@menu_id) as 'Oct'  [softtech].UDF_Get_month_wise_rate(11@menu_id) as 'Nov'  [softtech].UDF_Get_month_wise_rate(12@menu_id) as 'Dec' FETCH NEXT FROM @menuid INTO @menu_id END CLOSE @menuid DEALLOCATE @menuid SELECT * FROm #temp END and this my FUNCTION -- ============================================= -- Author: Dharmesh -- Create date: -- Description: -- ============================================= ALTER FUNCTION [softtech].[UDF_Get_month_wise_rate] (@month int@menuid int) RETURNS int AS BEGIN DECLARE @couter int@avgrating int@totalrating int --Insert Into temp([id][rest_id][menu][userid][username][comments][date][rating]) SELECT * FROM [foodpoodle].[softtech].[comments] where rest_id=@rest_id AND menu=@menuid AND Year([date])=YEAR(getdate()) --Insert Into temp1([id][rest_id][menu][userid][username][comments][date][rating]) SELECT * from temp where Month(date)=@month SELECT @couter=COUNT(id) FROM [foodpoodle].[softtech].[temp1] where [menu]=@menuid AND month([date])=@month SELECT @totalrating=SUM(rating) from [foodpoodle].[softtech].[temp1] where [menu]=@menuid AND month([date])=@month SET @avgrating=@totalrating/@couter return @avgrating END

Sushta Guthnar · Answer

Impressing many different ways of tryeing. Thanls everyone having much updtaes. Happy Inside Shusta

Aarti · Answer

Hi please try with below code: SELECT COUNT Distinct (*) MONTH ( Date ) FROM Table_Name GROUP BY MONTH ( Date );

Sushta Guthnar · Answer

Please be tryling the follower: SELECT * FROM ( SELECT YEAR(OrderDate) [Year] CASE MONTH(OrderDate) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 8 THEN 'September' WHEN 8 THEN 'October' WHEN 8 THEN 'November' WHEN 8 THEN 'December' END as [Month] SubTotal FROM Sales.SalesOrderHeader ) TableDate PIVOT ( SUM(SubTotal) FOR [Month] IN ( [January][February][March][April] [May][June][July][August] [September][October][November][December] ) ) PivotTable let me be giving you expert codeings Many thoughts and understandings Shusta

Dharmesh sharma · Answer

can you give some code of sql to understand esaly.........

Sushta Guthnar · Answer

you will be to add grouping to the outsides of the qry. After adding should groups will columning and conting. Shusta

Dharmesh sharma · Answer

thanks reply but i am not getting you ......... please describe me

Sushta Guthnar · Answer

Outsides For haveing year wise count. A groupby must including in that where you will be conting.It is appears like test is haveing times of 3 when looked. Many happy returns. Shusta