complicated query

rajanikrishna · June 15th, 2004, 10:50 PM

Hi friends,

I have 3 tables. MSAccess
holidays --> indno,trdate
leaves --> lindex,empid,leavedate,nodays(0.5/1 only)
attendance --> attindex,empid,attdate

The company pays for public holidays, if the emp is not absent just before or after the public holiday. For ex, if the emp takes leave on 2,5,6 dates. and 4 is public holiday. 3 is sunday. We will not pay for the public holiday (on 4th). Like that i want to count the unpaid public holidays in that month payslip. Can u give me the query.
I tried checking the leavedate and deducting/adding one by one date and searching for public holiday or sunday. But if the emp takes continuous leaves, the same public holiday counts no of times.

Thanx in advance

happygv · June 16th, 2004, 02:28 PM

In the example you posted here (2,5,6 dates, and 4 is public holiday, 3 is sunday), how many records are there in LEAVES table for this?

Are there separate records for 2, 5, and 6 in LEAVES table? I assume there won't be a record stored for 4 in that table.

Is that so, the HOLIDAYS table contain all the public holidays in trdate column?

Cheers!

_________________________
-Vijay G

Strive for Perfection

rajanikrishna · June 16th, 2004, 07:54 PM

yes,
there are seperate records for 2,5,6 in leaves table and holidays table contains only public holidays.

happygv · June 17th, 2004, 03:57 PM

what Database are you using? ACCESS or SQL?

_________________________
-Vijay G

Strive for Perfection

happygv · June 17th, 2004, 04:17 PM

This works fine for SQL server

Code:

select trdate 
from leaves, holidays
where 
((leavedate = dateadd(dd,-1,trdate) and leavedate = dateadd(dd,1,trdate)) 
or
(leavedate = dateadd(dd,-1,trdate) and datename(dw,trdate+1) = 'sunday')
or
(leavedate = dateadd(dd,1,trdate) and datename(dw,trdate-1) = 'sunday'))
and month(trdate) = month(getdate())
and empid=1

where Month for which you wanted to generate the details and EMPID marked red in the above code can be variables/parameters that you can pass to this.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection