User Profile

Jerry Winston · Dec 15 '11, 12:13 AM

I'll have to agree with Glenton here. Doing this kind of historical trend analysis and predictive modeling would be excruciatingly painful in Excel. Let me explain. Creating the functions would be very easy in Excel, however, the program would run slower and slower with each refresh and each report.

I would recommend using any free RDBMS available online. You have express/free versions of Oracle, MySql, MS SQL, and DB2 readily...

Jerry Winston · Jun 30 '11, 02:02 AM

Rob,

Do you have both the Windows Azure Compute Emulator and the Windows Azure Storage Emulator installed? if you're missing either check these links:

http://msdn.microsoft.com/en-us/library/gg432983.aspx
http://msdn.microsoft.com/en-us/library/gg432968.aspx

There's also a "how to configure" article here:
http://sqlblog.com/blogs/buck_woody/...re-emulators-o...

Jerry Winston · Apr 28 '11, 02:24 PM

At least you're able to get started in some capacity(Single User). Let's start debugging!

1. Although the SQL server appears to be failing silently, there are probably several error logs pointing to the failure. Check the SQL Server Logs as well as the Windows application/services logs. A lot of events may have been logged on your system since the last error. Before reviewing the error logs, repeat the steps you followed to the point...

Jerry Winston · Feb 4 '11, 06:10 PM

In the error message you receive in your email, which XXX_USER is the [User] we are INSERTING INTO QUESTIONARIO1? lOGIN_USER, AUTH_USER or REMOTE_USER?

Code:

AUTH_TYPE = Forms

AUTH_USER = BL0210

AUTH_PASSWORD =

LOGON_USER =

REMOTE_USER = BL0210

What is your DB COLLATION?

Also, in your application code. You have the line:

Code:

                n = CInt(ExecuteScalar(Me.ConnString,

...

Jerry Winston · Feb 4 '11, 05:28 PM

@maheshwag

It looks like you posted the functions to bridge gpl's code to your solution(COALES CE and ISNULL).
Is this question answered?
What was the script for your final solution?

Jerry Winston · Feb 4 '11, 05:19 PM

@ADezii

Sorry, I did definitely pointed the tie-beak question at you instead of OP.

Jerry Winston · Feb 4 '11, 04:48 PM

@Rabbit

I agree. (That's why I asked about tie-break rules)
But it doesn't seem to be part of ADezii's requirement.

Jerry Winston · Feb 4 '11, 04:32 PM

There are only two scenarios in which we want to COUNT the records "above" the current row for T1. When the time is less than the current row. and when the time is equal to the current row but the ID is less than T1 row.

Here's the corrected code:

Code:

SELECT T1.Time, ((SELECT Count(*)  FROM  resultsdata
 WHERE resultsdata.Time < T1.Time OR (resultsdata.Time=T1.Time AND resultsdata.ID

...

Jerry Winston · Feb 4 '11, 03:02 PM

@ADezii

Do you have a rule that explains why one runner should be ranked above another if they have the same time?

In any case this might be your solution (look at the overallPosition field):

Code:

SELECT T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time OR (resultsdata.ID=T1.ID OR resultsdata.ID<T1.ID) )) 
AS OverallPosition, 
T1.gender, ((SELECT

...

Jerry Winston · Feb 4 '11, 06:15 AM

You can usually make use of RANK BY to generate these kinds of sequential numbers.

Try this:

Code:

DECLARE @resultsdata TABLE(
	[ID] [int] NOT NULL,
	[firstname] [nvarchar](255) NULL,
	[surname] [nvarchar](255) NULL,
	[Time] [nvarchar](255) NULL,
	[OverallPosition] [nvarchar](255) NULL,
	[gender] [nvarchar](255) NULL,
	[GenderPosition] [nvarchar](255) NULL,
	[Category] [nvarchar](255) NULL,

...

Jerry Winston · Feb 3 '11, 03:04 PM

O.k. let's put on our debugging hats!

I have an unfounded suspicion it has something to do with the collation of your table/database but let's be thorough anyway. Can you post the CREATE scripts for:

dbo.QUESTIONARI O1
PK__QUESTION__A A65E05E412EB0B6
PK__QUESTION__A A65E05E1881A0DE

Jerry Winston · Feb 2 '11, 07:33 PM

I don't know if this is an improvement but here's a different way to look at class organization:

CourseTable: name of class, course code, Description("Ma th for DB's", MATH, "Database math")

CourseSectionTa ble: course section information (like your details table) meeting times, locations instructors ect.

There's alot of detail associated with large course management systems(like at colleges...

Jerry Winston · Feb 2 '11, 07:09 PM

I agree, that's the wrong output but what does your SQL statement look like? It's going to be tough to figure out the problem with your query without the query.

However, I did notice you have something funky going on with the single quote marks around your "text" and "city" string literals. Try deleting the single quotes and reapplying them. It looks like you picked up some odd characters by copy/pasting.

Jerry Winston · Feb 2 '11, 05:45 PM

Ok. There are a few ways to do this. It sounds like you want the difference of two averages. Because you omitted the comparison of weeks 2 and 3, I think you want the average of an arbitrary pair of weeks NOT the average over a two week interval. I'll give you two solutions. One iterative method using a CURSOR and another using a predefined pair Table.

The cursor method:

Code:

DECLARE @baseTable TABLE([month] int, [week] int, price

...

Jerry Winston · Feb 2 '11, 04:48 PM

I'm not sure I understand your requirement or perhaps your reason for distributing so much of your logic into many,separate ad-hoc SQL queries.

The easiest solution is to wrap the good ol' TRY...CATCH block in a single stored procedure. eg:

Code:

CREATE PROCEDURE sp_addIfNew(@userVar VARCHAR(32))
AS
BEGIN
BEGIN TRY
INSERT INTO QUESTIONARIO1([USER]) VALUES (@userVar)
END TRY
BEGIN CATCH

...

Jerry Winston · Dec 16 '10, 07:26 PM

Notice the blank space characters in you primary key name "University PK". SQL is expecting a single name. You could add an under score like :

Code:

ALTER TABLE UNIVERSITY ADD CONSTRAINT UNIVERSITY_PK PRIMARY KEY(U_PK);

or enclose the key name in brackets:

Code:

ALTER TABLE UNIVERSITY ADD CONSTRAINT [UNIVERSITY PK] PRIMARY KEY(U_PK);

Also, take a look at stepterr's syntax. I think you don't intend CONSTRAINT to be...

Jerry Winston · Sep 7 '10, 01:23 PM

I don't know if this is a typo here or in your executing code, but I think you've mislabled a field somewhere in your code.

Code:

@Cont[B][U]r[/U][/B]actStateCode

is not a valid parameter. however

Code:

@ContactStateCode

is a parameter.

Jerry Winston · Sep 7 '10, 01:00 PM

It looks like you're coding in VB.NET. If you are use two " marks to insert a single " inside of quotes.

Code:

"SELECT CADD_File_Name,Drawing_Number,Drawing_Title
FROM Drawing_Log
WHERE CADD_File_Name LIKE ""C1130*""  "

Also, add a break point at the top line and inspect your data objects while your code is running.

Jerry Winston · Sep 3 '10, 08:22 PM

What's your database? SQL or Access? What versions?

Jerry Winston · Sep 3 '10, 07:16 PM

Your ad hoc sql

Code:

"SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log" & " WHERE CADD_File_Name LIKE '" & CaddFile & "'"

Evaluates to:

Code:

SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log WHERE CADD_File_Name LIKE 'someFileName'

The problem is your like statement is missing the underscore or wildcard character '%'. I think you want your...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment: