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]

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -