display distinct records - 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 2008 Forums

Transact-SQL (2008)

display distinct records

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-08 : 11:18:22

below is my stored procedure:

create PROCEDURE [dbo].[SP_LEAVEATAGLANCE_VIEW]

-- The below are the input arguments for calling the procedure

@EMP_ID INTEGER

AS

SELECT
DISTINCT(B.LEAVETYPE_NAME) AS LEAVETYPE,
A.EMP_CARRYFORWARD AS LEAVE_ELIGIBLE,
(A.EMP_CARRYFORWARD/12)*(C.MONTH) AS LEAVE_ACCRUED,
C.NO_DAYS AS LEAVE_AVAILED,
(A.EMP_CARRYFORWARD/12)*(C.MONTH)- C.NO_DAYS AS LEAVE_BALANCE

FROM
LEAVE_CARRYFORWARD A,
MASTER_LEAVETYPE B,
LEAVE C

WHERE
C.EMP_ID=@EMP_ID
AND A.EMP_ID=C.EMP_ID
AND C.LEAVE_STATUSID=1
AND C.YEAR=YEAR(GETDATE())
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND B.LEAVETYPE_ID=C.LEAVETYPE_ID
AND A.CARRYFORWARD_YEAR=C.YEAR
AND C.DEL_FLAG=0
AND B.DEL_FLAG=0

but if execute this wuery means, it displays duplicate records (i.e) distinct leavetypes is not coming

for example,

leavetype leaveaccrued leaveavailed leaveeligible leavebalance

casual leave 1 2 3 1
medical leave 2 2 0 -2

like that i want, but this query displays,

casual leave more than one records.

pls help to get distinct leavetype

i tried groupby function in where condition, its not working

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-07-08 : 12:55:28

Distinct is for 1 column, so your query does not work as expected. However you can play around to make it works.

Provide your sample tables, with data, and expected output. People here may help you out.

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-08 : 13:01:02

I'm not sure without seeing more of your data, it almost sounds like there are multiple records for casual leave with different values in the other places.

Are you looking for the sum of the other numbers etc ?

If so, I would almost think you'd have to look at using SUM( [your fields here] ) OVER (partition by leavetype) etc in there...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources