Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 15th, 2004, 10:50 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default complicated query

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

 
Old June 16th, 2004, 02:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 16th, 2004, 07:54 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default

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


 
Old June 17th, 2004, 03:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

what Database are you using? ACCESS or SQL?

_________________________
-Vijay G
Strive for Perfection
 
Old June 17th, 2004, 04:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Seemingly Complicated Query help needed krashed SQL Language 5 March 31st, 2006 01:02 AM
How do you query this complicated thing? sprion SQL Language 5 January 6th, 2005 02:06 AM
Complicated Query roniestein Access 3 December 13th, 2003 10:34 AM
Too Complicated shiraz BOOK: ASP.NET Website Programming Problem-Design-Solution 3 September 23rd, 2003 10:06 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.