Forums - C# Corner

Forum guidelines
Riddhi Valecha

Riddhi Valecha

  • 481
  • 2.5k
  • 167.4k

SQL Query - PLs HELP

Feb 22 2016 4:27 AM

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

Girish.Shah@yahoo.com

A

1

2

Grishma.Sharma

Ashish.Chakraborty

Grishma.Sharma@yahoo.com

 

1

3

Ashish.Chakraborty

Sanjay.Dutta

Ashish.Chakraborty@yahoo.com

C

1

4

Arpita.Sinha

Jignasa.Ved

Arpita.Sinha@yahoo.com

A

1

5

Alok.Kadu

Jignasa.Ved

Alok.Kadu@yahoo.com

C

1

6

Jignasa.Ved

Sanjay.Dutta

Jignasa.Ved@yahoo.com

 

1

7

Amrita.Verma

Pooja.Kapil

Amrita.Verma@yahoo.com

 

1

8

Pooja.Kapil

Sanjay.Dutta

Pooja.Kapil@yahoo.com

C

1

9

Sumit.Sharma

Pooja.Kapil

Sumit.Sharma@yahoo.com

C

1

10

Sanjay.Dutta

Sanjay.Dutta

Sanjay.Dutta@yahoo.com

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

 

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

Girish.Shah@yaho.com

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

Jignasa.Ved@yahoo.com

 

Beginner

Q2

75% ( 4 persons report to Sanjay.Dutta , he sent only 3 persons to adobe training ).

Adobe

 

Pooja.Kapil

Sanjay.Dutta

Pooja.Kapil@yahoo.com

C

Intermediate

Q3

 
                 

And likewise – the entire list…

Please guide..

 Its urgent..
 
Please Help

Answers (14)