Solved

How to chart out results of a SQL Statement in VBscript

Posted on 2008-10-15
2
155 Views
Last Modified: 2010-04-21
I have a SQL Statement (thanks to a member here) that works quite nicely and returns the proper information I need but I do not know how to output it for users to see. I am using ASP and VBSCRIPT to create a webpage with a table. The table needs to contain the ifo from the SQL statement.

This SQL statement below returns the percentages of Invoices paid for each of our accounts by week (BDATE), when this is run I get a table with ACCO, PourcentageNet and BDATE for each account sorted by Account and BDATE, each account can have up to 52 BDATES (each BDATE is a weekending date).

I need to set up a table of 53 columns, column 1 is Account and column 2-53 are the BDATES.
Each row would have each account and the corresponding PercentValue for that BDATE as queried by the SQL below.

I don't know how to do this...

SELECT Payments.ACCO, Sum(IIf(((Payments.BSTA)="PAID"),Payments.Net,0))/Sum(Payments.Net) AS PourcentageNet, Payments.BDATE
FROM Payments
GROUP BY Payments.ACCO, Payments.BDATE
0
Comment
Question by:EGormly
2 Comments
 
LVL 5

Accepted Solution

by:
PaulKeating earned 500 total points
ID: 22726010
What you want is a crosstab. SQL can't do crosstabs. A select statement can produce any number of rows from 0 up at runtime, but the number and identity and data source of the columns is fixed at parse time. You want the columns in the query to depend on what is in the data and that is something standard SQL just can't do.

There are nonstandard extensions to SQL that will do it, but that depends on the dialect of SQL. For example, if your underlying database is SQL 2005 you can use a pivot clause. But since you're using iif() I am guessing your underlying database is Access, so you're probably out of luck there.

But if I am right and it is Access, then you might be able to save the data from your query in a temporary table and get Access to do an Access pivot table using VB Script.

I'm not an Access expert: interactively you do it with View | Pivot Table View. You might want to repost this question as an Access problem.

Just don't expect to be able to do it in SQL.
0
 

Author Closing Comment

by:EGormly
ID: 31506368
thanks.. not what I wanted to hear.. but still :>
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now