May 9, 2007 at 4:04 pm
Is it possible to write a query that uses the value of a field as the header?
This will be run once a month (and include the next two months) and the date series will advance by a month as well.
(We use SQL 2000 and our users can 'future-date' transactions to make them pending)
Also, the format below is not mandatory - I'm open to your suggestions.
For example, using a month/year as the heading:
User Transaction Counts by Month for April forward:
User 4/07 5/07 6/07
J Doe 22 14 8
B Smith 6 3 10
User Transaction Counts by Month for May forward:
User 5/07 6/07 7/07
J Doe 15 22 20
B Smith 14 9 11
May 9, 2007 at 4:20 pm
There are a couple ways of accomplishing this that I can think of...first let me ask.
How is this data going to be delivered?
If you are going to be using a reporting platform (SSRS, Crystal, Biz Objects, Cognos, Etc...) you should be able to return the data using a simple query and pivot the results making the months listed in the 'date' field the new headers for your report. So...
declare @Month in
set @Month = 4
Select
Month,
User,
Transactions
from
tables...
where
Month >= @Month
Then in your pivot have the 'Month' be the column header value, the 'User' be the row value, and the sum of 'Transactions' be your calculated value.
If the requirement is that the query must return the data already in this format I would suggest using a temp table or dynamic SQL although I'm sure there will be replies about performance using these methods.
Cheers,
Ben
Ben Sullins
bensullins.com
Beer is my primary key...
May 9, 2007 at 11:46 pm
SELECT COUNT(case when Trans_Date >= '04/01/2007' AND Trans_Date = '05/01/2007' AND Trans_Date < '06/01/2007' then ID_col else NULL end) as [05/07],
....
_____________
Code for TallyGenerator
May 10, 2007 at 7:41 am
Sergiy & Ben,
Ben, thanks for brainstorming on this...you brought up some things I really need to consider - possibly a whole new approach.
Sergiy - Sql did not recognize ID_col - but once i put single quotes around it, it worked beautifully! Thanks to all for your help!
"Time flies like an arrow, fruit flies like a banana" - Groucho Marx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply