totaling per location, per date.

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)

totaling per location, per date.

Author

Topic

molebrain
Starting Member

18 Posts

Posted - 2004-09-07 : 12:18:25

Ok, I have this inquiy table that looks like this:
locationID, name, inquiredate

I am trying to generate a report that looks like this:

Location 2004 2003
--------------- ----------- --------------
Atlanta 113 123
Berlin 36 24
Las Vegas 47 21

total 196 168

I want to be able to plug in a date range. So far I can only get this:

Location 2004
--------------- -----------
Atlanta 113
Berlin 36
Las Vegas 47

Any ideas? thanks!

-Tony

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 12:24:14

sound like a job for cross tabs:
http://www.sqlteam.com/item.asp?ItemID=2955

but you'll need to give more info like create table statements...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-07 : 12:24:20

re-read your question from a neutral point of view, pretend you don't know anything about your specific situation, and see if it makes sense exactly what you are asking and how can help you.

- Jeff

molebrain
Starting Member

18 Posts

Posted - 2004-09-07 : 15:13:57

That Pivot table procedure looks promising. But I'm dumb. I have two tables, the info gather table and a location lookup table:

CREATE TABLE [tblStudentForm] (
[StudentFormID] [int] IDENTITY (1, 1) NOT NULL ,
[StudentFormTimeStamp] [datetime] NULL ,
[StudentLocationID] [int] NULL ,
CONSTRAINT [PK_tblStudentForm] PRIMARY KEY NONCLUSTERED
(
[StudentFormID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [tblLocation] (
[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationCity] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

CONSTRAINT [PK_tblLocation] PRIMARY KEY NONCLUSTERED
(
[LocationID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

I can't even figure how to construct the SQL to use with the PIVOT procedure. I tried and I get this:

Server: Msg 208, Level 16, State 1, Procedure crosstab, Line 23
Invalid object name '##pivot'.

Ugh...thanks for helping out, Spirit1 :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources