Trying to capture data from two sources

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Trying to capture data from two sources

Author

Topic

leon1davies
Starting Member

2 Posts

Posted - 2011-08-09 : 08:11:35

I have an employee table which captures leaver data, mainly in the field called "date_left". Sometimes that field doesn’t get updated till after the employee has left.
I also have an audit table which makes a record based on changes in the database such as when the date_left field gets populated (regardless of the date they left).

I am trying to build a report which will run on the 1st of every month and will tell me anyone who left the previous month, or anyone who was made a leaver the previous month when their date left is before the previous month. So for example if the report comes out on the 1st July, I want anyone who left in June or anyone who was made a leaver in June but left in May or April etc.

I have managed to build two separate reports, one for the leavers last month and one for the "late" leavers but I cant figure out how to combine the two so I only have one report.

The audit table uses two main fields, "transact_date" (date made a leaver) and "fieldname" (this picks records when the date_left field is populated.

At the moment I am using the below SQL in a WHERE statement to get the required leavers in each report

Leavers Report
e.date_left >= Dateadd (month, Datediff (month, 0, getdate()) - 1, 0)
AND e.date_left < Dateadd (month, Datediff (month, 0, getdate()), 0)

AND e.date_left >= Dateadd (month, Datediff (month, 0, getdate()) - 1, 0)
AND e.date_left < Dateadd (month, Datediff (month, 0, getdate()), 0

Late Leavers Report
cmg.fieldname = 'date_left'
AND cmg.transact_date >= Dateadd (month, Datediff (month, 0, getdate()) - 1, 0)
AND cmg.transact_date < Dateadd (month, Datediff (month, 0, getdate()), 0)
AND e.date_left < Dateadd (month, Datediff (month, 0, getdate()), -2, 0)

Any help will be really appriciated[/font=Arial]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 10:26:22

you can combine both statements using OR so that it will bring both.


...
(
e.date_left >= Dateadd (month, Datediff (month, 0, getdate()) - 1, 0)
AND e.date_left < Dateadd (month, Datediff (month, 0, getdate()), 0)

AND e.date_left >= Dateadd (month, Datediff (month, 0, getdate()) - 1, 0)
AND e.date_left < Dateadd (month, Datediff (month, 0, getdate()), 0
)
OR
(cmg.fieldname = 'date_left'
AND cmg.transact_date >= Dateadd (month, Datediff (month, 0, getdate()) - 1, 0)
AND cmg.transact_date < Dateadd (month, Datediff (month, 0, getdate()), 0)
AND e.date_left < Dateadd (month, Datediff (month, 0, getdate()), -2, 0)
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

leon1davies
Starting Member

2 Posts

Posted - 2011-08-11 : 04:20:52

I thought that would be the case but I just get time out errors when I run that.
Just a quick question....in the first half of the statement you've put the e.date_left sections twice, is that intentional?

Thank you for your help by the way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:19:18

i just copy pasted your posted code as i dont know what all filters you want to apply

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources