SQL multiple criteria arrays - 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

General SQL Server Forums

New to SQL Server Programming

SQL multiple criteria arrays

Author

Topic

Lyriclover
Starting Member

8 Posts

Posted - 2010-02-09 : 11:03:02

I've been attempting to create automated charts for our company website using ChartDirector (asp version). We generally write with vb/html. (I'm a newbie, so please accept my apologies on bad terminology)

I managed to run a simple SQL that pulls up the Date and Feb fields (date field and number field, respectively) from a table in my database, and ChartDirector creates a lovely line chart just fine. My question seems to be however, how can i split this SQL? I would like to be able to have multiple arrays of data, so I can compare them over a similar timeline.

MY Example:
iif Date >= #2008/15/8# AND <= #2009/2/14#, then "Feb09 Array"
Else
iif Date >= #2007/15/8# AND <= #2008/2/14#, then "Feb08 Array"
Else... etc

So that I can somehow use these arrays versus one another over a time period of Feb 15 through Feb 14, how can I line up the above said Feb data so the prices generally line up versus one another?

I received a hint from ChartDirector, but it doesn't really apply I don't think, because I don't wish to use a calendar year.

**********
You can write VBScript code to separate your Date and Feb into multiple arrays. For example:

allDate = dbTable.getCol(0)
allFeb = dbTable.getCol(1)

For currentYear = 2005 to 2010
... get the data from the currentYear from allDate and allFeb, and store them in
... the arrays annualDate, and annualFeb. The annualDate should only contain the
... month and day part of the date. The year can be set to 2000 (or any constant)

'add the annualDate and annualFeb to
Set layer = c.addLineLayer(annualFeb, -1, currentYear)
Call layer.setXData(annualFeb)
Next
*********

Any ideas? Anything you can provide would be greatly appreciated.

Lyriclover
Starting Member

8 Posts

Posted - 2010-02-09 : 13:23:54

Ok, so I've made some changes to my MS Access data and the asp code I've been working on.

I have a query named FebHogs which contains the following fields:
Date: daily dates (no weekends) which range from 1995 to current
Feb: contains daily prices for the February CME Lean Hog Futures
FebF: calculates a "label" based on specific contract start/end dates (these start and end dates are always different based on a particular contract). The labels assigned are as such; Feb07, Feb08, Feb09, Feb10, or Feb11 (hence text). In general, the contract "year" is approx Feb 15 through Feb 14 (Feb08 would expire Feb 14'08, then Feb09 would begin).
GenDate: calculates a common date (I've used 2008 as my base year), uses month and day from the Date field (field 1 in my table, see above) and used 2008 as the date.

Here's some examples of the data from my query in MS Access.

Date Feb FebF GenDate
08-May-08 82.77 Feb09 5/8/2008
09-May-08 82.95 Feb09 5/9/2008
12-May-08 81.95 Feb09 5/12/2008
13-May-08 81.55 Feb09 5/13/2008

26-May-06 56.65 Feb07 5/26/2008
30-May-06 57.35 Feb07 5/30/2008
31-May-06 57.27 Feb07 5/31/2008
01-Jun-06 58.35 Feb07 6/1/2008
02-Jun-06 58.47 Feb07 6/2/2008

Maybe I've gotten all mixed up because I'm really more of an MS Excel user, and limited MS Access user, but I need to represent my data for each contract (Feb07, Feb08, etc) over Feb 15 through Feb 14.

I'm honestly not sure what I'm doing wrong, and no amount of google/forum searches seem to be helping. Anything you could add would be great.

I've attempted to use the code below, but keep receiving CD errors:

<%@ language="vbscript" %>
<%
Dim conn
Dim strSQL
Dim rs
Dim xDb_Conn_Str
Dim CFeb07, CFeb08, CFeb09, CFeb10, CFeb11


%>
<%
Set cd = CreateObject("ChartDirector.API")

SQL = "SELECT Date, Feb, FebF, GenDate FROM FebHogs where FebF IS NOT NULL order by Date;"

Set rs = CreateObject("ADODB.RecordSet")
Call rs.Open(SQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\WebDev\Db\WebDb.mdb;")
Set dbTable = cd.DBTable(rs)
rs.Close()

CFeb07 = "SELECT Feb From FebHogs where FebF = 'Feb07' order by GenDate;"
CFeb08 = "SELECT Feb From FebHogs where FebF = 'Feb08' order by GenDate;"
CFeb09 = "SELECT Feb From FebHogs where FebF = 'Feb09' order by GenDate;"
CFeb10 = "SELECT Feb From FebHogs where FebF = 'Feb10' order by GenDate;"
CFeb11 = "SELECT Feb From FebHogs where FebF = 'Feb11' order by GenDate;"

Set c = cd.XYChart(600, 350, &Heeeeff, &H000000, 1)
Call c.setRoundedFrame()

Call c.setPlotArea(55, 58, 520, 195, &Hffffff, -1, -1, &Hcccccc, &Hcccccc)

Call c.addTitle("Daily February Futures", "timesbi.ttf", 15).setBackground( _
&Hccccff, &H000000, cd.glassEffect())

Call c.xAxis().setLabelStep(16)
Call c.xAxis().setlabelstyle("arial.ttf", 8, &H000000, 90)

Call c.xAxis().setTitle("")

'Add a title to the y axis
Call c.yAxis().setLinearScale(0,90)
Call c.yAxis().setLabelFormat("{value|2,.}")

'Call c.xAxis().setLabels(CDate)
' Call c.xAxis().setLabels(dbTable.getCol(3))
' Call c.xAxis().setLabelFormat("{value|mmm dd}")
Call c.xAxis().setDateScale(#2007/2/15 00:00:00#, #2008/2/14 00:00:00#)
Call c.xAxis().setLabelFormat("{value|mmm dd}")

Call c.xAxis().setLabelStep(2)
Call c.xAxis().setlabelstyle("arial.ttf", 8, &H000000, 90)

Set layer = c.addLineLayer2()

Call layer.setLineWidth(2)

Call layer.addDataSet(CFeb11, -1, "2011")
Call layer.addDataSet(CFeb10, -1, "2010")
Call layer.addDataSet(CFeb09, -1, "2009")
Call layer.addDataSet(CFeb08, -1, "2008")
Call layer.addDataSet(CFeb07, -1, "2007")
Call layer.setBorderColor(cd.Transparent, 1)

Response.ContentType = "image/png"
Response.BinaryWrite c.makeChart2(cd.PNG)
Response.End
%>

Error currently being displayed:
ChartDirector error '800a8000'
Error converting argument 1 to type class DoubleArray
/charts/testing100204d.asp, line 65

Sachin.Nand

2937 Posts

Posted - 2010-02-09 : 13:36:43

At the first look for me it looks more like a asp issue than a SQL one.Also ur using some kind of 3rd party charting control.
Maybe u shud declare Dim CFeb07, CFeb08, CFeb09, CFeb10, CFeb11 as an Array.I am not quite sure as I am not so good on asp.
Try this

CFeb07 = "SELECT top 1(Feb) From FebHogs where FebF = 'Feb07' order by GenDate;"
CFeb08 = "SELECT top 1(Feb) From FebHogs where FebF = 'Feb08' order by GenDate;"
CFeb09 = "SELECT top 1(Feb) From FebHogs where FebF = 'Feb09' order by GenDate;"
CFeb10 = "SELECT top 1(Feb) From FebHogs where FebF = 'Feb10' order by GenDate;"
CFeb11 = "SELECT top 1(Feb)From FebHogs where FebF = 'Feb11' order by GenDate;"

and check whether the error is replicated.

PBUH

Lyriclover
Starting Member

8 Posts

Posted - 2010-02-09 : 13:44:44

I thank you for your reply Idera. :)

Unfortunately, I am still receiving the same error. I will attempt to find an ASP forum. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources