November 11, 2012 at 5:45 am
The PROJECT_LEVEL column should return the MAX DASHBOARD_STATUS_LEVEL for every MARKET_PROJECT of the same id (instead it appears to be returning the value within the DASHBOARD_STATUS_LEVEL column for every record).
For Example:
For Austin T6-2011-03157, the MAX DASHBOARD_STATUS_LEVEL is 3, therefore the PROJECT_LEVEL for each of the four records should be 3.
For DALLAS-L6-2012-1122, the MAX DASHBOARD_STATUS_LEVEL is 2, therefore the PROJECT_LEVEL for each of the two records should be 2.
MARKET_NAME MARKET_PROJECT OVP_AMT DASHBOARD_STATUS_LEVEL PROJECT_LEVEL
AUSTIN AUSTIN-T6-2011-03157 125.00 3 3
AUSTIN AUSTIN-T6-2011-03157 137.00 2 2
AUSTIN AUSTIN-T6-2011-03157 52.27 2 2
AUSTIN AUSTIN-T6-2011-03157 1.24 1 1
DALLAS DALLAS-L6-2012-1122 77.34 1 1
DALLAS DALLAS-L6-2012-1122 1122.25 2 2
Below is the SELECT Statement within my query:
SELECT
SQL10.MARKET_NAME,
SQL10.MARKET_PROJECT,
SQL10.PROJECT_STATUS,
SQL10.PROJ_TYPE_NAME,
SQL10.DAYS_IN_LAST_PROJ_STAT,
SQL10.PROV_NAME,
SQL10.PROV_NUM,
SQL10.PROV_IRS,
SQL10.PROVIDER_STATUS,
SQL10.TOTAL_UNVALIDATED_CLAIMS,
SQL10.TOTAL_PROVIDERS,
SQL10.OVP,
SQL10.LOAD_DT,
SQL10.OK_TO_ASSIGN_DT,
SQL10.ASSIGNED_DATE,
SQL10.DAYS_UNTIL_EXPIRE,
SQL10.DASHBOARD_STATUS_LEVEL,
MAX(SQL10.DASHBOARD_STATUS_LEVEL) AS [PROJECT_LEVEL],
Can anyone help Please???
November 11, 2012 at 11:57 am
If I've understood your requirements properly the following should do what you require
create table #test (
market_name varchar(6),
market_project varchar(25),
ovp_amt decimal(10,2),
dashboard_status_level int,
project_level int
)
insert into #test
values
('AUSTIN', 'AUSTIN-T6-2011-03157', 125.00, 3, null),
('AUSTIN', 'AUSTIN-T6-2011-03157', 137.00, 2, null),
('AUSTIN', 'AUSTIN-T6-2011-03157', 52.27, 2, null),
('AUSTIN', 'AUSTIN-T6-2011-03157', 1.24, 1, null),
('DALLAS', 'DALLAS-L6-2012-1122', 77.34, 1, null),
('DALLAS', 'DALLAS-L6-2012-1122', 1122.25, 2, null)
;with maxStatLevel as (
select market_project, max(dashboard_status_level) max_level
from #test
group by market_project
)
update t
set project_level = m.max_level
--select t.market_name, t.market_project, t.ovp_amt, t.dashboard_status_level, m.max_level
from #test t
inner join maxStatLevel m on t.market_project = m.market_project
select * from #test
drop table #test
November 16, 2012 at 1:20 pm
A more basic approach, in case you are not familiar with the CTE function ... would look something like
select market_project, max(dashboard_status_level) max_level
into #mytempTable
from mainTable
group by market_project
/* this creates a temporary table to reference in your SSMS windows*/
update m
set project_level = t.max_level
from mainTable as m inner join #mytemptable as t on t.market_project = m.market_project
drop table #myTempTable
I would still prefer the CTE construct depicted above however.
----------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply