COMBINE DATA IN SINGLE ROW FROM SINGLE TABLE

[heri.carandang]

HELLO,

How can i combine my data in single row ? All data are in a single
table sorted as employeeno, date


DATABASE
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000


Should Appear in the Database

EmpNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

How can I make it in Stored Proc or Views or Any SQL Command ?

THANKS IN ADVANCE

 

[Tom Ellison]

Dear Heri:

First, I will assume that the columns Employee No and Date are unique taken
together and that there are never more than 2 rows for an employee. For
other circumstances, I'll need sample data that illustrates this.

Here's an initial attempt at the query:

SELECT employeeno, MIN([Date]) AS Date1,
SALARY AS OLDSALARY, MAX([Date]) AS Date2,
(SELECT MAX(SALARY)
FROM DATABASE T1
WHERE [Date] =
(SELECT MAX([Date])
FROM DATABASE T2
WHERE T2.employeeno = T1.employeeno)
AND T1.employeeno = T.employeeno
AND [Date] >
(SELECT MAX([Date])
FROM DATABASE T2
WHERE T2.employeeno = T1.employeeno))
AS NEWSALARY
FROM DATABASE T
WHERE [Date] =
(SELECT MIN([Date])
FROM DATABASE T1
WHERE T1.employeeno = T.employeeno)
GROUP BY employeeno

Please carefully check the column names and table name I have used to refer
to your existing situation.

As you may be able to see, the hard work is getting the salary value from
the row that has the most recent date for each employee.

I put MAX on that salary in the hope that there will never be two rows for
that employee and date, but this assures it won't error by returning
multiple rows. No error, but whether this would be the desired value in
that case is debatable.

Tom Ellison

 

[heri.carandang]

Tom,

Based on your query data comes from multiple table but all my data
comes from a single table. My main problem is that how can I combine
same employeeno in a SINGLE ROW with a list of 1000 Employees sorted by
EmployeeNo and Date.

Please take into consideration that there will be a maximum of 2
records with the same EmployeeNo and sometimes there will be a single
record on each employee and data comes from a same table.

The reason behind this is I need to get the OLD SALARY and NEW SALARY
of an Employee based on Date and Display It in a single row.

My Table Fields are EmployeeNo, Date, Salary

EmployeeNo Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000

EmployeeNo 1 & 4 Should Display as

EmployeeNo OldSalary New Salary
1 500 1000 (SingleRow)
2 800
3 900
4 1000 8000 (Single Row)


I am really happy know and to see people helping each other around the
globe with different race and nationality, it is really wonderful and
awesome.

I really appreciate your reply and your kindness on helping me out. I
really need your help in the SELECT STATEMENT.

Thank you so much.

 

[Tom Ellison]

Dear Heri:

Sorry, but my query is on a single table, named DATABASE. I got that from
your earlier post.

It does deal with multiple instance of that table, but it's still just one
table.

The name DATABASE was the best I could figure out for what you have named
this table. If it is something else, then please change it throughout my
query and then, please, try it. It took a bit of time to figure this out,
and I'd appreciate you at least trying it, and report back what you find.

Other than the table name, the column names are important. I used
employeeno, Date, and SALARY, which I got from your original post. If the
spelling is right, you can leave this alone. You don't need to worry about
capilalization.

So, please try it. It's not designed to make your computer catch fire, or
anything like that. It won't erase or change your data. A SELECT query by
itself cannot do that! Not without and APPEND or INSERT for example.
There's not risk to you. Maybe there will be some benefit.

If you get an error message, please report that to me exactly. I'll try to
fix it up.

Tom Ellison

 

[heri.carandang]

Hi Tom,

Got your query right thank you so much I included it in my Select
Statement. However, I have this problem with my query. This query
gets the MIN and MAX date and get the OLDSALARY and NEWSALARY.
However, how can I check if there is a record inbetween my specified
date range ? Like for Example my date range is startdate >=
'2005-06-01' AND startdate <= '2006-06-30' and there is a record with
stardate of '2006-01-01' with the same employeeno it is in between my
specified date my old salary should be the amount from 2006-01-01 and
not the amount from 2005-06-01 becuase it is the employee salary before
the MAX date my NEWSALARY will be having no problem because I can get
the MAX stardate but my OLDSALARY is wrong because there is still a
record in between which should be my OLDSALARY value.

This query Get the Min and Max of Salary inbetween dates I want to get
the OldSalary Between Max and Min in case there is inbetween Record
how can I check it in addition to this query ? Date format is
smalldatetime

As you can see I inluded your query and it helps me a lot i can ow
display my record in a single row.

Hope you can help me again in my problem ... Kindly modify my query
below to get my desired output.


SELECT TOP 100 PERCENT EmployeeNo, StartDate AS StartDate, Amount AS
oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2005-06-01' AND
startdate <= '2006-06-30') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND
startdate >= '2005-06-01' AND startdate <= '2006-06-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate


Again Thank you

 

[heri.carandang]

Hi Tom,

Got your query right thank you so much I included it in my Select
Statement. However, I have this problem with my query. This query
gets the MIN and MAX date and get the OLDSALARY and NEWSALARY.
However, how can I check if there is a record inbetween my specified
date range ? Like for Example my date range is startdate >=
'2005-06-01' AND startdate <= '2006-06-30' and there is a record with
stardate of '2006-01-01' with the same employeeno it is in between my
specified date my old salary should be the amount from 2006-01-01 and
not the amount from 2005-06-01 becuase it is the employee salary before
the MAX date my NEWSALARY will be having no problem because I can get
the MAX stardate but my OLDSALARY is wrong because there is still a
record in between which should be my OLDSALARY value.

This query Get the Min and Max of Salary inbetween dates I want to get
the OldSalary Between Max and Min in case there is inbetween Record
how can I check it in addition to this query ? Date format is
smalldatetime

As you can see I inluded your query and it helps me a lot i can ow
display my record in a single row.

Hope you can help me again in my problem ... Kindly modify my query
below to get my desired output.


SELECT TOP 100 PERCENT EmployeeNo, StartDate AS StartDate, Amount AS
oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2005-06-01' AND
startdate <= '2006-06-30') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND
startdate >= '2005-06-01' AND startdate <= '2006-06-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate


Again Thank you

 

[heri.carandang]

Hi Tom,

Got your query right thank you so much I included it in my Select
Statement. However, I have this problem with my query. This query
gets the MIN and MAX date and get the OLDSALARY and NEWSALARY.
However, how can I check if there is a record inbetween my specified
date range ? Like for Example my date range is startdate >=
'2005-06-01' AND startdate <= '2006-06-30' and there is a record with
stardate of '2006-01-01' with the same employeeno it is in between my
specified date my old salary should be the amount from 2006-01-01 and
not the amount from 2005-06-01 becuase it is the employee salary before
the MAX date my NEWSALARY will be having no problem because I can get
the MAX stardate but my OLDSALARY is wrong because there is still a
record in between which should be my OLDSALARY value.

This query Get the Min and Max of Salary inbetween dates I want to get
the OldSalary Between Max and Min in case there is inbetween Record
how can I check it in addition to this query ? Date format is
smalldatetime

As you can see I inluded your query and it helps me a lot i can ow
display my record in a single row.

Hope you can help me again in my problem ... Kindly modify my query
below to get my desired output.


SELECT TOP 100 PERCENT EmployeeNo, StartDate AS StartDate, Amount AS
oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2005-06-01' AND
startdate <= '2006-06-30') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND
startdate >= '2005-06-01' AND startdate <= '2006-06-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate


Again Thank you

 

[Tom Ellison]

Dear Heri:

I reproduce your query here, reformated as I prefer to see it for study:

SELECT TOP 100 PERCENT EmployeeNo, StartDate AS StartDate,
Amount AS oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno
AND startdate >= '2005-06-01'
AND startdate <= '2006-06-30') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN (SELECT TOP 1([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno
AND Benefitcode <> 'HON'
AND startdate >= '2005-06-01'
AND startdate <= '2006-06-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate

You have made considerable changes to what I suggested, some of which make
little sense to me. My comments about this follow:

1. You have used TOP 100 PERCENT lin line 1 above. This is fairly
meaningless in this context. For someone studying this code, it is a
distraction to no purpose. Remember, it may be you coming back to read and
maintain this code in a year or two. Why have such distractions?

2. You Alias StartDate as StartDate. This does nothing. Same for EndDate.
Any excess code that does nothing just slows the process of reading and
digesting code. I recommend you avoid that.

3. The TOP 1 of an aggregate MAX is meaningless. MAX can return only one
value.

4. You have dropped the inner subquery from the subquery returning
newsalary. This makes a presumption, that salaries always increase. If
anyone ever takes a pay cut, this will malfunction!

5. You are limiting the results to the period June 1, 2005 through June 30,
2006 (a 13 month period). At least, it looks like you wish to do this. May
I recommend this? Use date literals. The date literals would be
represented as #06/01/2005# and #06/30/2006#. You must use MM/DD/YYYY for
date literals. However, what will happen when you need to use this query a
year from now? Wouldn't you want the dates to advance with time? Wouldn't
it be better to do this under the user's control, or even automatically? Or
do you intend to have someone change the query every year? And would it
ever be the case that someone would need to switch back and forth between
"this year" and "last year" in viewing these results?

6. In line 9 you use IN instead of =. The query returns only one row of
one column. IN and = are equivalent in this situation, but = is a simpler
read. IN is something I would reserve for situations where there could be
more than one row, just for the purpose of readability.

7. In line 12 you filter Benefitcode <> "HON". I expect his filter should
be applied to the earlier subquery as well if you want to eliminate rows
from consideration.

8. You have many columns in the GROUP BY. In part, this is my fault, as I
omitted something.

In addition to the above, there are challenges getting the nested subqueries
working in Access. So, I'm making this suggestion. Send me a copy of your
database and I'll work out these things. First, make a copy of the database
and rename the extension from MDB to XXX. Zip up the file. Then attach it
to an email to me (e-mail address removed)

I also want to know what results you expect to see, especially for an
employee who has 3 rows in the table.

Tom Ellison

Hi Tom,

Got your query right thank you so much I included it in my Select
Statement. However, I have this problem with my query. This query
gets the MIN and MAX date and get the OLDSALARY and NEWSALARY.
However, how can I check if there is a record inbetween my specified
date range ? Like for Example my date range is startdate >=
'2005-06-01' AND startdate <= '2006-06-30' and there is a record with
stardate of '2006-01-01' with the same employeeno it is in between my
specified date my old salary should be the amount from 2006-01-01 and
not the amount from 2005-06-01 becuase it is the employee salary before
the MAX date my NEWSALARY will be having no problem because I can get
the MAX stardate but my OLDSALARY is wrong because there is still a
record in between which should be my OLDSALARY value.

This query Get the Min and Max of Salary inbetween dates I want to get
the OldSalary Between Max and Min in case there is inbetween Record
how can I check it in addition to this query ? Date format is
smalldatetime

As you can see I inluded your query and it helps me a lot i can ow
display my record in a single row.

Hope you can help me again in my problem ... Kindly modify my query
below to get my desired output.


SELECT TOP 100 PERCENT EmployeeNo, StartDate AS StartDate, Amount AS
oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2005-06-01' AND
startdate <= '2006-06-30') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND
startdate >= '2005-06-01' AND startdate <= '2006-06-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate


Again Thank you

 

[heri.carandang]

Hi Tom,

Thank you for your kindness on giving me your email. You just dont
know how much you helped me in my project because we are under pressure
by our boss. Tom, favor please I am not really good at SQL query I
can do simple queries but not a complex one. I have this problem on
how to get a data from EMPLOYEE TABLE LINK to @ VIEW TABLES
(V_GETOLDNEWBASIC) AND (V_GETOLDNEWPOSITION) .

1. 1st I need to get all employees from Employee Table with Startdate
from between '01/01/2005 to 12/31/2005'
2. I need to link the employee table to V_GETOLDBASIC by employeeID
and I need to get all OLDBASIC and NEWBASIC PAY with startdate
between '01/01/2005 to 12/31/2005'. Dont worry the Startdate
OLDBASICPAY and NEWBASICPAY is in one row (I just need to filter the
max startdate to get the latest data)
3. Is the same as # 2 only the table is changed V_GETOLDNEWPOSITION
(Here i need to get the OLDPOSITION & NEWPOSITION from the date range
speicified in # 1 ) Link by Employeeno to Employee Table.

Actually I have 5 tables on where I should get the data all are linked
to EMployee Table. The Link should be 1 is to 1 and no relation with
other tables but each table has a relation to employee table (By
EMPLOYEENO and STARTDATE)

All i need to get is all the data from each table based on the date
range specified from EMPLOYEE TABLE STARTDATE in a single row per
employee no.

Employee OLD BASIC NEW BASIC OLD POSITION NEWPOSITION

1 1500 1000 Tech Support Programmer
2 4000 7000
3 3000 8000
4 Call Agent Team
Supervisor

I believe this is a SELECT STATEMENT WITHIN A SELECT and WITHIN A
SELECT .... depends on how many table I need to use. But for my case I
need to use 5 tables. But for my example i only use 3.


Attached herewith is my table structure in Views

Tom, I Got your query right. I believe there are some loop holes on
the one I made and after running the query you gave me I derived to the
correct output.

Again, Thank you so much

Awaiting for your reply.

Tom, I sent you and email on the addy you gave me.

Heri

 

[Tom Ellison]

Dear Heri:

Nothing came through in my email. Please try again.

Tom Ellison

 

[heri.carandang]

Tom,

I cant get through your email its bouncing back. Anyway my message
above is the same thing I sent to you through email, only the
attachment is missing which is the field structure. The field
structure are

ERROR MESSAGE ON THE EMAIL I SENT YOU
Hi. This is the qmail-send program at imain.jcdoyle.com.
I'm afraid I wasn't able to deliver your message to the following
addresses.
This is a permanent error; I've given up. Sorry it didn't work out.

<[email protected]>:


EMPLOYEE TABLE

EmployeeNo = Int
DATEHIRED = Smalltimedate (I NEED TO GET ALL THE EMPOYEES DATEHIRED
WITHIN THE DATE RANGE (FROMDATE >= DATEHIRED and TODATE <=DATEHIRED)

V_GETOLDNEWBASIC Table
EmployeeNo = Int
Startdate = smalldatetime
Oldbasic = Int
NewBasic =Int
OLDBASICPAY and NEWBASICPAY is in one row (I NEED TO GET ALL THE
EMPOYEES STARDATE WITHIN THE DATE RANGE (FROMDATE >= STARDATE and
TODATE <=STARDATE) LINK to EMPLOYEE TABLE BY EMPLOYEENO

V_GETOLDNEWPOSITION
EmployeeNo = Int
Startdate = smalltimedate
OLDPosition = Varchar
NewPosition =Varchar (I NEED TO GET ALL THE EMPOYEES STARDATE WITHIN
THE DATE RANGE (FROMDATE >= STARDATE and TODATE <=STARDATE) LINK to
EMPLOYEE TABLE BY EMPLOYEENO

V_HONBENEFITS TABLE
EmployeeNo = Int
Startdate = smalldatetime
OLDAmount = Int
(I NEED TO GET ALL THE EMPOYEES STARDATE WITHIN THE DATE RANGE
(FROMDATE >= STARDATE and TODATE <=STARDATE) LINK to EMPLOYEE TABLE BY
EMPLOYEENO

All Tables should be link to EMPLOYEE Table (EmployeeNo) and should
get the value depending on the date specified DATE>=Stardate and
DATE<=Startdate. Please take note that there are no link or
relationship between
(V_GETOLDNEWPOSITION,V_HONBENEFITS TABLE,V_GETOLDNEWBASIC Table).

1. I should get the all the records from EMPLOYEE TABLE where
DATEHIRED>=Stardate and DATEHIRED<=Stardate
2. I should link EMployee table to V_GETOLDNEWPOSITION by EMployeeNo
and should get all the data from V_GETOLDNEWPOSITION where
FROMDATE>=Stardate and TODATE<=Stardate THEN GET THE MAX RECORD
3. I should link EMployee table to V_HONBENEFITS TABLE by EMployeeNo
and should get all the data from V_HONBENEFITS TABLE where
FROMDATE>=Stardate and TODATE<=Stardate THEN GET THE MAX RECORD
4. I should link EMployee table to V_GETOLDNEWBASIC Table by
EMployeeNo and should get all the data from V_GETOLDNEWBASIC where
FROMDATE>=Stardate and TODATE<=Stardate THEN GET THE MAX RECORD

I will just follow your query on the remaining tables.

OUTPUT

Employee OLD BASIC NEW BASIC OLDPOS NEWPOS OLDBENEFITS NEWBENEFITS


1 1500 1000 Tech Support Programmer
2 4000 7000 500 600
3 3000 8000
4 Call Agent TeamLDR 1000 2000






Tom, I really, really need your help on this .... Again my most
gratitude to your effort. , Thanks a lot.

 

[heri.carandang]

Hi Tom,

I am still at work I will be going home and will return after a few
hours I have been on duty for the last 10 hrs really toxic ... Hope you
can assist me on the query. Please , please .... Thanks ...

I think its SELECT within SELECT within SELECT im not quite sure how
many .... hehehehe you're the expert !!!!

Ciao.

 

[Tom Ellison]

Dear Heri:

The email address is (e-mail address removed)

I was asking you to rename the database so the file extension is XXX, zip
it, and send it along. I do not want to try to reproduce all you have
myself. Not only would this take some time, but any small differences may
invalidate my solution. Can you do this? Will you do this?

I need the relevant tables with reasonable data to perform sufficient
testing, so that what I produce will work for you.

Tom Ellison

 

[heri.carandang]

Hi Tom,

We are using SQL 2005 how do you want me to send the table structures ?
Sending the database file from SQL is too big even if I zip it.

Please advise.

Thanks,

 

[Tom Ellison]

Dear Heri:

I did not know this before. That is indeed a different and larger
challenge.

I would suggest you make a backup and then restore it as a different
database. Remove all but the relevant tables. Backup this and zip it. How
large is that?

Tom Ellison

 

[heri.carandang]

Tom,

I just tried it but our email has a limit of 2MB and the ZIP file is
4.2 MB.

Anyway, thanks for a lot of help ... I will just try to figure it out
on how I can make this query. Its a bit complex on joining 3-5 tables
together and having nested Select Statements with INNER OR OUTER JOIN.

Again, I really appreciate all your help. I know I cant return any
favor from you cause you are much far better than me but maybe I can
also help other people in some other way like what you did to me.

God Bless and Take Care

With much appreciation,

Heri

 

[Tom Ellison]

Dear Heri:

Remove about 60% of the rows from your foreign key tables. Try to do this
fairly randomly so I still get a good sample. If cascade deletes are set
up, the related rows from other tables will be removed as well. I would
still be able to work with this, and it should make it through your email.

I hope you won't give up on this yet. I have SQL 2000 and 2005 set up here
and work with this constantly.

If you wish, we can have you send a CD or DVD. Contact me by email to make
arrangements.

Tom Ellison

 

[heri.carandang]

Tom,

It would take time if I will send to you the CD and I am really running
out of TIme as of now this is the best source i can give. I copied all
my Select Statements and I just want to combine this one in one select
Statement is it possible ? I will be sending you a sample select
statement and maybe it would help you to figure it out on what I want
to do. If you can just give me a step to follow on how I can combine
2 or 3 of my Select Statement then it would greatly help me it will
serve as my guide to do the rest of the stuff ... PLEASE I AM REALLY IN
NEED OF YOUR HELP ...

Thanks
,

I have 5 SELECT STATEMENTS to be LINK INTO EMPLOYEE TABLE


***** I have this SELECT STATMENT TO GET ALL EMPLOYEES IN EMPLOYEES
TABLE WHERE DATE HIRED IS = TO DATE RANGE


SELECT dbo.Employees.EmployeeNo, dbo.Employees.LastName,
dbo.Employees.FirstName, dbo.Departments.DeptName,
dbo.Sections.SectionName, dbo.Employees.DateHired

FROM dbo.Employees LEFT OUTER JOIN
dbo.Departments ON dbo.Employees.Department =
dbo.Departments.DeptCode LEFT OUTER JOIN
dbo.Sections ON dbo.Employees.SectionCode =
dbo.Sections.SectionCode
WHERE dbo.Employees.DateHired >= '2000-01-01' AND
dbo.Employees.DateHired <= '2007-03-30'
ORDER BY dbo.Employees.LastName
---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



*** I have this select statement to get the MIN and MAX BENEFITS Amount

in a date range.


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
ORDER BY EmployeeNo, StartDate


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



*** I have this select statement to get the MIN and MAX HONORARIUM
Amount in a date range
*** Data Comes from the same table aa above


SELECT EmployeeNo, StartDate,Amount AS OLDHON, EndDate, BenefitCode,

(SELECT MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND

startdate >= '2000-01-01' AND startdate <= '2007-03-30') AS NEWHON
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
ORDER BY EmployeeNo, StartDate


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



**** This is my select Statement to get OLD and NEW Basic Salary


SELECT EmployeeNo, StartDate, BasicSalary AS oldsalary, EndDate,
(SELECT MAX([BasicSalary])
FROM EMPSALARIES T2
WHERE T2.employeeno = T.employeeno AND

startdate >= '2001-01-01' AND startdate <= '2007-03-30') AS newsalary
FROM dbo.empSalaries T
WHERE (StartDate =
(SELECT TOP 1 ([startdate])
FROM EMPSALARIES T1
WHERE T1.employeeno = T.employeeno AND

startdate >= '2001-01-01' AND startdate <= '2007-03-30'))
GROUP BY EmployeeNo, BasicSalary, StartDate, BasicSalary, EndDate
ORDER BY EmployeeNo, StartDate


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



*** I have this select Statement to GET the OLD and NEW POSITION with
date range


SELECT T.EmployeeNo, T.StartDate AS StartDate, dbo.Positions.Title
AS NEWPOSITION, Positions_1.Title AS OLDPOSITION
FROM dbo.empPositions T LEFT OUTER JOIN
dbo.Positions Positions_1 ON T.OldPosition =
Positions_1.Code LEFT OUTER JOIN
dbo.Positions ON T.NewPosition =
dbo.Positions.Code
WHERE (T.StartDate =
(SELECT TOP 1 MAX([startdate])
FROM empPositions T1
WHERE T1.employeeno = T.employeeno AND

startdate >= '2001-01-01' AND startdate <= '2007-03-30'))
GROUP BY T.EmployeeNo, T.NewPosition, T.OldPosition, T.StartDate,
dbo.Positions.Title, Positions_1.Title
HAVING (T.NewPosition <> T.OldPosition)
ORDER BY T.EmployeeNo, T.StartDate DESC


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



IS THERE ANYWAY THAT I CAN LINK ALL THIS SELECT STATEMENTS INTO ONE
WHERE ALL ARE LEFT OUTER JOIN TO EMPLOYEE TABLE BY EMPLOYEENO ? AND THE

RESULT SHOULD BE IN A SINGLE ROW PER EMPLOYEE.


PLEASE HELP IM NOT REALLY GOOD MAKING THIS NESTED SELECT STATEMENTS
WITH LINK TO MULTIPLE TABLES ...


PLEASE HELP.


THANK YOU SO MUCH ....

 

[heri.carandang]

Hi Tom,

Can you please give me a SELECT STATEMENT that look like this based on
the above query that I have ? The Select statment that I want to
produce is somehow similar to this but the difference on our query is
that I need to get the MAX and MIN data based on Dates. Thanks

SELECT DISTINCT
TOP 100 PERCENT A3.mpilistid, A3.CALL_DATE,
A3.HOURS AS hours, A4.SALES, A5.CONTACTS, A6.COMPLETES, A9.AOS,
A11.DATE_UPLOADED
FROM (SELECT ACSFF.dbo.TBLFLASH.CALL_DATE,
ACSFF.dbo.TBLFLASH.mpilistid, SUM(ACSFF.dbo.TBLFLASH.hours) AS HOURS
FROM ACSFF.dbo.TBLFLASH
GROUP BY MPILISTID, CALL_DATE) A3 LEFT OUTER
JOIN
(SELECT ACSFF.dbo.VERIZON_FF.MPILISTID,
ACSFF.dbo.VERIZON_FF.REPORTED_DATE,
COUNT(ACSFF.dbo.VERIZON_FF.DISPOSITION)
AS TCOMPLETES
FROM ACSFF.dbo.VERIZON_FF
WHERE ACSFF.dbo.VERIZON_FF.DISPOSITION
BETWEEN '01' AND '65' AND ACSFF.dbo.VERIZON_FF.DISPOSITION <> 47
GROUP BY MPILISTID, REPORTED_DATE) A8 ON
A3.mpilistid = A8.MPILISTID AND A3.CALL_DATE = A8.REPORTED_DATE LEFT
OUTER JOIN
(SELECT ACSFF.dbo.VERIZON_FF.MPILISTID,
ACSFF.dbo.VERIZON_FF.REPORTED_DATE,
COUNT(ACSFF.dbo.VERIZON_FF.DISPOSITION)
AS SALES
FROM ACSFF.dbo.VERIZON_FF
WHERE ACSFF.dbo.VERIZON_FF.DISPOSITION
IN ('01', '02', '03')
GROUP BY MPILISTID, REPORTED_DATE) A4 ON
A3.CALL_DATE = A4.REPORTED_DATE AND A3.mpilistid = A4.MPILISTID LEFT
OUTER JOIN
(SELECT ACSFF.dbo.VERIZON_FF.MPILISTID,
ACSFF.dbo.VERIZON_FF.REPORTED_DATE,
COUNT(ACSFF.dbo.VERIZON_FF.DISPOSITION)
AS CONTACTS
FROM ACSFF.dbo.VERIZON_FF
WHERE ACSFF.dbo.VERIZON_FF.DISPOSITION
BETWEEN '01' AND '39' OR

ACSFF.dbo.VERIZON_FF.DISPOSITION = '60'
GROUP BY MPILISTID, REPORTED_DATE) A5 ON
A3.CALL_DATE = A5.REPORTED_DATE AND A3.mpilistid = A5.MPILISTID LEFT
OUTER JOIN
(SELECT ACSFF.dbo.VERIZON_FF.MPILISTID,
ACSFF.dbo.VERIZON_FF.REPORTED_DATE,
COUNT(ACSFF.dbo.VERIZON_FF.DISPOSITION)
AS COMPLETES
FROM ACSFF.dbo.VERIZON_FF
WHERE ACSFF.dbo.VERIZON_FF.DISPOSITION
BETWEEN '01' AND '65' AND ACSFF.dbo.VERIZON_FF.DISPOSITION <> 47
GROUP BY MPILISTID, REPORTED_DATE) A6 ON
A3.CALL_DATE = A6.REPORTED_DATE AND A3.mpilistid = A6.MPILISTID LEFT
OUTER JOIN
(SELECT ACSFF.dbo.VERIZON_FF.MPILISTID,
REPORTED_date, round(AVG(CONVERT(float, monthly_amount)), 2) AS 'AOS'
FROM verizon_ff
WHERE disposition IN ('01', '02',
'03')
GROUP BY ACSFF.dbo.VERIZON_FF.MPILISTID,
REPORTED_date) A9 ON A3.mpilistid = A9.MPILISTID AND
A3.CALL_DATE = A9.REPORTED_date LEFT OUTER JOIN
(SELECT ACSFF.dbo.TBLHOURS.MPILISTID,
ACSFF.dbo.TBLHOURS.CALL_DATE, SUM(HOURS) AS HOURS
FROM TBLHOURS
GROUP BY ACSFF.dbo.TBLHOURS.MPILISTID,
CALL_date) A10 ON A3.mpilistid = A10.MPILISTID AND
A3.CALL_DATE = A10.CALL_DATE LEFT OUTER JOIN
(SELECT MPILISTID, DATE_UPLOADED
FROM A_CLASS_FORMAT) A11 ON
A3.mpilistid = A11.MPILISTID
ORDER BY A11.DATE_UPLOADED, A3.CALL_DATE, A3.mpilistid

 

[Tom Ellison]

Dear Heri:

If I make an untested response, it may or may not be of any value. The
discussion we have had so far leads me to believe my efforts may be of
questionable value without applying it against your actual data.

Do you have an ftp site where you could post the database you prepared
already. I have enough bandwidth that a few megabytes isn't much of a
challenge. You could email me login and password so it would be fairly
secure.

Tom Ellison


Tom,

It would take time if I will send to you the CD and I am really running
out of TIme as of now this is the best source i can give. I copied all
my Select Statements and I just want to combine this one in one select
Statement is it possible ? I will be sending you a sample select
statement and maybe it would help you to figure it out on what I want
to do. If you can just give me a step to follow on how I can combine
2 or 3 of my Select Statement then it would greatly help me it will
serve as my guide to do the rest of the stuff ... PLEASE I AM REALLY IN
NEED OF YOUR HELP ...

Thanks
,

I have 5 SELECT STATEMENTS to be LINK INTO EMPLOYEE TABLE


***** I have this SELECT STATMENT TO GET ALL EMPLOYEES IN EMPLOYEES
TABLE WHERE DATE HIRED IS = TO DATE RANGE


SELECT dbo.Employees.EmployeeNo, dbo.Employees.LastName,
dbo.Employees.FirstName, dbo.Departments.DeptName,
dbo.Sections.SectionName, dbo.Employees.DateHired

FROM dbo.Employees LEFT OUTER JOIN
dbo.Departments ON dbo.Employees.Department =
dbo.Departments.DeptCode LEFT OUTER JOIN
dbo.Sections ON dbo.Employees.SectionCode =
dbo.Sections.SectionCode
WHERE dbo.Employees.DateHired >= '2000-01-01' AND
dbo.Employees.DateHired <= '2007-03-30'
ORDER BY dbo.Employees.LastName
---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



*** I have this select statement to get the MIN and MAX BENEFITS Amount

in a date range.


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
ORDER BY EmployeeNo, StartDate


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



*** I have this select statement to get the MIN and MAX HONORARIUM
Amount in a date range
*** Data Comes from the same table aa above


SELECT EmployeeNo, StartDate,Amount AS OLDHON, EndDate, BenefitCode,

(SELECT MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND

startdate >= '2000-01-01' AND startdate <= '2007-03-30') AS NEWHON
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
ORDER BY EmployeeNo, StartDate


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



**** This is my select Statement to get OLD and NEW Basic Salary


SELECT EmployeeNo, StartDate, BasicSalary AS oldsalary, EndDate,
(SELECT MAX([BasicSalary])
FROM EMPSALARIES T2
WHERE T2.employeeno = T.employeeno AND

startdate >= '2001-01-01' AND startdate <= '2007-03-30') AS newsalary
FROM dbo.empSalaries T
WHERE (StartDate =
(SELECT TOP 1 ([startdate])
FROM EMPSALARIES T1
WHERE T1.employeeno = T.employeeno AND

startdate >= '2001-01-01' AND startdate <= '2007-03-30'))
GROUP BY EmployeeNo, BasicSalary, StartDate, BasicSalary, EndDate
ORDER BY EmployeeNo, StartDate


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



*** I have this select Statement to GET the OLD and NEW POSITION with
date range


SELECT T.EmployeeNo, T.StartDate AS StartDate, dbo.Positions.Title
AS NEWPOSITION, Positions_1.Title AS OLDPOSITION
FROM dbo.empPositions T LEFT OUTER JOIN
dbo.Positions Positions_1 ON T.OldPosition =
Positions_1.Code LEFT OUTER JOIN
dbo.Positions ON T.NewPosition =
dbo.Positions.Code
WHERE (T.StartDate =
(SELECT TOP 1 MAX([startdate])
FROM empPositions T1
WHERE T1.employeeno = T.employeeno AND

startdate >= '2001-01-01' AND startdate <= '2007-03-30'))
GROUP BY T.EmployeeNo, T.NewPosition, T.OldPosition, T.StartDate,
dbo.Positions.Title, Positions_1.Title
HAVING (T.NewPosition <> T.OldPosition)
ORDER BY T.EmployeeNo, T.StartDate DESC


---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------



IS THERE ANYWAY THAT I CAN LINK ALL THIS SELECT STATEMENTS INTO ONE
WHERE ALL ARE LEFT OUTER JOIN TO EMPLOYEE TABLE BY EMPLOYEENO ? AND THE

RESULT SHOULD BE IN A SINGLE ROW PER EMPLOYEE.


PLEASE HELP IM NOT REALLY GOOD MAKING THIS NESTED SELECT STATEMENTS
WITH LINK TO MULTIPLE TABLES ...


PLEASE HELP.


THANK YOU SO MUCH ....