Click here to Skip to main content
15,030,320 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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

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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900