Help with Sql select - 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

General SQL Server Forums

New to SQL Server Programming

Help with Sql select

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-07-15 : 18:37:51

Hi

Here is my query which aggregates data from 2 table
dw_t_asw_host_jobdata and crmbuildto PL,

There are some jobs which are there in DW_T_ASW_HOST_JOBDATA table but not in CRMBuildtoPL, and i want all the jobs aggregated with having the word "NotDefined" for ProductLine column for the jobs not in CRMBuildtoPL table.

Here is my Query , could any body please let mw know if this sql query is valid and how to include word "NotDefined" for productLine whereever NULL....



SELECT     A.FINISH_DAY, C.PRODUCTLINE, 
           SUM(A.PENDING_MINUTES) AS TOTAL_PEND_MINUTES, SUM(A.RUN_MINUTES) AS TOTAL_RUN_MINUTES, 
          COUNT(A.JOB_STEP_ID) AS NUM_JOB_STEPS
FROM         dbo.DW_T_ASW_HOST_JOBDATA AS A left outer join
 ( select distinct jobname, productline from
  aswtrans.dbo.CRMBUILDTOPL ) C
on  A.JOB_NAME = C.JOBNAME
GROUP BY A.FINISH_DAY,C.PRODUCTLINE

karepa
Starting Member

3 Posts

Posted - 2010-07-15 : 21:49:28

Hi.

Just adapt this simple query to your necessity.

SELECT 'ID' = E.ID
, 'NAME' = E.NAME
, 'HAS_ADDRESS' = CASE WHEN EXISTS (SELECT 1 FROM [ADDRESS] A
WHERE A.EMPLOYEEID = E.ID)
THEN 'Yes'
ELSE 'No'
END
FROM EMPLOYEE E

Cheers,
Karepa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources