SQL Report Group - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

SQL Report Group

Author

Topic

cheatasp
Starting Member

10 Posts

Posted - 2009-05-19 : 01:58:28

Dear All,

I have found problem with report that has group section

Below query is the group

declare @Department varchar(4)
declare @JobType varchar(4)

set @Department ='%'
set @JobType='%'

SELECT DISTINCT T.JobTypeID, T.JobType
FROM TAB_JobPositionType AS T
INNER JOIN TAB_JobPosition AS J ON J.JobTypeID = T.JobTypeID
INNER JOIN TAB_Employee AS E ON J.EmpID = E.EmpID
INNER JOIN TAB_Department AS D ON E.DepartmentID = D.DepartmentID
INNER JOIN TAB_Location AS L ON E.LocationID = L.LocationID
WHERE datediff(dd,PromotionDate,getdate())>=75
AND datediff(dd,PromotionDate,getdate())<=90
AND J.JobTypeID LIKE @JobType
AND E.ContractTypeID=1
AND D.DepartmentID LIKE @Department

And below is the detail

declare @Department varchar(4)
declare @JobType varchar(4)

set @Department ='%'
set @JobType='%'

SELECT DISTINCT E. EmpID,Name, PromotionDate
,dateadd(day,90-datediff(dd,PromotionDate,getdate()),getdate()) As EndProbation
,(90-datediff(dd,PromotionDate,getdate())) AS DayLeft, D.Department, L.Location
FROM TAB_Employee AS E
INNER JOIN TAB_JobPosition AS J
ON J.EmpID = E.EmpID
INNER JOIN TAB_Department AS D ON E.DepartmentID = D.DepartmentID
INNER JOIN TAB_Location AS L ON E.LocationID = L.LocationID
WHERE datediff(dd,PromotionDate,getdate())>=75
AND datediff(dd,PromotionDate,getdate())<=90
AND JobTypeID LIKE @JobType
AND E.ContractTypeID=1
AND D.DepartmentID LIKE @Department

But when I run report it show differnce group but the same detail

please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 02:05:49

check your INNER JOIN. Make sure you specify all the columns in the foreign key in the ON

KH
[spoiler]Time is always against us[/spoiler]

cheatasp
Starting Member

10 Posts

Posted - 2009-05-19 : 02:29:26

Dear,

In table detail has foreign key JobTypeID and table main has primary key JobTypeID .
I inner join them already

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources