[HELP] Need get a single record

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[HELP] Need get a single record

Author

Topic

knockyo
Yak Posting Veteran

83 Posts

Posted - 2009-09-10 : 06:54:50

Hi all,

Below is my SQL,


SELECT DISTINCT     
  adiv.DivisionID,      
  a.UserID,      
  ic.WorkYear AS [Year],      
  ic.WorkMonth as [Month],   
  ----Optional-------
  ic.WeeksInMonth,     
  ic.FirstWeekMonth, 
  --ic.WorkWeek As [WorkWeek],
  MAX(ic.workweek) AS [LastSubmittedWeek],
  ic.workquarter,
  -------------------      
  adet.ProjectID
  
 FROM      
  tblActuals a WITH (NOLOCK)
  JOIN tblActualsDivisions adiv WITH (NOLOCK)
   ON adiv.ActualsID = a.ActualsID       
  JOIN tblActualsDetails adet WITH (NOLOCK)
   ON adet.ActualsDivisionID = adiv.ActualsDivisionID      
  JOIN dbo.view_Calendar ic WITH (NOLOCK)
   ON ic.WorkWeek = a.[Week]      
   AND ic.WorkYear = a.[Year]  
  LEFT JOIN tblSubJobTypes sjt WITH (NOLOCK)
   ON sjt.SubJobTypeId = adiv.SubJobTypeId      
  JOIN tblJobTypes jt WITH (NOLOCK)
   ON jt.JobTypeId = adiv.JobTypeID      
  JOIN tblDepartments d WITH (NOLOCK)
   ON d.DepartmentId = jt.DepartmentId      

 WHERE   
	a.year =2009 and workweek >=27 and workweek <=36 and a.userid=49836 and SubmittedUserId IS NOT NULL

 GROUP BY      
  adiv.DivisionID,      
  a.UserID,
  ic.WorkYear,      
  ic.WorkMonth,

  ----Optional-------      
  ic.WeeksInMonth,
  ic.FirstWeekMonth,      
  --ic.WorkWeek,
 ic.workweek, 
  ic.WorkQuarter,
  -------------------

  adet.ProjectID,      
  CASE WHEN adiv.SubmittedUserId IS NULL THEN 'N' ELSE 'Y' END,      
  d.DepartmentId,
  d.DepartmentDescription,      
  jt.JobTypeDescription,      
  sjt.SubJobTypeName,      
  adiv.SiteId,      
  adiv.CostCenterId,      
  adiv.ManagerUserId    
order by userid, projectid

Here is the table result,

Actually i want SELECT the MAX value in LastSubmittedWeek column only by projectid as below result,

DivisionID UserID Year Month WeeksInMonth FirstWeekMonth LastSubmittedWeek workquarter ProjectID
----------- ----------- ----------- ----------- ------------ -------------- ----------------- ----------- -----------

2 49836 2009 7 4 27 30 3 1324
2 49836 2009 7 4 27 30 3 4939
2 49836 2009 7 4 27 30 3 4940

Is that possible ?????

Thanks help ....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-10 : 07:18:29

use over(partition by projectid)

knockyo
Yak Posting Veteran

83 Posts

Posted - 2009-09-10 : 10:24:42

can you show me the way about the over function should put at where?

or still have others way?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-11 : 02:21:05

SELECT DISTINCT
adiv.DivisionID,
a.UserID,
ic.WorkYear AS [Year],
ic.WorkMonth as [Month],
----Optional-------
ic.WeeksInMonth,
ic.FirstWeekMonth,
--ic.WorkWeek As [WorkWeek],
MAX(ic.workweek)OVER(partition by projectid) AS [LastSubmittedWeek],
ic.workquarter,
-------------------
adet.ProjectID
from ..........

and don't use the group by clause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources