SQL Conversion Assistance Needed

Discussion in 'ASP General' started by DanVDM, Jul 6, 2005.

  1. DanVDM

    DanVDM Guest

    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.
    DanVDM, Jul 6, 2005
    #1
    1. Advertising

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





    "DanVDM" <> wrote in message
    news:...
    > 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], Jul 6, 2005
    #2
    1. Advertising

  3. DanVDM

    DanVDM Guest

    Would this work the way it is in ASP?


    "Aaron Bertrand [SQL Server MVP]" <> wrote in message
    news:...
    > This is a single statement. What do you mean by "one line"?
    >
    >
    >
    >
    >
    > "DanVDM" <> wrote in message
    > news:...
    > > 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.
    > >
    > >
    > >

    >
    >
    DanVDM, Jul 6, 2005
    #3
  4. 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

    DanVDM wrote:
    > Would this work the way it is in ASP?
    >
    >
    > "Aaron Bertrand [SQL Server MVP]" <> wrote in
    > message news:...
    >> This is a single statement. What do you mean by "one line"?
    >>
    >>
    >>
    >>
    >>
    >> "DanVDM" <> wrote in message
    >> news:...
    >>> 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.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 6, 2005
    #4
  5. > 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.
    Aaron Bertrand [SQL Server MVP], Jul 6, 2005
    #5
  6. DanVDM

    DanVDM Guest

    Got it. Thanks, it worked like a charm.


    "Aaron Bertrand [SQL Server MVP]" <> wrote in message
    news:...
    > > 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, Jul 6, 2005
    #6
  7. DanVDM

    DanVDM Guest

    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]" <> wrote in message
    news:...
    > 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
    >
    > DanVDM wrote:
    > > Would this work the way it is in ASP?
    > >
    > >
    > > "Aaron Bertrand [SQL Server MVP]" <> wrote in
    > > message news:...
    > >> This is a single statement. What do you mean by "one line"?
    > >>
    > >>
    > >>
    > >>
    > >>
    > >> "DanVDM" <> wrote in message
    > >> news:...
    > >>> 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.

    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    DanVDM, Jul 7, 2005
    #7
  8. DanVDM

    DanVDM Guest

    Figured it out on my own. Sorry


    "DanVDM" <> wrote in message
    news:...
    > 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]" <> wrote in message
    > news:...
    > > 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
    > >
    > > DanVDM wrote:
    > > > Would this work the way it is in ASP?
    > > >
    > > >
    > > > "Aaron Bertrand [SQL Server MVP]" <> wrote in
    > > > message news:...
    > > >> This is a single statement. What do you mean by "one line"?
    > > >>
    > > >>
    > > >>
    > > >>
    > > >>
    > > >> "DanVDM" <> wrote in message
    > > >> news:...
    > > >>> 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.

    > >
    > > --
    > > Microsoft MVP -- ASP/ASP.NET
    > > Please reply to the newsgroup. The email account listed in my From
    > > header is my spam trap, so I don't check it very often. You will get a
    > > quicker response by posting to the newsgroup.
    > >
    > >

    >
    >
    DanVDM, Jul 7, 2005
    #8
  9. DanVDM wrote:
    > 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
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 7, 2005
    #9
  10. DanVDM wrote:
    > Figured it out on my own. Sorry
    >

    Read my reply anyways. You may find it helpful.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 7, 2005
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. azdunes
    Replies:
    2
    Views:
    786
    ivailokroumov
    Sep 16, 2004
  2. news.microsoft.com

    Architectural assistance needed!

    news.microsoft.com, Jan 13, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    347
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Jan 13, 2006
  3. Jeff Goslin

    Newbie seeking VB to ANSI C Conversion assistance

    Jeff Goslin, Nov 5, 2003, in forum: C Programming
    Replies:
    14
    Views:
    699
    Jimmy
    Nov 23, 2003
  4. Alden Pierre
    Replies:
    3
    Views:
    340
    Ben Pope
    Apr 5, 2006
  5. michael horthum

    c# assistance needed 10$/hour

    michael horthum, Dec 12, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    108
    michael horthum
    Dec 12, 2005
Loading...

Share This Page