September 23, 2008 at 8:48 am
I have student attendance data. I want to calculate total number of school days between startdate and End date .I could write sql for startdate and End Date but eventhough i couldnot get it in the sameline. Startdate takes one line and End Date takes another line. I want to have all in one line. I am blank to calculate count between 2 days because i cant control startdate and enddate in one line.
i want like Like
studentno Startdate EndDate numberofdaysattended
942531 9/10/2007 6/24/2008 178
Startdate indicates in attendance as 'S'
Enddate indicate in attendance as ' E'
178 is only those value marked with characters. '-' is holiday
I have attached xls file for your review
Thanks
Ram
September 23, 2008 at 7:08 pm
Hi there,
Hope this helps... By the way, is the excel sample data from Excel or from SQL?
If from SQL, try this one...
DECLARE @EndDate DATETIME, @StartDate DATETIME
SELECT @StartDate=[Column_A] WHERE [Column_C]='S'
SELECT @EndDate=[Column_A] WHERE [Column_C]='E'
SELECT COUNT(1) FROM [Table]
WHERE [Column_C] <> '-'
AND [Column_A] BETWEEN @EndDate AND @StartDate
By the way, you'll be getting an error if there are more than 1 S or E in the table or if one or both of them does not exists. We'll if thats your case, try experementing with this code or ask again how. We'll be here to answer your questions.
Hope it helps...
Please tell me if this was helpful or if it needs some modifications... thanks ^__^
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 24, 2008 at 7:34 am
Hi,
It is from SQL. created a view from 3 tables.
Your sql gives an error. It didnot understand the coulmn name
It produces error .
DECLARE @EndDate DATETIME, @StartDate DATETIME
SELECT @StartDate=Date WHERE Attendance='S'
SELECT @EndDate=Date WHERE Attendance='E'
SELECT COUNT(1) FROM studentattendanceinfo
WHERE Attendance <> '-'
AND Date BETWEEN @EndDate AND @StartDate
It says invalid column name for date,attendance. Actually i did copy and paste
Thanks
Ram
September 24, 2008 at 8:12 am
Hi Ram
I'm not exactly sure what you're looking for: whether it's start and end date in the same row to get the days difference between the two, or to perform further processing on your table. The following statement gives start and end date in the same row and then joins back to the original table - this will give you some pointers about how to extract information from your data set. Note the use of MIN and MAX. If there's only one startdate and one end date per student, then the use of MIN and MAX is arbitrary, they are there to allow the aggregate to operate on the event date. If there's more than one start date or end date, then the use of MIN and MAX should be investigated further.
Cheers
ChrisM
[font="Courier New"]DROP TABLE #Attendance
CREATE TABLE #Attendance (EventDate DATETIME, studentno INT, attendance CHAR(1))
INSERT INTO #Attendance
SELECT '05/09/2007', 942531, '-' UNION ALL
SELECT '06/09/2007', 942531, 'D' UNION ALL
SELECT '07/09/2007', 942531, NULL UNION ALL
SELECT '08/09/2007', 942531, '-' UNION ALL
SELECT '09/09/2007', 942531, '-' UNION ALL
SELECT '10/09/2007', 942531, 'S' UNION ALL
SELECT '11/09/2007', 942531, 'P' UNION ALL
SELECT '12/09/2007', 942531, 'P' UNION ALL
SELECT '13/09/2007', 942531, 'P' UNION ALL
SELECT '14/09/2007', 942531, 'X' UNION ALL
SELECT '15/09/2007', 942531, '-' UNION ALL
SELECT '16/09/2007', 942531, '-' UNION ALL
SELECT '23/06/2008', 942531, 'C' UNION ALL
SELECT '24/06/2008', 942531, 'E'
SELECT a.*, '#' AS '#', d.*
FROM #Attendance a
INNER JOIN (
SELECT studentno, MAX(CASE attendance WHEN 'S' THEN EventDate ELSE NULL END) AS STARTDATE,
MIN(CASE attendance WHEN 'E' THEN EventDate ELSE NULL END) AS ENDDATE
FROM #Attendance
WHERE attendance IN ('S', 'E')
GROUP BY studentno
) d ON d.studentno = a.studentno
[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply