COMBINE DATA IN SINGLE ROW FROM SINGLE TABLE

[heri.carandang]

Tom,

How can i Join this 2 table ? I am getting an error my LEFT OUTER JOIN
DOESNT WORK thanks

SELECT EmployeeNo, Datehired, SectionCode,
Department,Lastname,Firstname
FROM (dbo.Employees H1
GROUP By EmployeeNo, Datehired, SectionCode,
Department,Lastname,Firstname)
dbo.Employees LEFT OUTER JOIN ON dbo.empBenefits.EmployeeNo =
dbo.Employees.EmployeeNo

SELECT EmployeeNo, StartDate,Amount AS OLDBENEFITS,
EndDate,BenefitCode,
(SELECT MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND
startdate >= '2000-01-01' AND startdate <= '2007-03-30')
AS NEWBENEFITS
FROM dbo.empBenefits T
WHERE(StartDate =(SELECT TOP 1 ([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND
Benefitcode = 'HON' AND startdate >= '2000-01-01' AND startdate <=
'2007-03-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount

 

[Tom Ellison]

Dear Heri:

In your query immediately after FROM, you have something in parentheses. It
isn't intelligible to me. If it is a subquery, it must be a complete,
working query, probably starting with SELECT and at least with its own FROM
clause. I would not expect it to work, but I cannot tell what it is
supposed to do.

I know you are in trouble and have very little time. I have tried to give
you some options of ways in which I really might be able to help. I'll be
available on and off for the next 3 hours or so and I'll check to see if you
have provided something from which I can work. After that, it may be Friday
before I'm available again.

I expect I could solve this in an hour or two. I need the SQL Server backup
about which we spoke in order to begin. Pare it down to a size we can
handle as I tried to explain before. Perhaps you should remove all
employees below a certain number, for example, to get it down to 2
megabytes.

Tom Ellison

 

[heri.carandang]

Hi Tom,

This is the best thing that I can do you can run this script and we can
have an identical table in this way you can clearly picture out and
maybe we can work this out together.

There are 3 scripts empsalaries,empbenefits and employees

Thanks a lot.


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[empSalaries]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[empSalaries]
GO

CREATE TABLE [dbo].[empSalaries] (
[EmployeeNo] [int] NOT NULL ,
[SalaryID] [tinyint] NOT NULL ,
[BasicSalary] [decimal](18, 2) NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL ,
[Status] [tinyint] NULL ,
[PaymentType] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PaymentRate] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PaymentMode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[empBenefits]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[empBenefits]
GO



CREATE TABLE [dbo].[empBenefits] (
[EmployeeNo] [int] NOT NULL ,
[BenefitCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[Amount] [decimal](10, 2) NULL ,
[Status] [tinyint] NOT NULL ,
[Remarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ShiftPlans_Employees]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ShiftPlans] DROP CONSTRAINT FK_ShiftPlans_Employees
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Employees]
GO

CREATE TABLE [dbo].[Employees] (
[EmployeeNo] [int] NOT NULL ,
[LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[MiddleName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateHired] [smalldatetime] NOT NULL ,
) ON [PRIMARY]
GO

 

[Tom Ellison]

I have this, and I've run it. More later. I'm busy seeing what I can do
with it.

Tom

 

[Tom Ellison]

Dear Heri:

Your script works to create 3 empty tables. How about some data, something
challenging? If I create the data myself, it will take longer than just
sending some that you have. Make it challenging. Maybe choose a few
employees and all their related data. Be sure to include some with twists.

On my side, I start trying some kind of query. I have to review what we
already have posted first. Can you do this?

Tom Ellison

 

[Tom Ellison]

If I write this query for SQL Server, you may have to make sure it is
Pass-thru. Is that acceptable?

I can't think why it wouldn't be. But you'll have to know how to do it.

It could also just be a View in the database.

Tom Ellison

 

[Tom Ellison]

Dear Heri:

I have this basic query working for SQL Server (not for Jet, please). It
assumes there are not more than two empSalary values:

SELECT SA.EmployeeNo, SA.StartDate, SA.BasicSalary, SB.StartDate,
SB.BasicSalary
FROM empSalaries SA, empSalaries SB
WHERE SA.EmployeeNo = SB.EmployeeNo
AND SA.StartDate = (SELECT MIN(StartDate) FROM empSalaries S1 WHERE
S1.EmployeeNo = SA.EmployeeNo)
AND SB.StartDate = (SELECT MAX(StartDate) FROM empSalaries S1 WHERE
S1.EmployeeNo = SA.EmployeeNo)

Now, what other information do you want to see? Also, if an employee has
more than two rows, what do you want the query to show?

Tom Ellison

Hi Tom,

This is the best thing that I can do you can run this script and we can
have an identical table in this way you can clearly picture out and
maybe we can work this out together.

There are 3 scripts empsalaries,empbenefits and employees

Thanks a lot.


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[empSalaries]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[empSalaries]
GO

CREATE TABLE [dbo].[empSalaries] (
[EmployeeNo] [int] NOT NULL ,
[SalaryID] [tinyint] NOT NULL ,
[BasicSalary] [decimal](18, 2) NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL ,
[Status] [tinyint] NULL ,
[PaymentType] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PaymentRate] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PaymentMode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[empBenefits]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[empBenefits]
GO



CREATE TABLE [dbo].[empBenefits] (
[EmployeeNo] [int] NOT NULL ,
[BenefitCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[Amount] [decimal](10, 2) NULL ,
[Status] [tinyint] NOT NULL ,
[Remarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ShiftPlans_Employees]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ShiftPlans] DROP CONSTRAINT FK_ShiftPlans_Employees
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Employees]
GO

CREATE TABLE [dbo].[Employees] (
[EmployeeNo] [int] NOT NULL ,
[LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[MiddleName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateHired] [smalldatetime] NOT NULL ,
) ON [PRIMARY]
GO

 

[Tom Ellison]

Dear Heri:

I didn't hear any response. I made a guess at a way of looking at the
salary changes consecutively:

SELECT SA.EmployeeNo, SA.StartDate, SA.BasicSalary, SB.StartDate,
SB.BasicSalary
FROM empSalaries SA, empSalaries SB
WHERE SA.EmployeeNo = SB.EmployeeNo
AND SB.StartDate = (SELECT MIN(StartDate) FROM empSalaries S1
WHERE S1.EmployeeNo = SA.EmployeeNo AND S1.StartDate > SA.StartDate)

That's about it for me today. I may be able to check back before Friday
here.

Tom Ellison