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.
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.MgrEmpFullName2FROM 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.IDWHERE (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.CodeThank 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 MVPhttp://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 MVPhttp://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 FNAMEADVOVERVIEW COM3452 WEBCOR 123456 LNAME FNAMEFRAUDPDP COM3836 WEBCOR 123456 LNAME FNAMEThank 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, FNAMEADVOVERVIEW, COM3452, WEBCOR, 123456, LNAME, FNAMEFRAUDPDP, COM3836, WEBCOR, 123456, LNAME, FNAMEThank You |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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 |
 |
|
|
|
|
|
|