New to SQL, Need Help - 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 2005 Forums

Transact-SQL (2005)

New to SQL, Need Help

Author

Topic

mcjr8503
Starting Member

6 Posts

Posted - 2012-05-10 : 15:47:54

Afternoon,

Can some one help please. Im doing some reporting for work and running into a issue. I feel so close, yet so far away. Ive been thinking about using a if statement to drop off the users that dont apply but dont know where to start.

I have two course curriculums that Employees(Users) have to take. The Curriculum's(Parent) are made up of 14 Web Based Courses(Children) each. The Users are broken into two groups "New" and "Existing". New Users/Transfer Users are required to the take the Parent labled "NewCurriculum" and the Existing Users take the Parent labled "ExistingCurrilum". I would filter on the User's Hire Date but as stated, I have transfer Users as well.

So 4 courses make the difference between the two Parent curriculums because ten of them require completion of the same course code.(Example: In college all students are required to take English 101 but at the same time all students arent required to take Business 401)

I would run the report on the Curriculum Course code but on the report I have to show the Parent being completed as well as the Children courses.

The SQL Code below that Im using is working correct but I now need to distinguish between the New/Transfer Hires and the Existing Employees.

SELECT DISTINCT
dimActivity.ActivityName, dimActivity.Code, dimActivity_1.Code AS Code2, dimActivity.ActivityLabel, dimUser.EmpNo, dimUser.EmpLName,
dimUser.EmpFName, dimUser.EmpMI, dimUser.EmpCode, dimUser.EmpStartDt, dimUser.EmpStat, dimUser.OptEmp_Dt2, dimUser.EmpEndDt,
dimUser.PrimaryJobName, factAttempt.CompletionStatusID, dimDate.Date AS StartDate, dimDate_1.Date AS CompletionDate, factAttempt.Score,
dimUser.MgrEmpFullName2
FROM dimUser INNER JOIN
factAttempt ON dimUser.ID = factAttempt.UserID INNER JOIN
dimActivity ON factAttempt.ActivityID = dimActivity.ID INNER JOIN
dimDate ON factAttempt.StartDtID = dimDate.DateID INNER JOIN
dimDate AS dimDate_1 ON factAttempt.EndDtID = dimDate_1.DateID INNER JOIN
dimActivity AS dimActivity_1 ON factAttempt.ActivityID = dimActivity_1.ID
WHERE (dimActivity.Code IN (N'COMPMED2011_NEWMEDICARESPECIALIZED_CUR', N'COMPMED2011_NEWMEDICARESPECIALIZED_OBJ1',
N'COMPCATALOGMED_4153', N'COMPCATALOGMED_3452', N'COMPCATALOGMED_2699', N'COMPCATALOGMED_2727',
N'COMPCATALOGMED_2708', N'COMPCATALOGMED_4191', N'COMPCATALOGMED_2768', N'COMPCATALOGMED_2731',
N'COMPCATALOGMED_2733', N'COMPCATALOGMED_2754', N'COMPCATALOGMED_2725', N'COMPFORMMED_CERT4470',
N'COMPFORMMED_EVAL3491', N'COMPMED2011', N'COMPMED2011_ANNMEDICARESPECIALIZED_CUR',
N'COMPMED2011_ANNMEDICARESPECIALIZED_OBJ1', N'COMPCATALOGMED_3836', N'COMPEXAMMED_MEDICARE',
N'COMPFORMMED2011_CERT4602'))
ORDER BY dimUser.EmpLName, dimUser.EmpFName, dimActivity.ActivityLabel, dimActivity.Code

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 15:54:48

post some sample data and explain what you want. Without that we cant understand what you meant by New/Transfer Hires ,existing employees etc as we cant see your tables

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

mcjr8503
Starting Member

6 Posts

Posted - 2012-05-10 : 15:57:00

Can you import attachments?

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 15:59:32

quote:
Originally posted by mcjr8503

Can you import attachments?

Thank You

Post the data here (only indicative data ie 5 - 10 rows from tables would do)

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

mcjr8503
Starting Member

6 Posts

Posted - 2012-05-10 : 16:08:40

Activity Name Code Activity Label Emp No Emp LName Emp FName Emp MI
2011 Ann Medica ANNCUR CURRICULUM 123456 LNAME FNAME
ADMINADV PLANPDP WEBCOR 123456 LNAME FNAME
ENROLLMENT COM2768 WEBCOR 123456 LNAME FNAME
ADVOVERVIEW COM3452 WEBCOR 123456 LNAME FNAME
FRAUDPDP COM3836 WEBCOR 123456 LNAME FNAME

Thank You

mcjr8503
Starting Member

6 Posts

Posted - 2012-05-10 : 16:13:08

You can make this into a CSV File.

Activity Name, Code, Activity Label, Emp No, Emp LName, Emp FName, Emp MI
2011 Ann Medica ,ANNCUR, CURRICULUM ,123456, LNAME, FNAME
ADMINADV, PLANPDP, WEBCOR, 123456, LNAME, FNAME
ENROLLMENT, COM2768, WEBCOR, 123456, LNAME, FNAME
ADVOVERVIEW, COM3452, WEBCOR, 123456, LNAME, FNAME
FRAUDPDP, COM3836, WEBCOR, 123456, LNAME, FNAME

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 16:18:15

where are data for all the involved tables? I see about 5 tables used above and cant see posted data for all

see below for guidelines on posting the data
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mcjr8503
Starting Member

6 Posts

Posted - 2012-05-10 : 16:24:32

You want the select and from clause? I apologize for my ignorance but again I havent touched SQL for 3.5yrs since I graduated thanks again for you help?

Thank You

mcjr8503
Starting Member

6 Posts

Posted - 2012-05-10 : 16:36:32

I removed all the other data because it was not distinct.

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources