|
Home > Archive > MS SQL Server > October 2006 > Return most recent date
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Return most recent date
|
|
|
| I have multiple rows of results with duplicate information except for one
field which is "date". I'd like to return only one "Project_ID" row which
means I need to return only the most recent "Stage_Date" field. I've
included the schema, sample results and query below:
Thanks,
Colin
Current Query:
SELECT EP.PROJECT_ID AS [Item ID], EP.PROJECT_NAME AS Name,
EP.PROJECT_STAGE_ID AS [Stage ID],
ES.STAGE_NAME AS Stage, Staging.STAGE_DATE
FROM EPKP_PROJECTS EP INNER JOIN
EPKP_STAGES ES ON EP.PROJECT_STAGE_ID = ES.STAGE_ID LEFT OUTER JOIN
MSP_WEB_RESOURCES MWR ON EP.PROJECT_OWNER = MWR.WRES_ID JOIN
(SELECT PROJECT_ID, STAGE_ID, STAGE_DATE
FROM EPKP_PROJECT_STAGES PS
WHERE STAGE_ID = 9) AS Staging ON EP.PROJECT_ID = Staging.PROJECT_ID
WHERE ES.STAGE_NAME='Closed'
ORDER BY EP.PROJECT_NAME
Sample Results:
814 (CANCELLED) Wireless Access Points 9 Closed 2006-09-08 15:44:03.000
169 ABC - Oscar.com PTW 9 Closed 2006-01-11 15:52:12.000
169 ABC - Oscar.com PTW 9 Closed 2006-03-17 14:30:04.000
Schema:
CREATE TABLE [dbo].[EPKP_PROJECTS](
[PROJECT_ID] [int] NOT NULL,
[PROJECT_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT
NULL,
& #91;PROJECT_START_DA
TE] [datetime] NULL,
& #91;PROJECT_STAGE_ID
] [int] NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].& #91;EPKP_PROJECT_STA
GES](
[PROJECT_ID] [int] NULL,
[STAGE_ID] [int] NULL,
[STAGE_DATE] [datetime] NULL,
) ON [PRIMARY]
| |
| Tracy McKibben 2006-10-24, 6:30 pm |
| Colin wrote:
> I have multiple rows of results with duplicate information except for one
> field which is "date". I'd like to return only one "Project_ID" row which
> means I need to return only the most recent "Stage_Date" field. I've
> included the schema, sample results and query below:
>
> Thanks,
>
> Colin
>
> Current Query:
> SELECT EP.PROJECT_ID AS [Item ID], EP.PROJECT_NAME AS Name,
> EP.PROJECT_STAGE_ID AS [Stage ID],
>
> ES.STAGE_NAME AS Stage, Staging.STAGE_DATE
>
> FROM EPKP_PROJECTS EP INNER JOIN
>
> EPKP_STAGES ES ON EP.PROJECT_STAGE_ID = ES.STAGE_ID LEFT OUTER JOIN
>
> MSP_WEB_RESOURCES MWR ON EP.PROJECT_OWNER = MWR.WRES_ID JOIN
>
> (SELECT PROJECT_ID, STAGE_ID, STAGE_DATE
>
> FROM EPKP_PROJECT_STAGES PS
>
> WHERE STAGE_ID = 9) AS Staging ON EP.PROJECT_ID = Staging.PROJECT_ID
>
> WHERE ES.STAGE_NAME='Closed'
>
> ORDER BY EP.PROJECT_NAME
>
> Sample Results:
> 814 (CANCELLED) Wireless Access Points 9 Closed 2006-09-08 15:44:03.000
> 169 ABC - Oscar.com PTW 9 Closed 2006-01-11 15:52:12.000
> 169 ABC - Oscar.com PTW 9 Closed 2006-03-17 14:30:04.000
> Schema:
>
> CREATE TABLE [dbo].[EPKP_PROJECTS](
>
> [PROJECT_ID] [int] NOT NULL,
>
> [PROJECT_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT
> NULL,
>
> & #91;PROJECT_START_DA
TE] [datetime] NULL,
>
> & #91;PROJECT_STAGE_ID
] [int] NULL,
>
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].& #91;EPKP_PROJECT_STA
GES](
>
> [PROJECT_ID] [int] NULL,
>
> [STAGE_ID] [int] NULL,
>
> [STAGE_DATE] [datetime] NULL,
>
> ) ON [PRIMARY]
>
>
>
>
>
>
>
>
>
>
>
>
Read this:
http://realsqlguy.com/serendipity/a...st-And-Greatest!.html
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
| |
| GlennThomas5 2006-10-24, 6:30 pm |
| in your derived table return (SELECT projectid, max(date) FROM table
WHERE fields)
then join it back on your query w/ the projectid to select that
project.
Colin wrote:
> I have multiple rows of results with duplicate information except for one
> field which is "date". I'd like to return only one "Project_ID" row which
> means I need to return only the most recent "Stage_Date" field. I've
> included the schema, sample results and query below:
>
> Thanks,
>
> Colin
>
> Current Query:
> SELECT EP.PROJECT_ID AS [Item ID], EP.PROJECT_NAME AS Name,
> EP.PROJECT_STAGE_ID AS [Stage ID],
>
> ES.STAGE_NAME AS Stage, Staging.STAGE_DATE
>
> FROM EPKP_PROJECTS EP INNER JOIN
>
> EPKP_STAGES ES ON EP.PROJECT_STAGE_ID = ES.STAGE_ID LEFT OUTER JOIN
>
> MSP_WEB_RESOURCES MWR ON EP.PROJECT_OWNER = MWR.WRES_ID JOIN
>
> (SELECT PROJECT_ID, STAGE_ID, STAGE_DATE
>
> FROM EPKP_PROJECT_STAGES PS
>
> WHERE STAGE_ID = 9) AS Staging ON EP.PROJECT_ID = Staging.PROJECT_ID
>
> WHERE ES.STAGE_NAME='Closed'
>
> ORDER BY EP.PROJECT_NAME
>
> Sample Results:
> 814 (CANCELLED) Wireless Access Points 9 Closed 2006-09-08 15:44:03.000
> 169 ABC - Oscar.com PTW 9 Closed 2006-01-11 15:52:12.000
> 169 ABC - Oscar.com PTW 9 Closed 2006-03-17 14:30:04.000
> Schema:
>
> CREATE TABLE [dbo].[EPKP_PROJECTS](
>
> [PROJECT_ID] [int] NOT NULL,
>
> [PROJECT_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT
> NULL,
>
> & #91;PROJECT_START_DA
TE] [datetime] NULL,
>
> & #91;PROJECT_STAGE_ID
] [int] NULL,
>
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].& #91;EPKP_PROJECT_STA
GES](
>
> [PROJECT_ID] [int] NULL,
>
> [STAGE_ID] [int] NULL,
>
> [STAGE_DATE] [datetime] NULL,
>
> ) ON [PRIMARY]
| |
|
|
| Tracy McKibben 2006-10-24, 6:30 pm |
| GlennThomas5 wrote:
> Just to let you know tracy you have a typo in your table bookprice(s).
> =P heheh
>
Cheap labor - you get what you pay for... :-)
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|
|
|
|
|