April 7, 2011 at 2:52 am
I have a database that contains Projects in tblProjects and the meetings scheduled for each Project in tblMeetings. The table structures are:
CREATE TABLE [dbo].[tblMeeting](
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingName] [varchar](255) NOT NULL,
[ProjectID] [int] NOT NULL,
[dtMeetingDate] [smalldatetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](100) NOT NULL
) ON [PRIMARY]
So, a list of Projects in tblProjects and a list of the meetings for each project in tblMeetings with the ProjectID as a foreign key in tblMeetings.
What I need to show on screen is a sort of calendar of meetings for any particular month.
So, in the front end, I need to show a sort of calendar (a table really) where the left column shows the date (all the days in any particular month) consecutively in each row and each column contains the Project and, at the intersection of the date and Project - show the name of the meeting.
I need the front end to look a bit like this:
................................Project 1...................Project 2...................Project 3
01/04/2011................................................Scope Meeting.......................
02/04/2011...........................................................................................
03/04/2011.................Development.........................................................
04/04/2011...........................................................................................
05/04/2011...............................................................................Review...
etc.
I need a stored procedure that I can pass a date to as a parameter which will return the data above i.e. a row for each day in that month, a column for each project and, if there is a meeting on that day for that Project, the name of the meeting.
When I first thought about it I thought it would be easy. Now I have to do it I realise I don't know how to do it.
Any help much appreciated.
April 7, 2011 at 3:11 am
2-3 years back I faced the same issue like Doctor and his available consultation slots. Table structure was same. What I did, joined both table and brought 2 columns DoctorName, AvaliableSlots. Later used PIVOT and showed the doctor in Rows and his slots as Columns. This is just to let you know the logic what I used that time. I don’t remember the code.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 7, 2011 at 3:15 am
CREATE TABLE [dbo].[tblMeeting](
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingName] [varchar](255) NOT NULL,
[ProjectID] [int] NOT NULL,
[dtMeetingDate] [smalldatetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](100) NOT NULL
) ON [PRIMARY]
create proc tblProjectsschedule
(
@date datetime
)
as
begin
---create a string that will pick all the names from table [tblProject] and create one temp table
declare @tempTable varchar(mAX), @column1 nvarchar(100),@column2 nvarchar(100)
set @tempTable =' Create table ##result ( Date datetime '
declare csr cursor for
Select [ProjectName],cast([ProjectID]as varchar(100)) from [tblProject]
open csr
fetch next from csr into @column1,@column2
while @@FETCH_STATUS =0
begin
set @tempTable = ', ' + @tempTable+ @column1+'_'+ @column2+' nvarchar(100)'
fetch next from csr into @column1,@column2
end
set @tempTable=@tempTable+' )'
-- now ##result table will hold structure like
-- create table ##result (
---Date datetime ,
---[Project_ProjectID] varchar(100),....
end
--Now move data to temp table by writting a function which return all the the dates and insert data in this ##result table and Insert/Update data accordingly
April 7, 2011 at 4:02 am
ranjodh_makkar (4/7/2011)
CREATE TABLE [dbo].[tblMeeting]([MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingName] [varchar](255) NOT NULL,
[ProjectID] [int] NOT NULL,
[dtMeetingDate] [smalldatetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](100) NOT NULL
) ON [PRIMARY]
create proc tblProjectsschedule
(
@date datetime
)
as
begin
---create a string that will pick all the names from table [tblProject] and create one temp table
declare @tempTable varchar(mAX), @column1 nvarchar(100),@column2 nvarchar(100)
set @tempTable =' Create table ##result ( Date datetime '
declare csr cursor for
Select [ProjectName],cast([ProjectID]as varchar(100)) from [tblProject]
open csr
fetch next from csr into @column1,@column2
while @@FETCH_STATUS =0
begin
set @tempTable = ', ' + @tempTable+ @column1+'_'+ @column2+' nvarchar(100)'
fetch next from csr into @column1,@column2
end
set @tempTable=@tempTable+' )'
-- now ##result table will hold structure like
-- create table ##result (
---Date datetime ,
---[Project_ProjectID] varchar(100),....
end
--Now move data to temp table by writting a function which return all the the dates and insert data in this ##result table and Insert/Update data accordingly
Thanks for your reply. I tried to run it but it reports a syntax error.
Incorrect syntax near ' )'
The error occurs on the line
set @tempTable=@tempTable+' )'
April 7, 2011 at 4:45 am
--Below is complete solution which will compile and run and have logic to --create dynamic result table. Now all you need to do is to write a function --that will return dates and insert into this ##result table and insert/update --accordingly.
CREATE TABLE [dbo].[tblMeeting](
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingName] [varchar](255) NOT NULL,
[ProjectID] [int] NOT NULL,
[dtMeetingDate] [smalldatetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](100) NOT NULL
) ON [PRIMARY]
insert into tblProject values('Project1')
insert into tblProject values('Project2')
insert into tblProject values('Project3')
GO
ALTER proc tblProjectsschedule
(
@date datetime =null
)
as
begin
---create a string that will pick all the names from table [tblProject] and create one temp table
declare @tempTable varchar(mAX), @column1 nvarchar(100),@column2 nvarchar(100)
set @tempTable =' Create table ##result ( Date datetime '
declare csr cursor for
Select [ProjectName],cast([ProjectID]as varchar(100)) from [tblProject]
open csr
fetch next from csr into @column1,@column2
while @@FETCH_STATUS =0
begin
set @tempTable = @tempTable+ ', ' + @column1+'_'+ @column2+' nvarchar(100)'
fetch next from csr into @column1,@column2
end
close csr
deallocate csr
set @tempTable=@tempTable+' )'
-- now ##result table will hold structure like
-- create table ##result (
--- Date datetime ,
---[Project_ProjectID] varchar(100),....
exec( @tempTable)
select * from ##result
drop table ##result
end
--Now move data to temp table by writting a function which return all the the dates and insert data in this ##result table and Insert/Update data accordingly
GO
Exec tblProjectsschedule
April 7, 2011 at 5:49 am
ranjodh_makkar (4/7/2011)
---create a string that will pick all the names from table [tblProject] and create one temp table
declare @tempTable varchar(mAX), @column1 nvarchar(100),@column2 nvarchar(100)
set @tempTable =' Create table ##result ( Date datetime '
declare csr cursor for
Select [ProjectName],cast([ProjectID]as varchar(100)) from [tblProject]
open csr
fetch next from csr into @column1,@column2
while @@FETCH_STATUS =0
begin
set @tempTable = @tempTable+ ', ' + @column1+'_'+ @column2+' nvarchar(100)'
fetch next from csr into @column1,@column2
end
close csr
deallocate csr
set @tempTable=@tempTable+' )'
print @tempTable
--exec( @tempTable)
end
In my database, if I run the code above it prints this:
Create table ##result ( Date datetime , Project A_1 nvarchar(100), Project B_2 nvarchar(100), Project C_3 nvarchar(100), Project D_4 nvarchar(100) )
If I run it and uncomment the line
exec(@tempTable)
it says 'Incorrect syntax near 'nvarchar'
Yet that Create table statement looks okay to me - but it won't run.
Thanks again.
April 7, 2011 at 5:52 am
sir - try my new post which is
--Below is complete solution which will compile and run and have logic to --create dynamic result table. Now all you need to do is to write a function --that will return dates and insert into this ##result table and insert/update --accordingly.
CREATE TABLE [dbo].[tblMeeting](
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingName] [varchar](255) NOT NULL,
[ProjectID] [int] NOT NULL,
[dtMeetingDate] [smalldatetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](100) NOT NULL
) ON [PRIMARY]
insert into tblProject values('Project1')
insert into tblProject values('Project2')
insert into tblProject values('Project3')
GO
ALTER proc tblProjectsschedule
(
@date datetime =null
)
as
begin
---create a string that will pick all the names from table [tblProject] and create one temp table
declare @tempTable varchar(mAX), @column1 nvarchar(100),@column2 nvarchar(100)
set @tempTable =' Create table ##result ( Date datetime '
declare csr cursor for
Select [ProjectName],cast([ProjectID]as varchar(100)) from [tblProject]
open csr
fetch next from csr into @column1,@column2
while @@FETCH_STATUS =0
begin
set @tempTable = @tempTable+ ', ' + @column1+'_'+ @column2+' nvarchar(100)'
fetch next from csr into @column1,@column2
end
close csr
deallocate csr
set @tempTable=@tempTable+' )'
-- now ##result table will hold structure like
-- create table ##result (
--- Date datetime ,
---[Project_ProjectID] varchar(100),....
exec( @tempTable)
select * from ##result
drop table ##result
end
--Now move data to temp table by writting a function which return all the the dates and insert data in this ##result table and Insert/Update data accordingly
GO
Exec tblProjectsschedule
April 7, 2011 at 7:04 am
Hello
Read this article [/url]by Jeff Moden to understand how the following code works.
-- make some sample data
SET DATEFORMAT DMY
DROP TABLE #tblMeeting
CREATE TABLE #tblMeeting(
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingName] [varchar](255) NOT NULL,
[ProjectID] [int] NOT NULL,
[dtMeetingDate] [smalldatetime] NULL)
INSERT INTO #tblMeeting (MeetingName, ProjectID, dtMeetingDate)
SELECT 'Scope Meeting', 2, '01/04/2011' UNION ALL
SELECT 'Development', 1, '03/04/2011' UNION ALL
SELECT 'Review', 3, '05/04/2011'
DROP TABLE #tblProject
CREATE TABLE #tblProject(
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](100) NOT NULL)
INSERT INTO #tblProject (ProjectName)
SELECT 'Project 1' UNION ALL
SELECT 'Project 2' UNION ALL
SELECT 'Project 3'
----------------------------------------------------------------------------------------------------------
-- start of the solution
DECLARE @StartDate DATE
SET @StartDate = GETDATE()
-- make a date range (check forum for currently-fashionable method for creating a tally table on the fly)
DROP TABLE #DateRange
SELECT Date = DATEADD(dd,n.n,DATEADD(mm,DATEDIFF(mm,0,@StartDate),0))
INTO #DateRange
FROM (
SELECT TOP 31 n = -1 + ROW_NUMBER() OVER (ORDER BY [Name])
FROM master.dbo.syscolumns
) n
WHERE MONTH(DATEADD(dd,n.n,DATEADD(mm,DATEDIFF(mm,0,@StartDate),0))) = MONTH(@StartDate)
-- run this to see how the results are to look
SELECT d.Date,
[Project 1] = CASE WHEN p.ProjectName = 'Project 1' THEN m.MeetingName ELSE SPACE(15) END,
[Project 2] = CASE WHEN p.ProjectName = 'Project 2' THEN m.MeetingName ELSE SPACE(15) END,
[Project 3] = CASE WHEN p.ProjectName = 'Project 3' THEN m.MeetingName ELSE SPACE(15) END
FROM #DateRange d
LEFT JOIN #tblMeeting m ON m.dtMeetingDate = d.Date
LEFT JOIN #tblProject p ON p.ProjectID = m.ProjectID
-- create a string with the same structure as the above query - do this bit by bit
DECLARE @SQLstring1 VARCHAR(MAX), @SQLstring2 VARCHAR(MAX), @SQLstring3 VARCHAR(MAX)
SET @SQLstring1 = 'SELECT d.Date,'
SET @SQLstring3 = '
FROM #DateRange d
LEFT JOIN #tblMeeting m ON m.dtMeetingDate = d.Date
LEFT JOIN #tblProject p ON p.ProjectID = m.ProjectID'
-- pick up names of projects active within date range
SELECT @SQLstring2 = ISNULL(@SQLstring2,'') + '[' + p.ProjectName + '] = CASE WHEN p.ProjectName = ''' + p.ProjectName + ''' THEN m.MeetingName ELSE SPACE(15) END, '
FROM #DateRange d
INNER JOIN #tblMeeting m ON m.dtMeetingDate = d.Date
INNER JOIN #tblProject p ON p.ProjectID = m.ProjectID
ORDER BY p.ProjectID
-- snip the final comma from the string
SET @SQLstring2 = REPLACE(@SQLstring2+'#',', #','')
-- always test!!
PRINT @SQLstring1 + @SQLstring2 + @SQLstring3
EXEC (@SQLstring1 + @SQLstring2 + @SQLstring3)
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2011 at 7:11 am
To ranjodh_makkar;
I have fixed the error in my code (needed square brackets around the column names] and got your code working. Thanks for your help.
April 7, 2011 at 7:27 am
To Chris M,
Well, not only does that do exactly what I need, I pretty much understand it too!
Thanks very much. I need to able to drill down in the front end to show meeting agendas - I figure I can just concatenate the MeetingID onto the MeetingName with a separating character ... 'Scope Meeting|123' and get at the '123' to retrieve the MeetingID to drill down.
Thanks again.
April 7, 2011 at 8:06 am
You're welcome - and you have the benefit of a fully-documented method 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2011 at 8:52 am
Chris Morris-439714 (4/7/2011)
You're welcome - and you have the benefit of a fully-documented method 🙂
... and no RBAR. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2011 at 11:23 pm
You're welcome.
April 7, 2011 at 11:27 pm
you're welcome.
also if you want to do it without cursor, try this simpler one
______________________________
CREATE TABLE [dbo].[tblMeeting](
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingName] [varchar](255) NOT NULL,
[ProjectID] [int] NOT NULL,
[dtMeetingDate] [smalldatetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](100) NOT NULL
) ON [PRIMARY]
insert into tblProject values('Project1')
insert into tblProject values('Project2')
insert into tblProject values('Project3')
GO
alter proc tblProjectsschedule
(
@date datetime =null
)
as
begin
---create a string that will pick all the names from table [tblProject] and create one temp table
declare @tempTable varchar(mAX), @column1 nvarchar(100),@column2 nvarchar(100)
set @tempTable =' Create table ##result ( Date datetime '
Select @tempTable=@tempTable+ ',[' + [ProjectName] +'_'+ cast([ProjectID]as varchar(100)) +'] varchar(100)'
from [tblProject]
set @tempTable=@tempTable+' )'
--declare csr cursor for
--Select [ProjectName],cast([ProjectID]as varchar(100)) from [tblProject]
--open csr
--fetch next from csr into @column1,@column2
--while @@FETCH_STATUS =0
--begin
--set @tempTable = @tempTable+ ', ' + @column1+'_'+ @column2+' nvarchar(100)'
--fetch next from csr into @column1,@column2
--end
--close csr
--deallocate csr
--set @tempTable=@tempTable+' )'
-- now ##result table will hold structure like
-- create table ##result (
--- Date datetime ,
---[Project_ProjectID] varchar(100),....
exec( @tempTable)
select * from ##result
drop table ##result
end
--Now move data to temp table by writting a function which return all the the dates and insert data in this ##result table and Insert/Update data accordingly
GO
Exec tblProjectsschedule
April 9, 2011 at 1:04 am
ranjodh_makkar (4/7/2011)
--Now move data to temp table by writting a function which return all the the dates and insert data in this ##result table and Insert/Update data accordingly
I don't recommend using a Global Temp table because it usually prevents concurrent runs one way or another.
Also, the "function" you speak of is a critical part of your code. Any chance of you providing it? Remember, it's easy to get to the Moon... all you have to do is build a rocket ship. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply