November 24, 2011 at 6:35 am
Hi,
I have a Attendance table named say Attendance which has Class and Status along with some other fields.Class will have the name of the class the student is in,whereas the status will have the Absent/Present data.There is another table named say ClassMaster which is the master which will have all the classnames and there is a column of ClassTeacher which will have the EmployeeCode.This employeeCode comes from a table EmployeeMaster.My requirement is i want to get the count of Absent students in Attendance table depending on the Class.For Example if there are two class say ClassA and ClassB,then i should get the count of the Students from Attendance table that belong to Class A and Class B.How cn i do this in one Query.Please guide
November 24, 2011 at 7:08 am
when you ask for any sort of help in the forum, pleae provide the table structure and the sample data in the form of insert statements.
Select name,Count(status) From Attendance
where status = 'Absent'
Group by Name,status
May be you are looking for a Query something like this.
did n't under stand why you are pointing out the tables Classsmaster and other tables.
November 24, 2011 at 7:29 am
I have three Tables.One is
EmployeeMaster(EmpCode,EmpName,MobileNo)
Sample Date EmployeeMaster(1,SameerKhan,4512541321)
ClassMaster(ClassId,ClassName,EmpCode)
Sample Data ClassMaster(1,ClassA,1)
Here EmployeeCode in ClassMaster is the EmpCode from EmployeeMaster.
Attendance(StudentID,Class,Status)
sample data Attendance(445,ClassA,Absent)
Here Class will come from ClassMaster
Now suppose there are 3 students record in Attendance of them 2 are having status='Absent'.Then i should get the number of students absent
and to which class and who is the Class teacher and what his mobile No.
Something like this below
NoOfAbsents 2
Class Class A
Teacher SameerKhan
Mobile 4512541321
Text in the bold are Column Names and Text in Italic are Expected Data.
SMK
Software Engineer
India
November 24, 2011 at 7:46 am
SameerKKhan (11/24/2011)
I have three Tables.One isEmployeeMaster(EmpCode,EmpName,MobileNo)
Sample Date EmployeeMaster(1,SameerKhan,4512541321)
ClassMaster(ClassId,ClassName,EmpCode)
Sample Data ClassMaster(1,ClassA,1)
Here EmployeeCode in ClassMaster is the EmpCode from EmployeeMaster.
Attendance(StudentID,Class,Status)
sample data Attendance(445,ClassA,Absent)
Here Class will come from ClassMaster
Now suppose there are 3 students record in Attendance of them 2 are having status='Absent'.Then i should get the number of students absent
and to which class and who is the Class teacher and what his mobile No.
Something like this below
NoOfAbsents 2
Class Class A
Teacher SameerKhan
Mobile 4512541321
Text in the bold are Column Names and Text in Italic are Expected Data.
SMK
Software Engineer
India
This should be what you're after: -
SELECT away.NoOfAbsents, cls.ClassName, emp.EmpName, emp.MobileNo
FROM (SELECT COUNT(StudentID) AS NoOfAbsents, Class
FROM Attendance
WHERE Status = 'Absent'
GROUP BY Class) away
INNER JOIN ClassMaster cls ON away.Class = cls.ClassName
INNER JOIN EmployeeMaster emp ON cls.EmpCode = emp.EmpCode
This is based off the following sample data and ddl
CREATE TABLE EmployeeMaster(EmpCode INT, EmpName VARCHAR(20), MobileNo BIGINT)
INSERT INTO EmployeeMaster
SELECT 1, 'SameerKhan', 4512541321
CREATE TABLE ClassMaster(ClassId INT, ClassName VARCHAR(20), EmpCode INT)
INSERT INTO ClassMaster
SELECT 1, 'ClassA', 1
CREATE TABLE Attendance(StudentID INT, Class VARCHAR(20), Status VARCHAR(20))
INSERT INTO Attendance
SELECT 445, 'ClassA', 'Absent'
November 25, 2011 at 1:13 am
hi thank u so much for the reply,
i want to add another filter to the Query i.e the Date.There is a Date column in Attendance.
I want to get the records that are Absent and Date='Current Date'.I have tried the below query but its not giving me Data.
SELECT away.NoOfAbsents,cls.ClassName, emp.EmpName, emp.MobileNo
FROM (SELECT COUNT(RegNo) AS NoOfAbsents, Class FROM Attendance WHERE
convert(varchar(10),Date,111)=convert(varchar(10),getdate(),111) and
Status = 'Absent' GROUP BY Class) away
INNER JOIN ClassMaster cls ON away.Class = cls.ClassName
INNER JOIN EmployeeMaster emp ON cls.EmpCode= emp.EmpCode
How can i use the Date filter as well
November 25, 2011 at 1:42 am
SameerKKhan (11/25/2011)
hi thank u so much for the reply,i want to add another filter to the Query i.e the Date.There is a Date column in Attendance.
I want to get the records that are Absent and Date='Current Date'.I have tried the below query but its not giving me Data.
SELECT away.NoOfAbsents,cls.ClassName, emp.EmpName, emp.MobileNo
FROM (SELECT COUNT(RegNo) AS NoOfAbsents, Class FROM Attendance WHERE
convert(varchar(10),Date,111)=convert(varchar(10),getdate(),111) and
Status = 'Absent' GROUP BY Class) away
INNER JOIN ClassMaster cls ON away.Class = cls.ClassName
INNER JOIN EmployeeMaster emp ON cls.EmpCode= emp.EmpCode
How can i use the Date filter as well
Using the following sample data: -
CREATE TABLE EmployeeMaster(EmpCode INT, EmpName VARCHAR(20), MobileNo BIGINT)
INSERT INTO EmployeeMaster
SELECT 1, 'SameerKhan', 4512541321
CREATE TABLE ClassMaster(ClassId INT, ClassName VARCHAR(20), EmpCode INT)
INSERT INTO ClassMaster
SELECT 1, 'ClassA', 1
CREATE TABLE Attendance(StudentID INT, Class VARCHAR(20), Status VARCHAR(20), Date DATETIME)
INSERT INTO Attendance
SELECT 445, 'ClassA', 'Absent', '2011-11-25'
UNION ALL SELECT 445, 'ClassA', 'Absent', '2011-11-24'
UNION ALL SELECT 445, 'ClassA', 'Absent', '2011-11-23'
UNION ALL SELECT 446, 'ClassA', 'Absent', '2011-11-25 12:00:00'
SELECT away.NoOfAbsents, cls.ClassName, emp.EmpName, emp.MobileNo
FROM (SELECT COUNT(StudentID) AS NoOfAbsents, Class
FROM Attendance
WHERE Status = 'Absent'
AND Date >= DATEADD(dd, DATEDIFF(dd,0, GETDATE()),0)
AND Date < DATEADD(dd, DATEDIFF(dd,0, GETDATE()+1),0)
GROUP BY Class) away
INNER JOIN ClassMaster cls ON away.Class = cls.ClassName
INNER JOIN EmployeeMaster emp ON cls.EmpCode = emp.EmpCode
November 25, 2011 at 3:03 am
Cant i use the Date filter with (yyyy/MM/dd) format only.Just to check the year,Month,Day and bring the time part in the picture.
cant i use the- convert(varchar(10),getDate,111) to compare the Date.
As because of this comparison my time part will not come in Picture.
November 25, 2011 at 3:14 am
SameerKKhan (11/25/2011)
Cant i use the Date filter with (yyyy/MM/dd) format only.Just to check the year,Month,Day and bring the time part in the picture.cant i use the- convert(varchar(10),getDate,111) to compare the Date.
As because of this comparison my time part will not come in Picture.
The way that I've done the filter pick up everything that happened today and uses any available index on the "Date" column. The way you want to do it will use a table scan which is infinitely slower. Your choice though.
Here's some evidence.
SET NOCOUNT ON
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 100000 + CAST('2000' AS DATETIME) AS randomDate
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Add index
CREATE NONCLUSTERED INDEX [test_index]
ON #testEnvironment (randomDate)
PRINT '========== BASELINE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(randomDate)
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== YOUR WAY =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(randomDate)
FROM #testEnvironment
WHERE CONVERT(VARCHAR(10),randomDate,111) = CONVERT(VARCHAR(10),GETDATE(),111)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== MY WAY =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(randomDate)
FROM #testEnvironment
WHERE randomDate >= DATEADD(dd, DATEDIFF(dd,0, GETDATE()),0)
AND randomDate < DATEADD(dd, DATEDIFF(dd,0, GETDATE()+1),0)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________000000000005'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 180 ms.
================================================================================
========== YOUR WAY ==========
Table '#testEnvironment____________________________________________________________________________________________________000000000005'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 547 ms, elapsed time = 547 ms.
================================================================================
========== MY WAY ==========
Table '#testEnvironment____________________________________________________________________________________________________000000000005'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
================================================================================
See the speed difference? Both methods result in the same "COUNT", but my way is considerably less IO intensive which translates into a considerable speed advantage.
November 25, 2011 at 3:26 am
Thanks mate for ur Help.
Im done with my issue.
Thanks again.
M i suppose to mark as asnwered or solved for this question.I dont find any such Option
November 25, 2011 at 3:39 am
hello again,
i have one more query.Can i get the Date and Hour using a Query.
I want to Insert the Date along with the hour part in the Table.Like this 2011/11/25 11
Here 11 is the hour part.
How cn i achieve this?
November 25, 2011 at 3:46 am
SameerKKhan (11/25/2011)
hello again,i have one more query.Can i get the Date and Hour using a Query.
I want to Insert the Date along with the hour part in the Table.Like this 2011/11/25 11
Here 11 is the hour part.
How cn i achieve this?
That sort of formatting is for the presentation layer. It is faster in the database to keep a DATETIME as a DATETIME than to convert it to a VARCHAR.
You can do it, but it's generally not a good idea. Why do you want to?
DECLARE @Date AS DATETIME
SET @Date = '2011-11-25 10:45:00'
SELECT REPLACE(CONVERT(VARCHAR(13),@Date,20),'-','/')
November 25, 2011 at 3:52 am
hello again,
i have one more query.Can i get the Date and Hour using a Query.
I want to Insert the Date along with the hour part in the Table.Like this 2011/11/25 11
Here 11 is the hour part.
How cn i achieve this?
November 25, 2011 at 3:52 am
SameerKKhan (11/25/2011)
hello again,i have one more query.Can i get the Date and Hour using a Query.
I want to Insert the Date along with the hour part in the Table.Like this 2011/11/25 11
Here 11 is the hour part.
How cn i achieve this?
Cadavre (11/25/2011)
That sort of formatting is for the presentation layer. It is faster in the database to keep a DATETIME as a DATETIME than to convert it to a VARCHAR.You can do it, but it's generally not a good idea. Why do you want to?
DECLARE @Date AS DATETIMESET @Date = '2011-11-25 10:45:00'
SELECT REPLACE(CONVERT(VARCHAR(13),@Date,20),'-','/')
November 25, 2011 at 4:11 am
ok got the Idea.Im Saving my Data Directly using getDate().
Thanks for all the Help.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply