Hi all, I need help in forming SQL Query ..
My Master Tables-
1. Employees
| ID | EmployeeName | ManagerName | EmailID | Dept | Flag |
| 1 | Girish.Shah | Ashish.Chakraborty | A | 1 | |
| 2 | Grishma.Sharma | Ashish.Chakraborty | B | 1 | |
| 3 | Ashish.Chakraborty | Sanjay.Dutta | C | 1 | |
| 4 | Arpita.Sinha | Jignasa.Ved | A | 1 | |
| 5 | Alok.Kadu | Jignasa.Ved | C | 1 | |
| 6 | Jignasa.Ved | Sanjay.Dutta | B | 1 | |
| 7 | Amrita.Verma | Pooja.Kapil | B | 1 | |
| 8 | Pooja.Kapil | Sanjay.Dutta | C | 1 | |
| 9 | Sumit.Sharma | Pooja.Kapil | C | 1 | |
| 10 | Sanjay.Dutta | Sanjay.Dutta | C | 1 |
Column-“EmployeeName” – List of all employess. (Even Managers are employess).
Column – ID and EmployeeName – are unique.
2. TrainingType
| ID | Training Type | Flag |
| 1 | Technical | 1 |
| 2 | Non - Technical | 1 |
3. Training Level
| ID | TrainingLevel | Flag |
| 1 | Beginner | 1 |
| 2 | Intermediate | 1 |
| 3 | Professional | 1 |
4. Training Name
| ID | TrainingLevelID | TrainingTypeID | TrainingName | Flag |
| 1 | 1 | 1 | Oracle 12c | 1 |
| 2 | 1 | 2 | Email writing skills | 1 |
| 3 | 3 | 2 | Presentation Skills | 1 |
| 4 | 2 | 1 | Adobe | 1 |
TrainingLEvelID – ForeignKEy of ID column of TrainingLevel table.
TrainingTypeID – ForeignKey of ID Column of TrainingType table.
5. Other subject
| ID | TrainingLevelID | TrainingTypeID | Other TrainingName | Flag |
| 1 | 1 | 1 | LINQ | 1 |
| 2 | 1 | 2 | Business meetings | 1 |
| 3 | 3 | 2 | Managing Top performers | 1 |
| 4 | 2 | 1 | Video Streaming | 1 |
TrainingLEvelID – ForeignKEy of ID column of TrainingLevel table.
TrainingTypeID – ForeignKey of ID Column of TrainingType table.
Logic-
If the user does not find appropriate training name in the dropdownlist in front-end, he will select a subject from “Other Subject” in other dropdownlist from front-end.
TRANSACTION TABLE –
| ID | ManagerID | EmployeeID | TrainingLevelID | TrainingTypeID | TrainingID | OtherSubjectID | TimeLine | Remarks | Flag |
| 1 | 3 | 1 | 1 | 1 | 1 | 0 | Q1 | Abcm | 1 |
| 2 | 6 | 4 | 2 | 1 | 4 | 0 | Q2 | Dsf | 1 |
| 3 | 6 | 5 | 1 | 1 | 0 | 1 | Q3 | Sdf | 1 |
| 4 | 3 | 2 | 1 | 2 | 0 | 2 | Q4 | T | 1 |
| 5 | 8 | 9 | 2 | 2 | 0 | 3 | Q4 | S | 1 |
| 6 | 8 | 7 | 3 | 2 | 4 | 0 | Q3 | D | 1 |
| 7 | 10 | 8 | 3 | 2 | 0 | 3 | Q2 | df | 1 |
ManagerID – ID in EmployeeTable – 3,6,8 are managers.
Logic – “select count(*) from Employees where managerName = @ManagerName (parameter from front-end).
If count(*) > 0; select employeesname from Employees where managername = @ManagerName.
EmployeeID – it can be anyID (Every Manager is employee and each each employee has a manager).
TrainingTypeID – Foreign Key of TrainingType table.
If user selects training name from 1st dropdownlist, then that ID will be inserted to TrainingID and OtherSubjectID will be 0.
If user select “Other Subject”, then TrainingTypeID will be 0.
I need the queries for following output -
1.
| Subject Name | Other Subject | Total Reports | Level |
| Oracle 12c | NULL | 1 (Only 1 employee was send to oracle training) | Beginner - 1 |
| Email writing skills | NULL | 0 | Beginner |
| Presentation Skills | NULL | 0 | Beginner |
| Adobe | NULL | 2 (Only 2 person went for adobe training) | Beginner-1 Intermediate -1 |
| NULL | LINQ | 1 | Beginner |
How do I get this query ?
One Columns – List of Subjects
Second Column – List of other subject
Total Report – Count (*) of how many employee attended that training
Level Wise division – How many for beginner, how many for intermediate, how many for expert
2. The “Total Reports” column will be a hyperlink – When the user will click on “1” or “2”, he must get the following –
| Subject | Oterh Subject | Employee | Manager | EmailID | Dept | LevelOfTraining | Quarter | Percentage |
| Oracle 12c | NULL | Girish.Shah | Ashish.Chakraborty | A | Beginner | Q4 | 50% ( 2 persons report to Ashish.Chakraborty, he sent only 1 person to oracle 12c Training) | |
| Adobe | NULL | Jignasa.Ved | Sanjay.Dutta | B | Beginner | Q2 | 75% ( 4 persons report to Sanjay.Dutta , he sent only 3 persons to adobe training ). | |
| Adobe |
| Pooja.Kapil | Sanjay.Dutta | C | Intermediate | Q3 |
| |
|
|
|
|
|
|
|
|
|
|
And likewise – the entire list…
Please guide..
Riddhi ValechaPosted Feb 20, 2016, 5:43 AM
Riddhi ValechaPosted Feb 19, 2016, 4:05 PM