[RESOLVED] Count Absent for a Month, Quartely, Weekly, etc using Query

[erickwidya]

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

[michaelrawi]

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.

Regards

Michael

[erickwidya]

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

[erickwidya]

i can count Total for a Week but can't count for Absent in a row..

any idea?

[michaelrawi]

If you can count total for a week, IMO you should know how to count absent in a row as well. How's your method counting for a week ?

[erickwidya]

i used this

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

[michaelrawi]

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...

Could you show me what your table look like?

[erickwidya]

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

[anhn]

Here is the query written in Access, you may have an idea to convert it to the language of SQL Server:

   SQL = "SELECT Year([Tangal]) AS [Year], Month([Tangal]) AS [Month],"
   SQL = SQL & " DatePart('ww',[Tangal]) AS Week,"
   SQL = SQL & " Min([Tangal]) AS [From], Max([Tangal]) AS [To], [To]-[From]+1 AS DaysInWeek,"
   SQL = SQL & " Right('0000000' & Oct(Sum([isAbsen]*8^(7-Weekday([Tangal],1)))),[DaysInWeek]) AS OnOff,"
   SQL = SQL & " Sum([isAbsen]) AS DaysAbsent,"
   SQL = SQL & " IIf(InStr([OnOff], '1111111'), 7, IIf(InStr([OnOff], '111111'), 6,"
   SQL = SQL & " IIf(InStr([OnOff], '11111'), 5, IIf(InStr([OnOff], '1111'), 4,"
   SQL = SQL & " IIf(InStr([OnOff], '111'), 3, IIf(InStr([OnOff], '11'), 2,"
   SQL = SQL & " IIf(InStr([OnOff], '1'), 1, 0))))))) As MaxAbsentInRow"
   SQL = SQL & " FROM Table1"
   SQL = SQL & " GROUP BY Year([Tangal]), Month([Tangal]), DatePart('ww',[Tangal]);"

Attached image is the output.

[michaelrawi]

-> 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)

That's why I ask what your table look like...

[erickwidya]

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

[michaelrawi]

You're welcome (sama-sama )

[erickwidya]

anhn

Right('0000000' & Oct(Sum([isAbsen]*8^(7-Weekday([Tangal],1)))),[DaysInWeek]

can u explain about this? why u come up with those formula?

seem SQL doesnt have Oct function..i'm stuck again i think

[anhn]

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:

Right('0000000' & CStr(Sum([isAbsen]*10^(7-Weekday([Tangal],1)))),[DaysInWeek])

or

Format(Sum([isAbsen]*10^(7-Weekday([Tangal],1)))), String([DaysInWeek], '0'))

Here is how it works:

Day  Value  Weekday()  7-Weekday()  Num
Sun     0         1            6    0*10^6 = 0000000
Mon     1         2            5    1*10^5 = 0100000
Tue     1         3            4    1*10^4 = 0010000
Wed     0         4            3    0*10^3 = 0000000
Thu     1         5            2    1*10^2 = 0000100
Fri     0         6            1    0*10^1 = 0000000
Sat     0         7            0    0*10^0 = 0000000
----------------------------------------------------
                                  Sum(Num) = 0110100

[erickwidya]

thx anhn

it works now after u give some example

here is the 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])