Sql server ,Group by [Resolved]

Posted by Raja_89 under Sql Server on 2/15/2016 | Points: 10 | Views : 286 | Status : [Member] | Replies : 3
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




Responses

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

Up
0
Down

Resolved
@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

Up
0
Down
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

Up
0
Down
Nice tips to mark.. Keep it up

Feel free to share informations 9731764134
mail Id ' adityagupta200@gmail.com
Thanks

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

Login to post response