SQL Conversion Assistance Needed

[DanVDM]

Came across this code.

SELECT Y,
Q1 = COUNT(CASE WHEN Q=1 THEN Sales END),
Q2 = COUNT(CASE WHEN Q=2 THEN Sales END),
Q3 = COUNT(CASE WHEN Q=3 THEN Sales END),
Q4 = COUNT(CASE WHEN Q=4 THEN Sales END)
FROM
dbo.SalesByQuarter
GROUP BY Y
ORDER BY Y

It does a pivot table type deal.

How would I convert this to a one line SQL statement? Is this possible.

 

[Aaron Bertrand [SQL Server MVP]]

This is a single statement. What do you mean by "one line"?

 

[DanVDM]

Would this work the way it is in ASP?

This is a single statement. What do you mean by "one line"?

 

[Bob Barrows [MVP]]

As in dynamic sql?

Sure. Why not? Just remove the line breaks and assign the string to a
variable, just as you would normally do with any sql statement.

Of course, I would rather encapsulate it in a stored procedure which can
easily be called from ASP.

http://tinyurl.com/jyy0

Bob Barrows

 

[Aaron Bertrand [SQL Server MVP]]

Would this work the way it is in ASP?

Yes. Did you try it?

If you are worried that the query actually wraps lines, you can bypass
thatusing line concatenation, e.g.

sql = "SELECT Y, " & _
"01 = COUNT(...)" & _
"..." & _
" ORDER BY Y"

set rs = conn.execute(sql)

Better yet, create a stored procedure, and call that. This kind of data
logic belongs in the database anyway, not in your ASP code.

 

[DanVDM]

Got it. Thanks, it worked like a charm.

Yes. Did you try it?

If you are worried that the query actually wraps lines, you can bypass
thatusing line concatenation, e.g.

sql = "SELECT Y, " & _
"01 = COUNT(...)" & _
"..." & _
" ORDER BY Y"

set rs = conn.execute(sql)

Better yet, create a stored procedure, and call that. This kind of data
logic belongs in the database anyway, not in your ASP code.

 

[DanVDM]

Hi Bob, I got this to work via the webpage, but I am new to stored
procedures, this seems like a much better way to go. Read the info that you
gave me. I am receiving an error (Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.). It is probably
simple.

Here is my ASP code

<connection string info>
strSQL = "EXEC dbo.byProblemType" & _
" @bdate = '" & bDate & "', " & _
" @edate = '" & eDate & "'"
Conn.Execute strSQL, 3, 3

<stored procedure code>
CREATE PROCEDURE dbo.byProblemType
@bDate VARCHAR(40),
@eDate VARCHAR(40)
AS
DECLARE @begdate DATETIME
DECLARE @enddate DATETIME
SELECT @begDate = CONVERT(DATETIME, @bdate)
SELECT @endDate = CONVERT(DATETIME, @edate)
BEGIN
SELECT ProblemType, Count(ProblemType) as ProbType
FROM Problems
WHERE RptDate >= @begDate and RptDate <= @endDate
GROUP BY ProblemType
END

RptDate is a DATETIME data type

Thanks

 

[DanVDM]

Figured it out on my own. Sorry

Hi Bob, I got this to work via the webpage, but I am new to stored
procedures, this seems like a much better way to go. Read the info that you
gave me. I am receiving an error (Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.). It is probably
simple.

Here is my ASP code

<connection string info>
strSQL = "EXEC dbo.byProblemType" & _
" @bdate = '" & bDate & "', " & _
" @edate = '" & eDate & "'"
Conn.Execute strSQL, 3, 3

<stored procedure code>
CREATE PROCEDURE dbo.byProblemType
@bDate VARCHAR(40),
@eDate VARCHAR(40)
AS
DECLARE @begdate DATETIME
DECLARE @enddate DATETIME
SELECT @begDate = CONVERT(DATETIME, @bdate)
SELECT @endDate = CONVERT(DATETIME, @edate)
BEGIN
SELECT ProblemType, Count(ProblemType) as ProbType
FROM Problems
WHERE RptDate >= @begDate and RptDate <= @endDate
GROUP BY ProblemType
END

RptDate is a DATETIME data type

Thanks

 

[Bob Barrows [MVP]]

Hi Bob, I got this to work via the webpage, but I am new to stored
procedures, this seems like a much better way to go. Read the info
that you gave me. I am receiving an error (Arguments are of the
wrong type, are out of acceptable range, or are in conflict with one
another.). It is probably simple.

Here is my ASP code

<connection string info>
strSQL = "EXEC dbo.byProblemType" & _
" @bdate = '" & bDate & "', " & _
" @edate = '" & eDate & "'"
Conn.Execute strSQL, 3, 3

You are executing this procedure as if it returned no records, in which case
those extra arguments on your Execute statement are the problem. If your
procedure actually did not return records, you would need to change the
statement to:
Conn.Execute strSQL, ,129

However, this procedure returns records so you need a recordset. Change the
statement to:

Set rs=Conn.Execute (strSQL, ,1)
This will return a serverside, forward-only cursor, which is exactly what
you need.

I find this way of running the procedure to be easier:

'first validate that the two variables contain dates:
on error resume next
bDate=cdate(bDate)
eDate=cdate(eDate)
'catch any errors that occur which will indicate a problem with the dates
'then

Set rs=createobject("adodb.recordset")
Conn.byProblemType bDate, eDate, rs

<stored procedure code>
CREATE PROCEDURE dbo.byProblemType
@bDate VARCHAR(40),
@eDate VARCHAR(40)

HTH,
Bob Barrows

 

[Bob Barrows [MVP]]

Figured it out on my own. Sorry

Read my reply anyways. You may find it helpful.

Bob Barrows