[RESOLVED] Count Absent for a Month, Quartely, Weekly, etc using Query
let say i have records like this (see the picture to make it simpler)
and the following question:
1. calculate the maximum Absent in a row for Employee A for a Week(Sunday to Saturday) in a Month?
2. calculate the Absent for Employee A for a Month?
3. calculate the Absent for Employee A for a Year?
the answer for it are:
1. Sep 2007, Week 3, 3 in a row
Sep 2007, Week 5, 2 in a row
Oct 2007, Week 2, 2 in a row
2. Sep 2007, 6 Absent
Oct 2007, 4 Absent
3. for the ilustrated it would be Total Absent = 10
can Query do something like that? using what? pls give some insight
PS: i'm using SQL Server 2000
thanks
Last edited by erickwidya; Nov 6th, 2007 at 02:02 AM.
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
Count employee's absent is a bit tricky, since you want to request a data that shouldn't exist (an employee who was absent meant there will be no data in history table).
To take a non exist data, you have to reverse the querry. Let's say that you have an employee table and absent table. I'll try to explain in a few steps.
First step is to make a querry that will display days that the employee was present. To make short, make querry like: select all days where the month is [insert month here] and the employee id is [insert employee id here]. Let's assume that the month is 9 (september) and employee id is 1. This querry will result list of days where that employee present in september.
Second, make querry that display all days in spesific month. Example if we use select all days where month is 9, it must return 30 rows.
Last step, make querry combined with both 2 querries above and reverse the first querry. It should look like this: select all days where month is 9 and those days are not in (select all days where the month is 9 and the employee id is 1).
Those steps are the answer of number 2. To solve number 1 and 3, use some querry modification with week(number), and year(number) functions. You can see their usage in MSDN.
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
thanks michaelrawi
i'll give it a try first..and will post with the answer and table structure if it's resolved
so if anyone have an insight, please share..
thanks
PS: hey, we came from same country
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
i can count Total for a Week but can't count for Absent in a row..
any idea?
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
i used this
Code:
SELECT DATEPART(ww, Tanggal),
SUM(CONVERT(INT,isAbsen))
FROM Table1
GROUP BY DATEPART(ww, Tanggal)
GO
The structure of the table are
Table1 = Tanggal (datetime) + isAbsen (bit)
isAbsen
0: no Absent
1: Absent
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
isAbsen? So, you mean that if someone were absent, that employee was given 0 isAbsen status in your database that day? If so, then who would input that employee status? I thought that you make an application based on automated absent clock...
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
TABLE1
Tanggal AS datetime
isAbsen AS bit
So, you mean that if someone were absent, that employee was given 0 isAbsen status in your database that day?
given by number 1
If so, then who would input that employee status?
-> not quite understand
this example just to make it simple
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
Originally Posted by erickwidya
-> not quite understand
When you use an automated absent machine (ex: fingerprint, swap cards etc), if an employee present, he will swap a card (or use his finger), then the system will insert his data with given date when the employee arrived. That means, if he was absent, the system won't insert a row for him. To speak, there will be no record about the employee that day. This is a state that he was absent that day.
But in your case, you have an isAbsen field to mark an employee. So, when an employee was absent that day, the system will still insert a data that day. If this is your system, then my guess is there's someone else who operating an application that will monitor employee's absent? (It means that your system is not live then)
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
thanks anhn and michaelrawi
If this is your system, then my guess is there's someone else who operating an application that will monitor employee's absent? (It means that your system is not live then)
-> well, it's not really my system..i just have some thought regarding select record in a row and the Absent is come up for an example of it..
When you use an automated absent machine (ex: fingerprint, swap cards etc), if an employee present, he will swap a card (or use his finger), then the system will insert his data with given date when the employee arrived. That means, if he was absent, the system won't insert a row for him. To speak, there will be no record about the employee that day. This is a state that he was absent that day.
-> this one is the same in the concept i think..just use the one that suit u..
Here is the query written in Access, you may have an idea to convert it to the language of SQL Server
-> i try to convert it then..
if any other insight, please post it
thanks
Last edited by erickwidya; Nov 1st, 2007 at 10:27 PM.
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
can u explain about this? why u come up with those formula?
seem SQL doesnt have Oct function..i'm stuck again i think
Last edited by erickwidya; Nov 5th, 2007 at 09:48 PM.
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
Actually, I did try to convert a number to Binary String, but VB does not have Bin() function, so I used Oct(), it also works with Hex().
However, I did make it become more complicated. You can change that to:
Re: Count Absent for a Month, Quartely, Weekly, etc using Query
thx anhn
it works now after u give some example
here is the code
Code:
SET DATEFIRST 1
SELECT YEAR([Tanggal]) AS [Year],
MONTH([Tanggal]) AS [Month],
DATEPART(ww,[Tanggal]) AS [Week] ,
CONVERT(NVARCHAR(12), Min([Tanggal])) AS [From],
CONVERT(NVARCHAR(12), Max([Tanggal])) AS [To],
CONVERT(INT, Max([Tanggal]) - Min([Tanggal]))+1 AS [DaysInWeek],
SUM(CONVERT(INT, isAbsen)) AS [TotalAbsen],
RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7) AS OnOFf,
CASE
WHEN PATINDEX('%1111111%', RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7)) > 0 THEN 7 ELSE
CASE WHEN PATINDEX('%111111%', RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7)) > 0 THEN 6 ELSE
CASE WHEN PATINDEX('%11111%', RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7)) > 0 THEN 5 ELSE
CASE WHEN PATINDEX('%1111%', RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7)) > 0 THEN 4 ELSE
CASE WHEN PATINDEX('%111%', RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7)) > 0 THEN 3 ELSE
CASE WHEN PATINDEX('%11%', RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7)) > 0 THEN 2 ELSE
CASE WHEN PATINDEX('%1%', RIGHT('0000000' + CONVERT(NVARCHAR(7), SUM(isAbsen*(POWER(10,(7-DATEPART(dw,Tanggal)))))),7)) > 0 THEN 1 ELSE 0
END
END
END
END
END
END
END AS [AbsenInRow]
FROM Table1
GROUP BY Year([Tanggal]), Month([Tanggal]), DatePart(ww,[Tanggal])
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -