July 28, 2008 at 7:58 am
I am designing a site which is connected to our Time Management System's SQL Server. I want this particular site to give the user the access to enter their full name and when they press submit, it will show them there total holidays. (i.e. John Smith = 25 Days) I also want it to output the users 'Taken Holidays' and 'Planned Holidays'.
The structure of this Time Management Database is illustrated below:
tblEmployee tblHolEntitle tblDay
intEmpID sngEntitle intEmpID SngEntitle intEmpID intAbscenceType IntAbscenceCode
My Query looks something like this:
SELECT tblEmployee.strForename + ' ' + tblEmployee.strSurname AS Name, tblHolEntitle.sngEntitle, Tensor.dbo.tblDay.intAbsenceType,
COUNT(Tensor.dbo.tblDay.intAbsenceType) AS Expr1
FROM Tensor.dbo.tblEmployee, Tensor.dbo.tblHolEntitle, Tensor.dbo.tblDay
WHERE Tensor.dbo.tblEmployee.intEmpID = Tensor.dbo.tblHolEntitle.intEmpID AND Tensor.dbo.tblEmployee.intEmpID = Tensor.dbo.tblDay.intEmpID AND
(tblEmployee.intEmpID = tblHolEntitle.intEmpID) AND (Tensor.dbo.tblDay.intAbsenceCode = 8023) AND (Tensor.dbo.tblDay.dteDate >= '1/1/2008')
GROUP BY tblEmployee.strForename + ' ' + tblEmployee.strSurname, tblHolEntitle.sngEntitle, Tensor.dbo.tblDay.intAbsenceType
HAVING (tblEmployee.strForename + ' ' + tblEmployee.strSurname = ?)
Now, as i said above, when the user types in a name and presses enter, i want there Total Holiday to appear, but i also want their Taken and Planned Holiday to appear also. This is where i have had to add this new table tblDay. To do this i have to take into account the fields intAbscenceType and IntAbscenceCode. IntAbscenceType consists of numbers in where 1=0.5(AM) 2=0.5(PM) 3=1(Full Day) and 4=Timed. In the IntAbscenceCode there are many different abscence codes but the one i want to concentrate on is 8023 which = Holidays. I need to try and signify the half days so i assume i will need to insert an IF statement. When i run the query the current results i get is this:
Name SngEntitle intAbscenceType Expr1
John Smith 25 2 3
John Smith 25 3 19
The first line is telling me that the Employee John Smith is entitled to 25 Days. The intAbscenceType is telling me that half a day type is being used and the expression is a count of how many times the AbscenceType has been used. So this is telling me that a day and a half has been used. The second line tells me about the AbscenceType being 3 which is 1 full day. This particular user has taken 19 full days. So the total holiday this user has taken is 20.5 Days. They have 4.5 Days left. So the first question is how do i signify the half days? The second question is, i want the website to output the Holiday left. I have done this in Access and it works fine, the SQL Code in Access is:
SELECT dbo_tblEmployee.intEmpID, [strForename] & " " & [strSurname] AS Name, dbo_tblHolEntitle.intYear AS [Calender Year], dbo_tblHolEntitle.sngEntitle AS [Entitled To], Sum(IIf([dteDate]<Now(),IIf([intAbsenceType]<=2,0.5,IIf([intAbsenceType]=3,1,0)),0)) AS Taken, Sum(IIf([dteDate]<Now(),0,1)) AS Planned, [Entitled To]-[Taken]-[Planned] AS [Left]
FROM (dbo_tblEmployee INNER JOIN dbo_tblHolEntitle ON dbo_tblEmployee.intEmpID = dbo_tblHolEntitle.intEmpID) INNER JOIN dbo_tblDay ON dbo_tblEmployee.intEmpID = dbo_tblDay.intEmpID
WHERE (((dbo_tblDay.dteDate)>=DateValue("01/01/" & [intYear])) AND ((dbo_tblDay.intAbsenceCode)=8023))
GROUP BY dbo_tblEmployee.intEmpID, [strForename] & " " & [strSurname], dbo_tblHolEntitle.intYear, dbo_tblHolEntitle.sngEntitle
HAVING ((([strForename] & " " & [strSurname]) Like [?]) AND ((dbo_tblHolEntitle.intYear)=2008));
This above Access code works fine. Is it ok if someone can take a look at this for me and see whether they can convert it into SQL
I hope all this makes sense.
Jonah
July 29, 2008 at 4:20 pm
You say SQL Server.... which revision SQL 2000 or 2005 or 2008?
Do you have a copy of the database which is not on a production server, do you have either Query Analyzer (SQL 2000) or SQL Server Management Studio (SSMS for SQL 2005) available?
If so then cut and paste your SQL statements into Query Analyzer or SSMS and execute them, or better yet ask your Database Administrator to do so for you.
August 27, 2008 at 10:29 pm
Sum(IIf([dteDate]<Now(),IIf([intAbsenceType]<=2,0.5,IIf([intAbsenceType]=3,1,0)),0)) AS Taken, Sum(IIf([dteDate]<Now(),0,1)) AS Planned
Use a Case Statement to resolve this complex iif statement. The case will take a condition when x then 5.
Jason
August 29, 2008 at 6:44 am
Just another way to approach this, but Microsoft makes a FANTASTIC migration tool for migrating Access databases to SQL Server 2005 or 2008. I've used the 2005 version, and it's pretty slick. You usually need to override the basic settings that the wizard portion would otherwise use, but on the whole, it's a pretty slick deal.
The concept is that this allows you to maintain your "application" portion within MS Access, and just move the tables up to SQL Server, with all their rules and indexes and relationships intact. Once the migration is done (which is really just a copy process), you can then "LINK" the existing table names to the SQL Server, and the net result is your application runs just as it has, except that the tables are now on the SQL server instead of within the Access database, which provides greater protection for your data. Your queries need not change in the slightest. Here's a link to the MS site:
Keep in mind that this doesn't free you from maintaining the Access database, including regular "Compact and Repair" operations, but if you make regular backups of the .mdb file, you can quite easily recover most of your code from a backup, as that part is unlikely to change much over time.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply