Date Calculations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Calculations

Author

Topic

sqlspider
Starting Member

16 Posts

Posted - 2005-08-24 : 21:04:05

Vikram writes "Hi,

I need some help in date manipulations on SQL server data.

I am required to calculate to see

1. If anyone is absent one day within a 30 day period. if they are then upon their next absence in the same rolling 30 day period they need to be move to step I of disciplinary stage.

this information has to be checked for hundreds of employees.

I tried many ways but am not getting the right results. Moreover upon defaulting they will have to be moved to differnt steps.

Any suggestions will be greatly appreciated.

thanks in advance

Vik"

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-24 : 21:51:34

Post your table structures, some sample data and any attempt at a query that you may have...

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-08-24 : 21:59:41

Vikram

I won't bother commenting on how draconian your work place is! Glad I don't work there.

Many ways you could do this depending on what you want to achieve. when you say "rolling 30 day period" do you mean that you want to list all employees where the difference between absence dates is < 30, or do you mean per calendar month?

Calendar month

select month, employee 
from absences 
group by month, employee
having count(absences) > 1

less than 30 difference

select a1.employee 
from absences a1 left join absences a2 
on a1.employee = a2.employee and a1.absence < a2.absence
where datediff('d',a1.absence, a2.absence) < 30

* don't have BOL in front of me, so you'll need to check the Datediff function

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

sqlspider
Starting Member

16 Posts

Posted - 2005-08-24 : 22:19:03

I am using asp.net front end to manipulate the data. This is the db structure info and below is my approach.

===============================
Database Structure
===================================

I have a table named sheet2 which contains a weekly report of employees who are absent in the current week. Sheet2 Structure is basic with emp_name,emp_id, and eventdtm(current date of abs).

I have an other table named wlist which contains the prev days of absence and steplevel columns for employees.

The app now should update the wlist info with current date of abs and compare it to previous absences to either upgrade the disciplinary stagelevel or retain it following the restrictions as mentioned in my first post.

The tables have no PK_ assigned.

==============================
My approach
==============================

In the page load I am filling a datagrid with the following command

*****************************
Dim cmdtext As String = "SELECT DISTINCT SHEET2.Employee, wlist.Employee AS PREVHISTORY, SHEET2.Eventdtm as 'CURRENT_ABSENCE' , wlist.[Date] as 'LAST_ABSENCE', SHEET2.Shift, wlist.Step,wlist.Type,wlist.sco,wlist.b1,wlist.b2,wlist.b3,wlist.b4,wlist.occ,wlist.assigned,wlist.preassigned FROM SHEET2 INNER JOIN wlist ON SHEET2.[Clock #] = wlist.[Clock #]WHERE (SHEET2.EXCUSED_STATUS='NOT EXCUSED') AND (wlist.[Date] BETWEEN '01/01/2005' AND '12/31/2005') AND (SHEET2.Eventdtm BETWEEN '01/01/2005' AND '12/31/2005')ORDER BY SHEET2.Employee"
************************************************************

Now I have a datagrid with current and previous dates of absence for each employee. All I am doing now is iterating through the datagrid to extract the current and last absences and running the following query as a function

****************************************************************
For Each dgi In DataGrid1.Items
con1.Open()
Dim b1, b2, b3, b4, sco As Integer
Dim emp As String
Dim dt, occ, diff As DateTime
emp = dgi.Cells(0).Text
dt = dgi.Cells(2).Text
Dim currabs = dgi.Cells(2).Text
Dim lastabs = dgi.Cells(3).Text sco = CInt(dgi.Cells(7).Text)
b1 = dgi.Cells(8).Text
b2 = dgi.Cells(9).Text
b3 = dgi.Cells(10).Text
b4 = dgi.Cells(11).Text
occ = dgi.Cells(12).Text
Dim stepcount As Integer = dgi.Cells(5).Text
Dim assigned As Integer
assigned = dgi.Cells(13).Text
Dim cmdtext1 = "getmonthdiffs "
------>## Is a store proc which is datediff (month,currabs,lastabs)--->##
cmdtext1 &= "'" & currabs & "'"
cmdtext1 &= ","
cmdtext1 &= "'" & lastabs & " '" Dim cmd1 As New SqlCommand(cmdtext1, con1)
Dim r1 As System.Data.SqlClient.SqlDataReader
r1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection)
r1.Read()

Response.Write(r1("months"))
If r1("months") = "0" Then
setsco1(emp, currabs, lastabs) End If
================================================================
If the diff in months is<1 then I will be updating the disciplinary step to curr_step +1
=================================================================
If count(emp) >= 2 Then
dgi.Cells(0).BackColor = System.Drawing.Color.Red.Red
incrstep(emp, stepcount)
End If

con1.Close()
Next

=============================================================
This method is working but the results are slightly varying and the performance is really slow. Since I am iterating the datagrid, some time the connection pooling is being timed out.

So I am thinking hard for a different effective solution. Any help and suggestions are welcome.

Thanks you so much!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources