question

mcsc avatar image
mcsc asked

Summing records

I have the following case (see image) ![alt text][1] For Each student I need to sum periods (Ps) and absents (As) considering only records between Code =E (entry) and Code = L (leave) (if student did not leave, code is blank). Any idea how to do this in SQL (server 2012)? Thanks [1]: /storage/temp/2897-esempio.png
sql server 2012
esempio.png (30.8 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This looks like an assignment or homework - which isn't a problem, but if you can tell us how far you got in coding it (and where you are stuck), or what your thinking is on how to solve it, we'd be happy to help you along.
3 Likes 3 ·
Yup Agree with @Dave
0 Likes 0 ·

1 Answer

· Write an Answer
erlokeshsharma08 avatar image
erlokeshsharma08 answered
This one should work. So what I am doing is finding the max date for a particular student and school if the code is L. Then finding the sum based on the condition that the given date should be lesser than equal to the maximum date just calculated above. create table #sample (studentid int, school int, dat datetime, code varchar(2), a0 int, a1 int, a2 int, p0 int, p1 int, p2 int) insert into #sample values (2455, 4, '8/28/2015','E',0,1,0,1,1,1),(2455, 4, '8/29/2015','',0,1,0,1,1,1), (2455, 4, '8/30/2015','',0,1,0,1,1,1),(2455, 4, '8/31/2015','L',0,1,0,1,1,1),(1255, 4, '8/28/2015','E',0,1,0,1,1,1), (1255, 4, '8/29/2015','',0,1,0,1,1,1),(1255, 4, '8/30/2015','L',0,1,0,1,1,1),(2455, 4, '9/07/2015','E',0,1,0,1,1,1), (2455, 4, '9/08/2015','',0,1,0,1,1,1),(2455, 4, '9/09/2015','L',0,1,0,1,1,1), (1255, 4, '10/28/2015','E',0,1,0,1,1,1), (1255, 4, '10/28/2015','',0,1,0,1,1,1) SELECT S1.studentid, S1.school,SUM(A0)+SUM(A1)+SUM(A2) AS ABSENTS, SUM(P0)+SUM(P1)+SUM(P2) AS presents FROM #SAMPLE S1 INNER JOIN (SELECT studentid,school,MAX(DAT) AS DAT_DELIMILTER FROM #SAMPLE WHERE CODE='L' GROUP BY studentid,school ) S2 ON S1.studentid=S2.studentid AND S1.school=S2.school WHERE S1.DAT
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I am a beginner....and I know my question is a stupid one, so forgive me for my stupidity.... I did the sums of Ps and As grouping by StudentID and SchoolID, but I m lost when I need to pick only records between codes E and L and when L is not there.... I hope you can help me
0 Likes 0 ·
Hint try case statement....and please dont call yourself stupid....we all neeed to learn and grow
0 Likes 0 ·
I think code should not be a problem. You just need to sum individual sums of absents and presents and you will get cumulative presents and absents. Please post your query and we ll help you out.
0 Likes 0 ·
Sorry I do not get it.....maybe in my example i did not mention that same student could be exited and re-entered, so I may found other records with other dates for same student in same school see image![alt text][1] [1]: /storage/temp/2901-esempio.png
0 Likes 0 ·
esempio.png (34.2 KiB)
Thanks a TON, erlokeshsharma08 !!!!
0 Likes 0 ·
You are welcome!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.