Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    32

    Unanswered: Select into parameters grouped data

    Hi all,

    This is probably one of the easier questions you get, but I have brain freeze and just can't do it and very rusty.

    How can i assign the the values from sql below into variables e.g I want to get all new, pending and cancelled Leads from the rows returned which will come from the status id.


    SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID
    Order by LeadStatusID asc

    Cheers

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    What exactly are you trying to do? There is no such thing as a multi-valued variable. The query you show us will not return one row, but many. What you can do however, is using the INTO Clause to insert the data to a new (local) temporary table.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jul 2004
    Posts
    32
    It seems that I have caused a bit of confusion I am using Sql Server 2005 and I want to return several output parameter e.g.

    @intLeadsApproved, @intLeadsPending and @ntLeadsRejected as integer output parameters

    from a sql query below. The query below will return all the rows for the different statuses but the I want to do a quick check to see if its statusid = 1 then its approved so

    SELECT @intLeadsApproved = Count (leadStatusId) from t Where Leadstatusid = 1
    From (query below) t

    I know the syntax is messed up but I hope I have explained a little better.

  4. #4
    Join Date
    Jul 2004
    Posts
    32
    Is this really difficult, I'm no sql expert but basically I'm trying to query the results from a result set but I'm getting an error when I use a embedded select.

    I'd really appreciate any help.

    Thanks

  5. #5
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

    Thumbs up

    Quote Originally Posted by gaj
    Is this really difficult, I'm no sql expert but basically I'm trying to query the results from a result set but I'm getting an error when I use a embedded select.

    I'd really appreciate any help.

    Thanks

    took some time for me to undetstand too...coz u havent explained it that clearly but from what i get i can give you a solution

    whatever you get from this query

    SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID
    Order by LeadStatusID asc


    you store it in a temporary table which can be somthing like

    declare @temp_table table
    (
    NoOfLead int,
    leadStatusID int,
    sum_value int
    )

    then you can do
    insert into @temp_table
    SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID

    so now all your resultset will go into @temp_table

    now u can query your temp table as

    SELECT @intLeadsApproved = NoOfLead from @temp_table Where leadStatusID = 1

    hope this helped..

  6. #6
    Join Date
    Jul 2004
    Posts
    32
    Yes it does help, thank you.

    I was hoping there was an easier query because I don't want to use a temporary table, Is it not possible to create a query like this.

    PHP Code:
    Select @Approved Select NoOfLeads From Temp where leadstatusid 1
    From
    (SELECT Count (leadStatusId) As NoOfLeadsleadStatusIDsum(value) As Value
    FROM 
    [dbo].[tLeadL
    Inner Join dbo
    .tLeadStatus S on linkLeadStatus LeadStatusID
    Inner Join dbo
    .tClick C on linkClick ClickID
    WHERE L
    .DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID
    Temp
    Order by LeadStatusID asc 

  7. #7
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    ya you can try out this query it'll work fine

    Select @Approved = XYZ.NoOfLeads
    From
    (SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID) as XYZ
    where XYZ.leadstatusid = 1

  8. #8
    Join Date
    Jul 2004
    Posts
    32
    Okay, we are getting there now so I want to get the following, so how do I do this,

    Select @Approved = NoOfLead from XYZ where XYZ.leadstatusid = 1,
    @Pending= NoOfLead from XYZ where XYZ.leadstatusid = 2
    From
    (SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID) as XYZ

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    When using a subquery "table" like this, you can't return two different values based on two different WHERE clauses like you are attempting to do.

    You will either have to do the assignment in two separate queries (using identical XYZ-generating sub-selects) or a single query using two identical sub-selects.

    The sub-select can be thought of as a single table...and it's not possible (as far as I know) to use two different WHERE clauses to generate two different variable assignments when you are in the same select on the same table (without doing something really ugly like a sub-select inside a sub-select).

    as an example:
    Code:
    Select @Approved = XYZ.NoOfLead
    From
    (SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID) as XYZ
    where XYZ.leadstatusid = 1
    
    Select @Pending= XYZ.NoOfLead
    From
    (SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID) as XYZ
    where XYZ.leadstatusid = 2
    Last edited by TallCowboy0614; 05-23-07 at 19:57.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I suppose you could also do something like the following to get it all in one select, but to do so would probably ultimately bring you into discord with the Gods, and result in your being flayed, your soul being raked across the burning coals of despair at some point in the afterlife, and very likely prevent anyone of the opposite sex from ever again giving you a phone number.
    Code:
    SELECT @approved = WTF.approved, @Pending = WTF.pending
    FROM 
    (Select XYZ.NoOfLead
    From
    (SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID) as XYZ
    where XYZ.leadstatusid = 1) as approved,
    (Select XYZ.NoOfLead
    From
    (SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
    FROM [dbo].[tLead] L
    Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
    Inner Join dbo.tClick C on linkClick = ClickID
    WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
    Group by LeadStatusID) as XYZ
    where XYZ.leadstatusid = 2) as pending)) as WTF
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    there could be many solutions.... like a few given over here by tallcowboy....... however it boils down to only one thing... for every leadstatusid u will have to write a different subquery.......there cannot be 2 where clauses as you have specified.....

    finally how you implement it depends upon your imagination......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •