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.
| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2011-04-28 : 14:23:58
|
| How do I merge these two queries into a single query to return a single dataset? The problem Im trying to solve is to look at a table containing historical project information (I pull a snapshot every morning and add a datetime stamp), and identify the extent projects have slipped from their inception to current.-- find the current plannedenddateSELECT distinct pin, max(datamartdate) AS CurrentRecord,max(plannedenddate)as currentenddateFROM epm_project where projectstate='active' and plannedenddate is not null group by pin order by pin-- find the original plannedenddateSELECT DISTINCT pin, min(datamartdate) as OldestRecords,min(plannedenddate) AS OldestPEndDateFROM epm_project where projectstate='active' and plannedenddate is not null group by pin order by pinonce I get these two datasets together, I can calculate the enddate variance (slippage).... |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2011-04-28 : 14:31:40
|
| I'm getting dangerous...here is my solution...seems to work ok for me...-- find Project slippageSELECT distinct pin, max(datamartdate) AS CurrentRecord,max(plannedenddate)as currentenddate,min(datamartdate) as OldestRecords,min(plannedenddate) AS OldestPEndDate, cast(max(plannedenddate)-min(plannedenddate) as int) as EndDateVarianceFROM epm_project where projectstate='active' and plannedenddate is not null group by pin order by pin |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-28 : 15:34:08
|
| distinct doesn't do anything here.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2011-04-29 : 12:24:03
|
| the group by does the work?...thank you! |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2011-06-15 : 16:23:24
|
| I found a problem with this approach...its in the max min... what I actually want is the plannedenddate from the oldest record in a group...and the plannedenddate from the newest record in a group...tell me where it started, and where it current is set for plannedenddate...sooo..how do I select a value from the oldest member of a group..and a value from the youngest member of a group?. |
|
|
|
|
|
|
|
|
subqueries?