Sql server ,Group by [Resolved]

Posted by Raja_89 under Sql Server on 2/15/2016 | Points: 10 | Views : 1368 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hai

How can i achieve the desired result for

tbl_id name course login time logout Mode
1 r 1 2016-02-15 15:47:04.000 2016-02-15 15:49:18.000 a
2 y 1 2016-02-15 13:47:04.000 2016-02-15 15:49:18.000 b
3 f 2 2016-02-15 11:47:04.000 2016-02-15 15:49:18.000 a
4 2 3 2016-02-15 12:47:04.000 2016-02-15 15:49:18.000 d

Output

Year Month Level p
2016 Feb a 1
2016 Feb b 1

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rajnilari2015 on: 2/15/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

0	@Raja_89, Please try this Declare @t table(tbl_id int identity,name varchar(10),course varchar(10),[login time] datetime, logout datetime,Mode varchar(10)) Insert into @t values ('r','1','2016-02-15 15:47:04.000','2016-02-15 15:49:18.000','a'), ('y','1','2016-02-15 13:47:04.000','2016-02-15 15:49:18.000','b'), ('f', '2' ,'2016-02-15 11:47:04.000' ,'2016-02-15 15:49:18.000', 'a'), ('2', '3' ,'2016-02-15 12:47:04.000', '2016-02-15 15:49:18.000', 'b') SELECT Year,Month,Level,p FROM( SELECT Year = YEAR([login time]) ,Month=LEFT(datename(month, [login time]),3) ,Level=Mode ,p=Course ,Rn=ROW_NUMBER() OVER(Partition By Mode Order By [login time] DESC) FROM @t)X WHERE X.Rn =1 Output Year Month Level p 2016 Feb a 1 2016 Feb b 1 Hope this helps -- Thanks & Regards, RNA Team Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

@Raja_89,
Please try this

Declare @t table(tbl_id int identity,name varchar(10),course varchar(10),[login time] datetime, logout datetime,Mode varchar(10))

Insert into @t values

('r','1','2016-02-15 15:47:04.000','2016-02-15 15:49:18.000','a'),

('y','1','2016-02-15 13:47:04.000','2016-02-15 15:49:18.000','b'),

('f', '2' ,'2016-02-15 11:47:04.000' ,'2016-02-15 15:49:18.000', 'a'),

('2', '3' ,'2016-02-15 12:47:04.000', '2016-02-15 15:49:18.000', 'b')



SELECT Year,Month,Level,p

FROM(	

		SELECT 

			Year = YEAR([login time])

			,Month=LEFT(datename(month, [login time]),3)

			,Level=Mode

			,p=Course	

			,Rn=ROW_NUMBER() OVER(Partition By Mode Order By [login time] DESC)

		FROM @t)X

WHERE X.Rn =1

Output

Year	Month	Level	p

2016	Feb	a	1

2016	Feb	b	1

Hope this helps

--
Thanks & Regards,
RNA Team

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 2/16/2016 [Member] Starter | Points: 25

0	Hai Thanks for the support Could you tell above query formation and also the above code for the above table how can i calculate the total no of present,absent and total strength Sample table tbl_id name course login time logout Mode 1 r 1 2016-02-15 15:47:04.000 2016-02-15 15:49:18.000 a 2 y 1 2016-02-15 13:47:04.000 2016-02-15 15:49:18.000 b 3 f 2 2016-02-15 11:47:04.000 2016-02-15 15:49:18.000 a 4 2 3 2016-02-15 12:47:04.000 2016-02-15 15:49:18.000 d Output Table Year Month Level p Total present Total absent Total Strength 2016 Feb a 1 1 0 1 2016 Feb b 1 0 1 1 In simple query will result the fetch the no of present,absent,total for the corresponding course ,for that year say 2016 and for Feb month Thanks Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Hai

Thanks for the support
Could you tell above query formation and also
the above code for the above table
how can i calculate the total no of present,absent and total strength

Sample table
tbl_id name course login time logout Mode
1 r 1 2016-02-15 15:47:04.000 2016-02-15 15:49:18.000 a
2 y 1 2016-02-15 13:47:04.000 2016-02-15 15:49:18.000 b
3 f 2 2016-02-15 11:47:04.000 2016-02-15 15:49:18.000 a
4 2 3 2016-02-15 12:47:04.000 2016-02-15 15:49:18.000 d

Output Table
Year Month Level p Total present Total absent Total Strength
2016 Feb a 1 1 0 1
2016 Feb b 1 0 1 1

In simple

query will result the fetch the no of present,absent,total for the corresponding course ,for that year say 2016 and for Feb month

Thanks

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amatya on: 2/16/2016 [Member] Silver | Points: 25

0	Nice tips to mark.. Keep it up Feel free to share informations. mail Id ' adityagupta200@gmail.com Thanks Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts