November 22, 2011 at 12:23 am
Hi,
From the following table :-
create table A4 (
EEID int,
EFDT date,
DATA char (4)
)
insert into A4 values (6,'03/01/1987', 'A')
insert into A4 values (8,'03/01/1988', 'L')
insert into A4 values (7,'03/01/1989', 'T')
insert into A4 values (6,'03/01/1990', 'T')
insert into A4 values (7,'03/01/1991', 'L')
insert into A4 values (8,'03/01/1992', 'T')
insert into A4 values (6,'03/01/1993', 'L')
insert into A4 values (7,'03/01/1975', 'A')
insert into A4 values (8,'03/01/1976', 'T')
insert into A4 values (6,'03/01/1977', 'A')
I want to query out data in the following way :-
eeid MaxDate Maxdate_Data MinDate MinDate_Data
61993-03-01 00:00:00.000 L 1977-03-01 00:00:00.000 A
71991-03-01 00:00:00.000 L 1975-03-01 00:00:00.000 A
81992-03-01 00:00:00.000 T 1976-03-01 00:00:00.000 T
Is there any method other than creating Function or #tables
Thanks
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
November 22, 2011 at 1:16 am
Here is one way of doing it. Take into consideration that you showed us an example and didn’t write any specification, so the example works on the dataset that you provided, but might not be what you need. For example if you have more then 1 record per min or max date and EEID, you will more then one record in the query that I’ve provided. If this is not what you need, pleas specify what to do in such case.
;with MyCTE as (
select EEID, max(EFDT) as MaxDate, min(EFDT) as MinDate
from A4
group by EEID)
select M.EEID, M.MaxDate, AMax.DATA, M.MinDate, AMin.DATA
from MyCTE as M INNER JOIN A4 as AMax ON M.EEID = AMax.EEID AND M.MaxDate = AMax.EFDT
INNER JOIN A4 AMin ON M.EEID = AMin.EEID AND M.MinDate = AMin.EFDT
ORDER BY M.EEID
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 1:20 am
How is this?
; WITH MaxValues AS
(
SELECT EEID , EFDT , DATA ,
MaxRow = ROW_NUMBER() OVER(PARTITION BY EEID ORDER BY EFDT DESC)
FROM A4
)
,MinValues AS
(
SELECT EEID , EFDT , DATA ,
MinRow = ROW_NUMBER() OVER(PARTITION BY EEID ORDER BY EFDT)
FROM A4
)
SELECT BaseTable.EEID , BaseTable.EFDT AS Max_date , BaseTable.DATA AS Max_date_data
,CrsApp.EFDT AS Min_date , CrsApp.DATA AS Min_date_data
FROM MaxValues BaseTable
CROSS APPLY
( SELECT *
FROM MinValues InnerTable
WHERE InnerTable.MinRow = 1 AND InnerTable.EEID = BaseTable.EEID
) CrsApp
WHERE BaseTable.MaxRow = 1
Caution: Untested as i was not with a system that had SQL Server on it while i replied. Please test it.
November 22, 2011 at 1:22 am
Thanks Adi this is perfect and clears my concept on Joins as well.
Regards,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
November 22, 2011 at 1:27 am
Thanks cold coffee,
But I was looking for solution that Adi provided. I hope you too would appreciate his code.
Regards,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply