Merge data from many to one line in recordset

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 2005 Forums

Transact-SQL (2005)

Merge data from many to one line in recordset

Author

Topic

iand109
Starting Member

14 Posts

Posted - 2009-12-02 : 11:32:34

Hi,
I'm using SQL 2005 DB with ASP VB.
I'm creating a 6-month forecast report which tells us how many people we have working for us at any given start date.

I have a table which is employment records for everyone in the company. The asp page looks at each record to see whether a person was working for us during each month within the selected date range.

The problem I have is that one person can have one employment record with a start date and end date, as well as another employment record with a start date that is adjacent to the previous end date (i.e. maybe they got a pay rise - that's a new employment record).

Currently my report shows these same people on 2 lines (1 for each record), where what I really want is to show that person as having continuous employment on the 1 line. Does anyone please have any idea as to how I could do this?
Many thanks

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-02 : 11:55:33

So...

Emp.; Start Date; End Date
1 1/1/09 1/31/09
1 2/1/09 12/31/09

You want to see...

1 1/1/09 12/31/09

?

Well if so...

Select
EmpNo,
minStartDate,
maxEndDate
FROM
(
Select
EmpNo,
Min(StartDate) as minStartDate,
Max(EndDate) as maxEndDate
From EmpTable
Group by EmpNo
) a
ORDER BY 1

iand109
Starting Member

14 Posts

Posted - 2009-12-03 : 11:47:40

Hi,
many thanks for your reply. Yes, that is what I would like to see, but I only want it to group the records if the first end date is adjacent to the following start date, thereby it effectively being a continuous employment. There may be instances where someone has left and then come back later, or taken a period of unpaid leave.
I tried this:

SELECT AppNo, minStartDate, maxEndDate
FROM (SELECT AppNo, MIN(EmpStart) AS minStartDate, MAX(EmpEnd) AS maxEndDate
FROM dbo.Employment
WHERE (EmpEnd = DATEADD(dd, - 1, EmpStart))
GROUP BY AppNo)
ORDER BY AppNo

But it came up with no results. Can you think of how I could achieve this?
Many thanks

iand109
Starting Member

14 Posts

Posted - 2009-12-03 : 12:40:31

Another approach, which might work better for the purposes of my 6-month staff forecast report, might be to collate employment start dates and end dates into one line grouped by individual, like EmpStart1, EmpEnd1, EmpStart2, EmpEnd2 etc. Then when i run the report it could see whether that person was employed within that month and check the column for that month dependent on various conditions..do you know how I could do that - scanning each employment record and setting it as EmpStart1 etc?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources