Cross tab problems - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Cross tab problems

Author

Topic

maqster
Starting Member

5 Posts

Posted - 2002-12-17 : 09:17:09

I have a problem with a query:

Here are my tables:

CREATE TABLE [Project] (
[ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
[Import_ProjID] [int] NULL ,
[ActionID] [int] NOT NULL ,
[ClassID] [int] NOT NULL ,
[CostNoID] [int] NOT NULL ,
[ProjectManager] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[BuildingResponsible] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[BuildingMobileNo] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[OnSiteResponsible] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OnSiteMobileNo] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[ContractArea] [int] NULL ,
[CreatedDate] [datetime] NOT NULL ,
[CreatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[LastUpdatedDate] [datetime] NULL ,
[LastUpdatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Project_Action] FOREIGN KEY
(
[ActionID]
) REFERENCES [Action] (
[ActionID]
),
CONSTRAINT [FK_Project_Class] FOREIGN KEY
(
[ClassID]
) REFERENCES [Class] (
[ClassID]
),
CONSTRAINT [FK_Project_CostNo] FOREIGN KEY
(
[CostNoID]
) REFERENCES [CostNo] (
[CostNoID]
)
) ON [PRIMARY]
GO

CREATE TABLE [Timeplan] (
[TimeplanID] [int] IDENTITY (1, 1) NOT NULL ,
[ProjectID] [int] NOT NULL ,
[TimeplanTitleID] [int] NOT NULL ,
[YearID] [int] NOT NULL ,
[WeekID] [int] NOT NULL ,
[OldYear] [int] NULL ,
[OldWeek] [int] NULL ,
[CreatedDate] [datetime] NOT NULL ,
[CreatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[LastUpdatedDate] [datetime] NULL ,
[LastUpdatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ApprovedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ApprovedDate] [datetime] NULL ,
CONSTRAINT [PK_Timeplan] PRIMARY KEY CLUSTERED
(
[TimeplanID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Timeplan_Project] FOREIGN KEY
(
[ProjectID]
) REFERENCES [Project] (
[ProjectID]
),
CONSTRAINT [FK_Timeplan_TimeplanTitle] FOREIGN KEY
(
[TimeplanTitleID]
) REFERENCES [TimeplanTitle] (
[TimeplanTitleID]
),
CONSTRAINT [FK_Timeplan_Week] FOREIGN KEY
(
[WeekID]
) REFERENCES [Week] (
[WeekID]
),
CONSTRAINT [FK_Timeplan_Year] FOREIGN KEY
(
[YearID]
) REFERENCES [Year] (
[YearID]
)
) ON [PRIMARY]
GO

CREATE TABLE [TimeplanTitle] (
[TimeplanTitleID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeplanTitleTypeID] [int] NOT NULL ,
[TimeplanTitle] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ShortName] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[DisplayOrder] [int] NOT NULL ,
[Disabled] [int] NULL ,
[CreatedDate] [datetime] NOT NULL ,
[CreatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[LastUpdatedDate] [datetime] NULL ,
[LastUpdatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_TimeplanTitle] PRIMARY KEY CLUSTERED
(
[TimeplanTitleID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_TimeplanTitle_TimeplanTitleType] FOREIGN KEY
(
[TimeplanTitleTypeID]
) REFERENCES [TimeplanTitleType] (
[TimeplanTitleTypeID]
)
) ON [PRIMARY]
GO

CREATE TABLE [TimeplanTitleType] (
[TimeplanTitleTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeplanTitleType] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[DisplayOrder] [int] NOT NULL ,
[Disabled] [int] NULL ,
[CreatedDate] [datetime] NOT NULL ,
[CreatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[LastUpdatedDate] [datetime] NULL ,
[LastUpdatedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_TimeplanTitleType] PRIMARY KEY CLUSTERED
(
[TimeplanTitleTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Week] (
[WeekID] [int] IDENTITY (1, 1) NOT NULL ,
[Week] [int] NOT NULL ,
CONSTRAINT [PK_Week] PRIMARY KEY CLUSTERED
(
[WeekID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Year] (
[YearID] [int] IDENTITY (1, 1) NOT NULL ,
[Year] [int] NOT NULL ,
CONSTRAINT [PK_Year] PRIMARY KEY CLUSTERED
(
[YearID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

This is the SQL I'm trying to use, I know it doesn't work for my topic.

SELECT Timeplan.TimeplanID, TimeplanTitle.TimeplanTitle, [Year].[Year], [Week].[Week], Timeplan.OldYear, Timeplan.OldWeek
FROM Project INNER JOIN
Timeplan ON Project.ProjectID = Timeplan.ProjectID INNER JOIN
TimeplanTitle ON Timeplan.TimeplanTitleID = TimeplanTitle.TimeplanTitleID INNER JOIN
[Year] ON Timeplan.YearID = [Year].YearID INNER JOIN
[Week] ON Timeplan.WeekID = [Week].WeekID
WHERE Timeplan.ProjectID = @ProjectID
ORDER BY TimeplanTitle.DisplayOrder

I'm trying to get the values for a Timeplan, displaying all TimeplanTitles and the values Year and Week for each Title.
If there is no value for Year or Week it shall show the Title and NULL for Year and Week.

The problem is that if there is no value for the Title it doesn't exists in Timeplan.

The result should look something like this:

TimplanTitle Year Week
Headline1 2003 3
Headline2 NULL NULL
Headline3 2003 7
Headline4 NULL NULL
Headline5 2003 7
Headline6 2003 8

Can sombody please help me ?

Edited by - maqster on 12/17/2002 09:33:40

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-17 : 09:23:16

The image link points to your local hard drive, none of us can see it. You'll need to move that image to a web server and link using the URL of that server.

It would also help if you provided the structure of the table(s) you're using in your query (CREATE TABLE statements are preferred). It would also help if you provided some sample data and the output you want to get from it, and any SQL statements you're using now.

maqster
Starting Member

5 Posts

Posted - 2002-12-17 : 09:35:24

Sorry new to the forum I've tried to make the changes you asked for.

Hope this will help resolve my issue :)

maqster

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-17 : 09:46:24

See if this fixes it:

SELECT Timeplan.TimeplanID, TimeplanTitle.TimeplanTitle, [Year].[Year], [Week].[Week], Timeplan.OldYear, Timeplan.OldWeek
FROM Project INNER JOIN
Timeplan ON Project.ProjectID = Timeplan.ProjectID RIGHT JOIN
TimeplanTitle ON Timeplan.TimeplanTitleID = TimeplanTitle.TimeplanTitleID INNER JOIN
[Year] ON Timeplan.YearID = [Year].YearID INNER JOIN
[Week] ON Timeplan.WeekID = [Week].WeekID
WHERE Timeplan.ProjectID = @ProjectID
ORDER BY TimeplanTitle.DisplayOrder

If TimeplaneTitle contains all of the titles you're looking for, it should list them all.

maqster
Starting Member

5 Posts

Posted - 2002-12-18 : 03:33:54

I'm sorry but it still doesn't work, I only get one line like this:

TimeplanID TimeplanTitle Year Week
139 Opening 2004 38

Here is the data in the tables if it helps:

Timeplan
TimeplanID ProjectID TimeplanTitleID YearID WeekID
139 13 20 5 38

TimeplanTitle
TimeplanTitleID TimeplanTitle DisplayOrder
--------------- --------------------------------------- ------------
1 Drawings From Country 1
2 Basic drawings 2
3 Layout 3
4 Floor drawings 4
5 Ceiling drawing 5
6 Interior drawing 6
7 Flooring stone 7
8 Flooring wood 8
9 Flooring vinyl 9
10 Illumination downlights 10
11 Illumination spotlights 11
12 Illumination ercotracks 12
13 Wardrobes 13
14 Stockroom 14
15 Fittingroom 15
16 Floorstands 16
17 Cashpoints 17
18 Matching details 18
19 Decomaterial 19
20 Opening 20

Anybody got any idea ?

maqster

sanjnep
Posting Yak Master

191 Posts

Posted - 2002-12-18 : 04:42:48

Hello friend I think you should use MS access database for cross tabulation report than SQL server.It can be done easily in MS access.
Thank you for your question.
........................
Sanjeev

Sanjeevshrestha

maqster
Starting Member

5 Posts

Posted - 2002-12-18 : 05:07:31

I can't use MS ACCESS I'm stuck with MSSQL 2000, what to do ?

I'm not it's a cross tab I need, but I think it should be possible to get the result I need without having to insert rows in Timeplan with nulls.

maqster

maqster
Starting Member

5 Posts

Posted - 2002-12-19 : 09:46:52

Somebody please help me ?

maqster

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-19 : 11:38:58

Try:

SELECT
TimeplanTitle, A.*
FROM
TimeplanTitle
LEFT OUTER JOIN
(SELECT Timeplan.TimeplanID, [Year].[Year], [Week].[Week], Timeplan.OldYear, Timeplan.OldWeek
FROM Project
INNER JOIN
Timeplan ON Project.ProjectID = Timeplan.ProjectID
INNER JOIN
[Year] ON Timeplan.YearID = [Year].YearID
INNER JOIN
[Week] ON Timeplan.WeekID = [Week].WeekID
WHERE Timeplan.ProjectID = @ProjectID
) A
ON A.TimeplanTitleID = TimeplanTitle.TimeplanTitleID
ORDER BY TimeplanTitle.DisplayOrder

Is is basically the SQL you had, took timeplan title OUT of the SQL, encapsulated it, and then we do a LEFT OUTER JOIN from all of the titles to your data, sorting as needed.

Hope it works. Hopefully you get the idea.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources