| Author |
 Topic  |
|
|
mcjr8503
Starting Member
USA
6 Posts |
 Posted - 05/10/2012 : 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
India
47173 Posts |
Posted - 05/10/2012 : 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
USA
6 Posts |
Posted - 05/10/2012 : 15:57:00
|
Can you import attachments?
Thank You |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 05/10/2012 : 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
USA
6 Posts |
Posted - 05/10/2012 : 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
USA
6 Posts |
Posted - 05/10/2012 : 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
India
47173 Posts |
|
|
mcjr8503
Starting Member
USA
6 Posts |
Posted - 05/10/2012 : 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
USA
6 Posts |
Posted - 05/10/2012 : 16:36:32
|
I removed all the other data because it was not distinct.
Thank You |
|
|
| |
Topic |
|
|
|
New to SQL, Need Help
