SQL Query - PLs HELP

Riddhi Valecha

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	[email protected]	A	1
2	Grishma.Sharma	Ashish.Chakraborty	[email protected]		1
3	Ashish.Chakraborty	Sanjay.Dutta	[email protected]	C	1
4	Arpita.Sinha	Jignasa.Ved	[email protected]	A	1
5	Alok.Kadu	Jignasa.Ved	[email protected]	C	1
6	Jignasa.Ved	Sanjay.Dutta	[email protected]		1
7	Amrita.Verma	Pooja.Kapil	[email protected]		1
8	Pooja.Kapil	Sanjay.Dutta	[email protected]	C	1
9	Sumit.Sharma	Pooja.Kapil	[email protected]	C	1
10	Sanjay.Dutta	Sanjay.Dutta	[email protected]	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 1^st 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 -

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	[email protected]	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	[email protected]		Beginner	Q2	75% ( 4 persons report to Sanjay.Dutta , he sent only 3 persons to adobe training ).
Adobe		Pooja.Kapil	Sanjay.Dutta	[email protected]	C	Intermediate	Q3

And likewise – the entire list…

Please guide..

Its urgent..

Please Help

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Riddhi Valecha10y ago

Hi all..I again need help...in forming the SQL Query -

Please Help Me Out...

In my usermaster table, there are 500 users.

First two columns - ID and Domain Name are unique.

In my transaction table, Manager ID = ID of User Master table where IsAllowed=0(HE is a manager).

Now, I want the following output -

Total Employees Reporting to one manager - Query -

select * from tbl_UserMaster where varManagerName = 'Pooja.Shah'

Manager Name	Total Employee Reporting to him	Total Employee Send For training	Total Employee Not Send For training	% Of Employee Send For Training	% Of Employee Not Send For Training
Pooja.Patel	10	2	8	80%	20%
Vivek.Shah	100	25	75	25%	75%

I want this data of all employees...

Also, I want a search query for this - If from front end, I send multiple Manager ID (say 4,5,6) , then records for 3 employees must be displayed.

--------------------------------

Table Scripts -

SET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TBL_TRANSACTION]( [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [ManagerID] [numeric](18, 0) NOT NULL, [EmployeeID] [numeric](18, 0) NOT NULL, [TrainingTypeID] [numeric](18, 0) NOT NULL, [TrainingLevelID] [numeric](18, 0) NOT NULL, [SubjectAreaID] [numeric](18, 0) NULL, [SubjectID] [numeric](18, 0) NOT NULL, [OtherSubjectID] [numeric](18, 0) NOT NULL, [TrainingRemarks] [varchar](1000) NOT NULL, [TimeLine] [varchar](50) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [numeric](18, 0) NOT NULL, [UpdatedDate] [datetime] NULL, [UpdatedBy] [numeric](18, 0) NULL, [Flag] [bit] NOT NULL, CONSTRAINT [PK_TBL_TRANSACTION] PRIMARY KEY CLUSTERED ( [ID] ASC, [EmployeeID] ASC, [TrainingTypeID] ASC, [TrainingLevelID] ASC, [SubjectID] ASC, [OtherSubjectID] ASC, [TimeLine] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UK_TBL_TRANSACTION] UNIQUE NONCLUSTERED ( [EmployeeID] ASC, [TrainingTypeID] ASC, [TrainingLevelID] ASC, [SubjectID] ASC, [OtherSubjectID] ASC, [TimeLine] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO
-------------
SET ANSI_PADDING ONGOCREATE TABLE [dbo].[TBL_USERSMASTER]( [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [varEmployee] [varchar](200) NULL, [varManagerName] [varchar](80) NULL, [varEmail] [varchar](80) NULL, [varContactNo] [varchar](100) NULL, [varLocation] [varchar](150) NULL, [varCity] [varchar](30) NULL CONSTRAINT [PK_TBL_USERSMASTER] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO

----------

Also, there is a column - VarEmpCode - we have to select only those employees whose varEmpCode column first character is 'P'..not any other

Riddhi Valecha10y ago
Hi.. I tried using CTE - but failed ... PLease Help ...

with cte as
(
select x.Subject_Name ,x.SID ,x.Other_Subject , x.OID , null as 'Total_Reports' , cast(x.Level as varchar(max)) + ' - ' + cast( x.Total_Reports as varchar(max)) as 'Level Wise Report' from (
(select
tt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',
null as 'OID' , null as 'Other_Subject',
cast( count(tt.SubjectID) as varchar(max)) as 'Total_Reports',
--count(distinct (case when tl.ID = 1 then 1 else 0 end)) as 'Beginner',
--count(distinct (case when tl.ID = 2 then 1 else 0 end)) as 'Intermediate',
--count(distinct (case when tl.ID = 3 then 1 else 0 end)) as 'Expert',
(select ll.ILevel from TBL_LEVELMASTER ll where ll.Flag = 1 and ll.ID = tt.TrainingLevelID) as 'Level'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn
where tt.SubjectID = tn.ID and tt.Flag =1 and tt.TrainingLevelID = tl.ID
group by tt.SubjectID,tl.ID , tt.TrainingLevelID
union all
select
null as 'SID' , null as 'Subject_Name',
tt.OtherSubjectID as 'OID', (select sm.SubjectName from TBL_OTHERSUBJECTS sm where sm.Flag = 1 and sm.ID = tt.OtherSubjectID) as 'Other_Name',
cast( count(tt.OtherSubjectID) as varchar(max)) as 'Total_Reports',
--count(distinct (case when tl.ID = 1 then 1 else 0 end)) as 'Beginner',
--count(distinct (case when tl.ID = 2 then 1 else 0 end)) as 'Intermediate',
--count(distinct (case when tl.ID = 3 then 1 else 0 end)) as 'Expert',
(select ll.ILevel from TBL_LEVELMASTER ll where ll.Flag = 1 and ll.ID = tt.TrainingLevelID) as 'Level'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_OTHERSUBJECTS tn
where tt.OtherSubjectID = tn.ID and tt.Flag =1 and tt.TrainingLevelID = tl.ID
group by tt.OtherSubjectID ,tl.ID , tt.TrainingLevelID )) x
union
select a.Subject_Name, a.SID,a.Other_Subject,a.OID, cast(a.Total_Reports as varchar(max)) as 'Total_Reports' , null as 'Level Wise Report' from
(select
tt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',
null as 'OID' , null as 'Other_Subject',
cast( count(tt.SubjectID)/3 as varchar(max)) as 'Total_Reports'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn
where tt.SubjectID = tn.ID and tt.Flag =1
group by tt.SubjectID
union
select
null as 'SID', null as 'Subject_Name',
tt.OtherSubjectID as 'OID', (select o.SubjectName from TBL_OTHERSUBJECTS o where o.Flag=1 and o.ID = tt.OtherSubjectID) as 'Other_Subject',
cast(count(tt.OtherSubjectID)/3 as varchar(max)) as 'Total_Reports'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_OTHERSUBJECTS os
where tt.OtherSubjectID = os.ID and tt.Flag =1
group by tt.OtherSubjectID )a
group by a.SID, a.OID , a.Subject_Name , a.Other_Subject, a.Total_Reports
having max(a.SID) = min(a.SID) and max(a.Subject_Name) = min(a.Subject_Name) and max(a.OID) = min(a.OID)
and max(a.Other_Subject) = min(a.Other_Subject) and max(a.Total_Reports) = min(a.Total_Reports)
)
select * from cte
union all
select x.Subject_Name ,x.SID ,x.Other_Subject , x.OID , null as 'Total_Reports' , cast(x.Level as varchar(max)) + ' - ' + cast( x.Total_Reports as varchar(max)) as 'Level Wise Report' from (
(select
tt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',
null as 'OID' , null as 'Other_Subject',
cast( count(tt.SubjectID) as varchar(max)) as 'Total_Reports',
--count(distinct (case when tl.ID = 1 then 1 else 0 end)) as 'Beginner',
--count(distinct (case when tl.ID = 2 then 1 else 0 end)) as 'Intermediate',
--count(distinct (case when tl.ID = 3 then 1 else 0 end)) as 'Expert',
(select ll.ILevel from TBL_LEVELMASTER ll where ll.Flag = 1 and ll.ID = tt.TrainingLevelID) as 'Level'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn
where tt.SubjectID = tn.ID and tt.Flag =1 and tt.TrainingLevelID = tl.ID
group by tt.SubjectID,tl.ID , tt.TrainingLevelID
union all
select
null as 'SID' , null as 'Subject_Name',
tt.OtherSubjectID as 'OID', (select sm.SubjectName from TBL_OTHERSUBJECTS sm where sm.Flag = 1 and sm.ID = tt.OtherSubjectID) as 'Other_Name',
cast( count(tt.OtherSubjectID) as varchar(max)) as 'Total_Reports',
--count(distinct (case when tl.ID = 1 then 1 else 0 end)) as 'Beginner',
--count(distinct (case when tl.ID = 2 then 1 else 0 end)) as 'Intermediate',
--count(distinct (case when tl.ID = 3 then 1 else 0 end)) as 'Expert',
(select ll.ILevel from TBL_LEVELMASTER ll where ll.Flag = 1 and ll.ID = tt.TrainingLevelID) as 'Level'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_OTHERSUBJECTS tn
where tt.OtherSubjectID = tn.ID and tt.Flag =1 and tt.TrainingLevelID = tl.ID
group by tt.OtherSubjectID ,tl.ID , tt.TrainingLevelID )) x
union
select a.Subject_Name, a.SID,a.Other_Subject,a.OID, cast(a.Total_Reports as varchar(max)) as 'Total_Reports' , null as 'Level Wise Report' from
(select
tt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',
null as 'OID' , null as 'Other_Subject',
cast( count(tt.SubjectID)/3 as varchar(max)) as 'Total_Reports'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn
where tt.SubjectID = tn.ID and tt.Flag =1
group by tt.SubjectID
union
select
null as 'SID', null as 'Subject_Name',
tt.OtherSubjectID as 'OID', (select o.SubjectName from TBL_OTHERSUBJECTS o where o.Flag=1 and o.ID = tt.OtherSubjectID) as 'Other_Subject',
cast(count(tt.OtherSubjectID)/3 as varchar(max)) as 'Total_Reports'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_OTHERSUBJECTS os
where tt.OtherSubjectID = os.ID and tt.Flag =1
group by tt.OtherSubjectID )a
where a.SID not in (select cte.SID from cte)
Riddhi Valecha10y ago
Hi..I tried, but failed... My Query -

select a.Subject_Name, a.SID,a.Other_Subject,a.OID, cast(a.Total_Reports as varchar(max)) as 'Total_Reports' , null as 'Level Wise Report' from
(select
tt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',
null as 'OID' , null as 'Other_Subject',
cast( count(tt.SubjectID)/3 as varchar(max)) as 'Total_Reports'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn
where tt.SubjectID = tn.ID and tt.Flag =1
group by tt.SubjectID
union
select
null as 'SID', null as 'Subject_Name',
tt.OtherSubjectID as 'OID', (select o.SubjectName from TBL_OTHERSUBJECTS o where o.Flag=1 and o.ID = tt.OtherSubjectID) as 'Other_Subject',
cast(count(tt.OtherSubjectID)/3 as varchar(max)) as 'Total_Reports'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_OTHERSUBJECTS os
where tt.OtherSubjectID = os.ID and tt.Flag =1
group by tt.OtherSubjectID )a
union
select x.Subject_Name ,x.SID ,x.Other_Subject , x.OID , null as 'Total_Reports' , cast(x.Level as varchar(max)) + ' - ' + cast( x.Total_Reports as varchar(max)) as 'Level Wise Report' from (

(select
tt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',
null as 'OID' , null as 'Other_Subject',
cast( count(tt.SubjectID) as varchar(max)) as 'Total_Reports',
--count(distinct (case when tl.ID = 1 then 1 else 0 end)) as 'Beginner',
--count(distinct (case when tl.ID = 2 then 1 else 0 end)) as 'Intermediate',
--count(distinct (case when tl.ID = 3 then 1 else 0 end)) as 'Expert',
(select ll.ILevel from TBL_LEVELMASTER ll where ll.Flag = 1 and ll.ID = tt.TrainingLevelID) as 'Level'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn
where tt.SubjectID = tn.ID and tt.Flag =1 and tt.TrainingLevelID = tl.ID
group by tt.SubjectID,tl.ID , tt.TrainingLevelID
union all
select
null as 'SID' , null as 'Subject_Name',
tt.OtherSubjectID as 'OID', (select sm.SubjectName from TBL_OTHERSUBJECTS sm where sm.Flag = 1 and sm.ID = tt.OtherSubjectID) as 'Other_Name',
cast( count(tt.OtherSubjectID) as varchar(max)) as 'Total_Reports',
--count(distinct (case when tl.ID = 1 then 1 else 0 end)) as 'Beginner',
--count(distinct (case when tl.ID = 2 then 1 else 0 end)) as 'Intermediate',
--count(distinct (case when tl.ID = 3 then 1 else 0 end)) as 'Expert',
(select ll.ILevel from TBL_LEVELMASTER ll where ll.Flag = 1 and ll.ID = tt.TrainingLevelID) as 'Level'
from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_OTHERSUBJECTS tn
where tt.OtherSubjectID = tn.ID and tt.Flag =1 and tt.TrainingLevelID = tl.ID
group by tt.OtherSubjectID ,tl.ID , tt.TrainingLevelID )) x
--------------
How do I do this ? PLease guide .... I failed in stuff function... PLease help again dear
Pankaj Kumar Choudhary10y ago
Hello! Riddhi read this article for XML Path
http://blog.sqlauthority.com/2013/04/05/sql-server-group-by-rows-and-columns-using-xml-path-efficient-concating-trick/
Riddhi Valecha10y ago
Well..How to do that ?? I did not get you !!

Pankaj Kumar Choudhary10y ago

Hello! Riddhi for below result

Subject_Name	SubjectID	Other_Subject	OtherSubjectID	TotalReports	Level Wise Report
Oracle 12c	11	null	null	5	Beginner-2; Intermediate -3
Adobe	14	null	null	3	Intermediate – 2; Expert-1
NULL	NULL	LINQ	70	4	Beginner – 2; Expert-2;
NULL	NULL	Silverlight	40	1	Intermediate - 1

you can union the result of both query that you made and after union use the FOR XML PATH and STUFF method to make desire result............

Riddhi Valecha10y ago

Hi...Thanks a lot Pankaj.... This is right result...

One more help required..... For Total Number of Subjects -I tried making the query and gone half-way through -

Query –

select a.Subject_Name, a.SID,a.Other_Subject,a.OID,a.Total_Reports from (selecttt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',null as 'OID' , null as 'Other_Subject',count(tt.SubjectID)/3 as 'Total_Reports'from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn where tt.SubjectID = tn.ID and tt.Flag =1 group by tt.SubjectID unionselectnull as 'SID', null as 'Subject_Name',tt.OtherSubjectID as 'OID', (select o.SubjectName from TBL_OTHERSUBJECTS o where o.Flag=1 and o.ID = tt.OtherSubjectID) as 'Other_Subject', count(tt.OtherSubjectID)/3 as 'Total_Reports'from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_OTHERSUBJECTS os where tt.OtherSubjectID = os.ID and tt.Flag =1 group by tt.OtherSubjectID )a

Result –

Subject_Name	SID	Other_Subject	OID	Total_Reports
Oracle 12c	11	null	null	5
Adobe	14	null	null	3
Null	null	LINQ	70	1
null	Null	Silverlight	40	4

Query –

select a.SID,a.Subject_Name,a.Other_Subject,a.OID,a.Total_Reports,a.Levelfrom (select tt.SubjectID as 'SID', (select sm.Subject from TBL_SUBJECTMASTER sm where sm.Flag = 1 and sm.ID = tt.SubjectID) as 'Subject_Name',null as 'OID' , null as 'Other_Subject',count(tt.SubjectID) as 'Total_Reports',count(distinct (case when tl.ID = 1 then 1 else 0 end)) as 'Beginner',count(distinct (case when tl.ID = 2 then 1 else 0 end)) as 'Intermediate',count(distinct (case when tl.ID = 3 then 1 else 0 end)) as 'Expert',(select ll.ILevel from TBL_LEVELMASTER ll where ll.Flag = 1 and ll.ID = tt.TrainingLevelID) as 'Level'from TBL_TRANSACTION tt , TBL_LEVELMASTER tl, TBL_SUBJECTMASTER tn where tt.SubjectID = tn.ID and tt.Flag =1 and tt.TrainingLevelID = tl.IDgroup by tt.SubjectID,tl.ID , tt.TrainingLevelID ) agroup by a.SID,a.Subject_Name,a.Other_Subject,a.OID,a.Total_Reports,a.Level

Subject_Name	SID	Other_Subject	OID	Total_Reports	Level
Oracle 12c	11	null	null	2	Beginner
Oracle 12c	11	null	null	3	Intermedidate
Adobe	14	null	null	2	Intermediate
Adobe	14	null	null	1	Expert

How do I get the result as –

Please help me out once again naa....

Subject_Name	SubjectID	Other_Subject	OtherSubjectID	TotalReports	Level Wise Report
Oracle 12c	11	null	null	5	Beginner-2; Intermediate -3
Adobe	14	null	null	3	Intermediate – 2; Expert-1
NULL	NULL	LINQ	70	4	Beginner – 2; Expert-2;
NULL	NULL	Silverlight	40	1	Intermediate - 1

Pankaj Kumar Choudhary10y ago
Hello @Riddhi i did some work on your query check out this
I used this Transaction tables

I used following Employee Table:
I used this query
SELECT Tab2.EmployeeName AS Manager_Name,Tab2.Total_Employee_Report,Tab4.Employee_For_Traning,
(Tab2.Total_Employee_Report-Tab4.Employee_For_Traning) AS Total_Employee_Not_Send_For_Traning,
CAST(Tab4.Employee_For_Traning*100/Tab2.Total_Employee_Report AS [nvarchar](max))+'%' AS '% sent Employees',
CAST((Tab2.Total_Employee_Report-Tab4.Employee_For_Traning)*100/Tab2.Total_Employee_Report AS [nvarchar](max))+'%' AS '% Employees Not Send'
FROM
(SELECT Tab.Manager_Id, Tab.EmployeeName ,Count(Tab.Manager_Id) AS Total_Employee_Report From
(SELECT e.Emp_Id AS Manager_Id,e.EmployeeName FROM dbo.Employee e, dbo.Employee e2
WHERE e.EmployeeName=e2.ManagerName)Tab
GROUP BY Tab.EmployeeName,Tab.Manager_Id)Tab2
OUTER APPLY
(SELECT * FROM (SELECT tt.ManagerID, COUNT(DISTINCT(tt.EmployeeID)) as Employee_For_Traning FROM dbo.TBL_TRANSACTION tt
GROUP BY tt.ManagerID)Tab3
WHERE Tab2.Manager_Id=Tab3.ManagerID)Tab4;

And Found Following Result:

This result is correct according my table. Please check this and tell me if you have any doubt or above result is not as you expect. I will try and sorry for late response because i go to college and after college i go my friend's company for work. So due to shortage of time , response get time.
But don't worry i will try help you till you doesn't get a correct result...

Riddhi Valecha10y ago

Hi..

Maruti.......

I again need your help...

Thank you so much.... Issue was with my data.... There is no subject in "OtherSubjectID" column in transaction table...

Thanks a ton... This query worked.......

Other Query Output -

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
8	3	2	1	2	1	0	Q1	df	1
9	6	4	1	1	4	0	Q2	df	1
10	8	7	3	1	4	0	Q3	dsf	1

TrainingLEvelID – 1 – Beginner ; 2 –Intermediate ; 3 –Expert

TrainingID – 1- Oracle12c ; 4-Adobe

Here, 4 – Adobe is occouring 3 times for level Beginner, and 1 time for Intermediate level.

So, in output must be – Total Reports -4; Beginner-3; Intermediate-1

Now ,I am getting TotalReport-2; Beginner-1;Intermediate-1

Hi... I tried making the query, but did not get correct result...

Hi... PLease help me out again in making this output ....

Manager Name	Department	Total Reports (Total Employess reporting to Manager)	Total Employees manager sent for training	Total Employees manager not sent for training	% sent Employees	%Not send
Pooja.Shah	IT - Database	100	30	70	30%	70%
Amit.Sharma	IT - Development	100	40	60	60%	40%

Riddhi Valecha10y ago

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
8	3	2	1	2	1	0	Q1	df	1
9	6	4	1	1	4	0	Q2	df	1
10	8	7	3	1	4	0	Q3	dsf	1

Maruthi Palllamalli10y ago
I posted query with my friend help, Find result and
Let me know if any modification.

Riddhi Valecha10y ago

To all.....I do know the concepts of primary key and foreign keys...

But, I do lack knowledge in Pl/SQL (Loops , cursors, etc) - (SQL and Oracle)

I am not able to make this long query, hence I posted this issue.

How to get this output ??

If I get atleast one, I can try the other also...

Please Guide...

Please Help me out I need Help...

I am still struggling...

Subject	Subject Count	Other Subject	Other Subject Count	Levelwise count
Oracle	100	null	0	Beginner - 30 Intermediate - 30 Expert - 40 (30+30+40 = 100)
Adobe	50	null	0	begin - 50 inter - 0 expert - 0
Null	0	Query String	70	begin - 20 inte- 50 expert - 0
NULL	0	HTML	80	begin - 80 int - 0 exp -0

I am struggling in making this format......

Please help !

Other Output that I need is -

When user clicks on 100;50; or any other number greater than 0; then in other webpage;the following report must be displayed -

Subject - Oracle 12C

usermaster table usermaster table

Manager Name	Employee Name	Employee Email	Subject Level	Remakrs
Pooja	Anisha	usermaster table	Beginner	dsf
Kapil	Shubhra	Expert	df
Aradhna	Sachin	Intermediate	dsf

Also, along with it - I need the following count -

Manager Name	Total Employees Reporting to him	Total Employees send for training	Total Employees not send for any training	Percent of send employees
Pooja	10	9	1	90%
Kapil	100	60	40	60%

Hi... Please guys... Help me out fast ....

Mahesh Kale10y ago
Two things to be done before solving this. Make sure you know how to use foreign keys. And second make sure you know how to use joins. As the resulting table will be made by making use of joins. And you need to know how to join the data from two or more tables to create another new table.
Praveen Dhatrika10y ago
Hi Riddhi,

Please refer below articles and videos, that might better help you understand the basic understanding of SQL Server and Query Management

http://www.w3schools.com/sql/
https://www.udemy.com/sql-server-for-beginners/
https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB (better for beginner)
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html (article for above)

Hope it helps you, Good Luck :)