Sql Query to Drill down to a table to minimum level as per RoleID and Hierarchy Structure

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

We have a application where users are entering their time efforts .We have two tables, Production table for entering all production tasks and NonProduction for entering all non production related tasks in the application.We also have a UserInfo table where all the user information is stored like his eCode, Name, Designation,RoleID,ImmediateSupervisor ECode to whom the user is reporting etc. We are trying to make a sql query in such manner so that any users whose roleid is greater then 1 can drill down and extract the Production and NonProduction information of the users reporting under him but the condition is that the extracted report is of RoleID 1 only. For example, A Manager RoleID is 4 ,so firstly he drill down and find all the users reporting to him (This info is in ImmediateSupervisor column on UserInfo table)i.e RoleID 3 or RoleID 4, then again we drill down to these Users and extract information who are reporting to them and continue until we drill down up to ROleID 1 and once we drill down to that level, we extract respective users Production and NonProduction details and display in the Report.

Below is the table structure query in Create Table format;-
Production:-

SQL

CREATE TABLE [dbo].[Production](
	[ProductionTimeEntryID] [int] IDENTITY(100,1) NOT NULL,
	[CalendarDate] [datetime] NOT NULL,
	[UserID] [int] NOT NULL,
	[NatureOfWorkID] [int] NOT NULL,
	[RegionProjectID] [int] NOT NULL,
	[CountyID] [int] NOT NULL,
	[WorkTypeID] [int] NOT NULL,
	[TaskID] [int] NOT NULL,
	[VolumeProcessed] [int] NOT NULL,
	[NosOfError] [int] NULL,
	[NosOfVolumeAudited] [int] NULL,
	[TimeSpent] [varchar](25) NULL,
	[Comment] [varchar](250) NULL,
	[IsTaskCompleted] [int] NOT NULL,
	[isCurrentDayTask] [bit] NOT NULL,
	[SupervisorECode] [nvarchar](50) NULL,
	CONSTRAINT [PK_Production] PRIMARY KEY CLUSTERED 
	(
	[ProductionTimeEntryID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	SET ANSI_PADDING OFF
	GO
	ALTER TABLE [dbo].[Production]  WITH NOCHECK ADD  CONSTRAINT [FK_Production_County] FOREIGN KEY([CountyID])
	REFERENCES [dbo].[County] ([CountyID])
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_County]
	GO
	ALTER TABLE [dbo].[Production]  WITH NOCHECK ADD  CONSTRAINT [FK_Production_NatureOfWork] FOREIGN KEY([NatureOfWorkID])
	REFERENCES [dbo].[NatureOfWork] ([NatureOfWorkID])
	ON UPDATE CASCADE
	ON DELETE CASCADE
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_NatureOfWork]
	GO
	ALTER TABLE [dbo].[Production]  WITH NOCHECK ADD  CONSTRAINT [FK_Production_RegionAndProjectInfo] FOREIGN KEY([RegionProjectID])
	REFERENCES [dbo].[RegionAndProjectInfo] ([RegionProjectID])
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_RegionAndProjectInfo]
	GO
	ALTER TABLE [dbo].[Production]  WITH CHECK ADD  CONSTRAINT [FK_Production_Task] FOREIGN KEY([TaskID])
	REFERENCES [dbo].[Task] ([TaskID])
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_Task]
	GO
	ALTER TABLE [dbo].[Production]  WITH CHECK ADD  CONSTRAINT [FK_Production_USERINFO] FOREIGN KEY([UserID])
	REFERENCES [dbo].[USERINFO] ([UserID])
	ON UPDATE CASCADE
	ON DELETE CASCADE
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_USERINFO]
	GO
	ALTER TABLE [dbo].[Production]  WITH CHECK ADD  CONSTRAINT [FK_Production_WorkType] FOREIGN KEY([WorkTypeID])
	REFERENCES [dbo].[WorkType] ([WorkTypeID])
	ON UPDATE CASCADE
	ON DELETE CASCADE
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_WorkType]
	GO
	ALTER TABLE [dbo].[Production] ADD  DEFAULT ((0)) FOR [IsTaskCompleted]
	GO
	ALTER TABLE [dbo].[Production] ADD  DEFAULT ((0)) FOR [isCurrentDayTask]
	GO

UserInfo Table:-

SQL

CREATE TABLE [dbo].[USERINFO](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserECode] [nvarchar](50) NOT NULL,
	[UserName] [nvarchar](250) NOT NULL,
	[CCCode] [nvarchar](50) NULL,
	[CCName] [nvarchar](50) NULL,
	[Password] [varchar](50) NULL,
	[IsFlagEnabled] [bit] NULL,
	[IsFirstTimeUserLoggedIn] [bit] NULL,
	[EmailAddress] [nvarchar](250) NULL,
	[Designation] [varchar](50) NULL,
	[ShiftStartTime] [varchar](8) NULL,
	[ShiftEndTime] [varchar](8) NULL,
	[WeekendShiftStartTime] [varchar](8) NULL,
	[WeekendShiftEndTime] [varchar](8) NULL,
	[RoleID] [int] NULL,
	[ShiftEndFlagStatus] [int] NOT NULL,
	[ShiftStartTimeWithTimeStamp] [datetime] NULL,
	[ShiftEndTimeWithTimeStamp] [datetime] NULL,
	[LoggedInDateTime] [datetime] NULL,
	[FirstLoggedInOnthedayflag] [int] NOT NULL,
	[ImmediateSupervisor] [nvarchar](50) NULL,
	 CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED 
	(
		[UserID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	SET ANSI_PADDING OFF
	GO
	ALTER TABLE [dbo].[USERINFO] ADD  DEFAULT ((1)) FOR [RoleID]
	GO
	ALTER TABLE [dbo].[USERINFO] ADD  DEFAULT ((1)) FOR [ShiftEndFlagStatus]
	GO
	ALTER TABLE [dbo].[USERINFO] ADD  DEFAULT ((1)) FOR [FirstLoggedInOnthedayflag]
	GO

Please help me to make such query or if any one had created such type of query, please share so that i also try to incorporate same in my application. Thanks in Advanse

Posted 16-Jan-13 22:11pm

Dharmenrda Kumar Singh

Add a Solution

Comments

CHill60 17-Jan-13 7:34am

Try this google search ... http://www.lmgtfy.com/?q=sql+recursive+query - I think this is what you're after, but I haven't posted it as a solution just in case

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dharmenrda Kumar Singh · Accepted Answer · 2013-01-17T22:15:00

SQL

with
	ProductionCTE(CalendarDate,RoleID,UserID,UserECode,UserName,ImmediateSupervisor,NatureOfWorkName,RegionProjectName,CountyName,WorkTypeName,TaskName,VolumneProcessed,TimeSpent,Comment)
	as
	(
	select P.CalendarDate,U.RoleID,U.UserID,U.UserECode,U.UserName,U.ImmediateSupervisor,N.NatureofWorkName,
	R.RegionProjectName,C.Countyname,W.WorktypeName,T.TaskName,P.VolumeProcessed,P.Timespent,P.Comment
	from production P inner join NatureOfWork N
	on N.NatureofWorkID=P.natureofworkid
	inner join dbo.RegionAndProjectInfo R
	on R.RegionProjectID=P.RegionProjectID
	inner join county C
	on C.countyid=P.countyid
	inner join worktype W
	on W.Worktypeid=P.worktypeID
	inner join task T
	on T.taskid=P.TaskID
	inner join UserInfo U
	on U.Userid=P.userid
	where P.userid=952
		
		union all
 
			select P.CalendarDate,U.RoleID,U.UserID,U.UserECode,U.UserName,U.ImmediateSupervisor,N.NatureofWorkName,
			R.RegionProjectName,C.Countyname,W.WorktypeName,T.TaskName,P.VolumeProcessed,P.Timespent,P.Comment
			from production P inner join NatureOfWork N
			on N.NatureofWorkID=P.natureofworkid
			inner join dbo.RegionAndProjectInfo R
			on R.RegionProjectID=P.RegionProjectID
			inner join county C
			on C.countyid=P.countyid
			inner join worktype W
			on W.Worktypeid=P.worktypeID
			inner join task T
			on T.taskid=P.TaskID
			inner join UserInfo U
			on U.Userid=P.userid
			inner join ProductionCTE
			on U.ImmediateSupervisor=ProductionCTE.UserECode
	)
    select distinct CalendarDate,RoleID,UserID,UserECode,UserName,ImmediateSupervisor,NatureOfWorkName,RegionProjectName,CountyName,WorkTypeName,TaskName,VolumneProcessed,TimeSpent,Comment from ProductionCTE order by UserECode

Using Recursive CTE , the issue is fixed.