SSMS 2008 QUERY LOGIC - 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)

SSMS 2008 QUERY LOGIC

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-04 : 07:32:41

A0230 C C 1,2,3,5 Team Work
A0230 C C 1,2,3,5 Communication
A0230 G K 5 Performs assigned tasks with
A0230 G K 5 Punctuality, Leave and Discipline

I AM EXECUTE MY QUERY WITH CASE LOGIC LIKE
SELECT DISTINCT
A.EMP_STAFFID "Employee Code",
CASE WHEN O.TRANS_GOAL_COMP_TYPE='C' THEN o.TRANS_GOAL_COMP_NAME else NULL END C,
CASE WHEN O.TRANS_GOAL_COMP_TYPE='G' THEN o.TRANS_GOAL_COMP_NAME else NULL END G,

MY OUT PUT IS 6 RECORDS ==================
COLUMN1 COLUMN2 COLUMN3
------ ----- -----

A0126 K 5 Punctuality, Leave and Discipline
A0126 K 5 Quality and process improvement
A0126 Kl 5 Word Compare Feedback Saving for
A0126 C 1,2,3,5 Implementa
a0126 C 1,2,3,5 Integrity
a0126 C 1,2,3,5 Processess
MY EXPECTED OUT PUT IS SHOULD GET 3 RECORDS ONLY

COLUMN1 COLUMN2 COLUMN3
---------- ------- ------
A0126 , C 1,2,3,5 Implementa , K 5 Punctuality, Leave and Disciplin
A0126 , C 1,2,3,5 Integrity , K 5 Quality and process improvement
A0126 , C 1,2,3,5 , Kl 5 Word Compare Feedback Saving

ANYBODY KNOW PLS EXPLAIN THE WAY

A0126 C 1,2,3,5 Communication

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 10:26:29

you've not shown us full code. the reason you get 6 rows is not beacuse of CASE....WHEN

I guess its because of involved join with one to many relationship

Show some sample data with join statement and we will be able to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-05 : 00:42:48

I AM SHARING THE COMPLETE QUERY

SELECT DISTINCT
A.EMP_STAFFID "Employee Code",
--O.TRANS_GOAL_COMP_TYPE,
NT.TRANS_GOAL_COMP_NAME C,
NT1.TRANS_GOAL_COMP_NAME G,
--CASE WHEN O.TRANS_GOAL_COMP_TYPE='C' THEN o.TRANS_GOAL_COMP_NAME else '' END C,
--CASE WHEN O.TRANS_GOAL_COMP_TYPE='G' THEN o.TRANS_GOAL_COMP_NAME else ''END G,
--O.TRANS_GOAL_COMP_WEIGHTAGE,
A.EMP_FIRSTNAME + ' '+ isnull(A.EMP_MIDDLENAME,'') + ' '+ isnull(A.EMP_LASTNAME,'') "Employee Name",
H.GRADE_NAME "Grade",
I.DESIGNATION_NAME "Designation",
K.SHIFT_NAME AS "Base Shift",
M.MASTER3_NAME as "Function",
L.OU_NAME AS "Project",
E.DM_DEPT_NAME as "Department",
G.LOCATION_NAME "Location",
A.EMP_DATEOFJOINING "Date of Joining",
A.EMP_FUNCTIONALREPORTINGTO "Department Head"

FROM ERM_EMPLOYEE_MASTER A inner JOIN ERM_DEPT_MAST_T E ON E.DM_DEPT_ID=A.EMP_DEPT_ID
inner join ERM_LOCATION_MASTER G ON G.LOCATION_ID=A.EMP_LOCATION_ID
inner JOIN ERM_GRADE_MASTER H ON A.EMP_GRADE_CODE=H.GRADE_CODE
INNER JOIN ERM_DESIGNATION_MASTER I ON I.DESIGNATION_CODE=A.EMP_DESIGNATION_CODE
INNER JOIN MP_SHIFT_MASTER K ON K.SHIFT_CODE=A.EMP_SHIFTCODE
INNER JOIN ERM_OU_MASTER L ON A.OU_ID=L.OU_ID
INNER JOIN ERC_RLG_MASTER3 M ON A.MASTER3_ID=M.MASTER3_ID
INNER JOIN (SELECT DISTINCT PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_STAFFID ,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_NAME,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE
FROM PMS_NEW_EMP_TRANS_DETAILS_T WHERE PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE='C') NT ON NT.TRANS_STAFFID=A.EMP_STAFFID
INNER JOIN (SELECT DISTINCT PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_STAFFID ,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_NAME,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE
FROM PMS_NEW_EMP_TRANS_DETAILS_T WHERE PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE='G') NT1 ON NT1.TRANS_STAFFID=A.EMP_STAFFID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 10:30:15

no point in giving query alone. Without seeing some sample data we cant understand how tables are related and whether its one to many etc. Please post some sample data and explain how you want output.
see below for guidelines on posting data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-07 : 02:32:43

column1 column2 column3
m1263 , null , c1,c2,c3 dlfs
m1263 , null , c1,c2,c3,c4,sdlfsla
m1263 , null , c1,c2,c3,sdf
m1263 , k1,k2,k3df, null
m1263 , k1,k2,k3fd , null
m1263 , k1,k2,k3dsf, null

my expected out put is
above mentioned 6 ROWS should present in 3 ROWS ONLY
BECAUSE COLUMN2 AND COLUMN3 ARE ABOVE MENTIONED CASE LCGICS USED IN THE QUERY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 23:05:45

see guidelines on how to post relevant data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources