SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 totaling per location, per date.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

molebrain
Starting Member

USA
18 Posts

Posted - 09/07/2004 :  12:18:25  Show Profile  Visit molebrain's Homepage  Send molebrain an AOL message  Reply with Quote
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

Slovenia
11751 Posts

Posted - 09/07/2004 :  12:24:14  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/07/2004 :  12:24:20  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

molebrain
Starting Member

USA
18 Posts

Posted - 09/07/2004 :  15:13:57  Show Profile  Visit molebrain's Homepage  Send molebrain an AOL message  Reply with Quote
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000