SQL Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Statement

Author

Topic

shers
Starting Member

3 Posts

Posted - 2006-06-17 : 09:01:31

Hi all,

I have three tables from which I need to retrieve information.

This is the generated SQL Script.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Designation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Designation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Availability]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Availability]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StaffMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StaffMaster]
GO

CREATE TABLE [dbo].[Designation] (
	[Priority] [int] NULL ,
	[Designation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Discipline] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Availability] (
	[Empcode] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Discipline] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Month_Year] [smalldatetime] NOT NULL ,
	[Avail] [int] NOT NULL ,
	[AvailDate] [smalldatetime] NULL ,
	[Id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StaffMaster] (
	[EmpCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmpName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Designation] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Discipline] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

The SQL I am using right now is as follows:

"TRANSFORM Max(Availability.Avail) AS MaxOfAvail 
SELECT StaffMaster.EmpCode, StaffMaster.EmpName,   StaffMaster.Discipline, StaffMaster.Designation 
FROM StaffMaster 
LEFT JOIN Availability 
ON 
StaffMaster.EmpCode = Availability.Empcode 
WHERE (StaffMaster.Designation<>'Project Manager' 
AND StaffMaster.Designation 
NOT LIKE 'Head%' AND StaffMaster.Designation 
NOT LIKE '%Manager%') 
GROUP BY StaffMaster.Discipline, StaffMaster.EmpCode, StaffMaster.EmpName, StaffMaster.Designation 
ORDER BY StaffMaster.Discipline, StaffMaster.Designation, 
StaffMaster.EmpName PIVOT Availability.Month_Year"