Solved

need select statement

Posted on 2007-03-26
5
206 Views
Last Modified: 2010-03-19
I have one table with the following fields:
quote_info: quote_number, reps, location, model, product, job_title

this is messy but i want to get the details for each distinct quote_number, product pair and the reps that have job_title like 'sales_rep'

so a simple distinct isn't going to cut it here... I need the details for each quote_number, product combination...
0
Comment
Question by:acdagirl
5 Comments
 
LVL 13

Expert Comment

by:adraughn
ID: 18796277
look at the results from this:

SELECT DISTINCT quote_number, reps, location, model, product, job_title
FROM quote_info
WHERE job_title = 'sales_rep'

how does that compare to what you are looking for?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18796343
you need to give us some example data and results to work on ...

your requirements are to imprecise...

set the scence and tell us what your trying to achieve,,,

e.g.

sales quotes are added to quote_info , a quote can have many products, many sales men can work on a quote..
i need to see ....

 
0
 

Author Comment

by:acdagirl
ID: 18796351
i think the problem is that I want the number of line items to be the count(distinct quote_number, product) so I need some sort of aggregate table...

quote_number  product   employee .....
123                   laptop     abe, bob, jane
123                   mouse    bob, julie
352                   laptop     bubba

like that, rather than

123        laptop    abe
123        laptop   bob
....
0
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 18796788
this is quite complex. You probably need a temp table to return the rep names and you'll need to loop through it for each main record. You'll also need a main temp table.

The main table, #Temp1, will have all the fields, except the REP field will be a varchar(max) field.
#Temp1 also will need a IDENTITY field, call it "QnumID" or something.

create #Temp2 for the REP data. It will have an ID int field, not IDENTITY, and a varchar(max) field for the list of REPS

You insert the data into the main table using the query as above, except don't fill the REP field, and don't fill the IDENTITY field, that popultaes automatically:

INSERT #Temp1
SELECT DISTINCT quote_number, location, model, product, job_title
FROM quote_info
WHERE job_title = 'sales_rep'

next yoy loop through #Temp1 using the IDENTITY field as a counter, and update #Temp1 with concantentated data from a subquery to return the REPS:

DECLARE @Row int
, @RowCount int
SET @Row = 1
SET @RowCount = (SELECT COUNT(1) FROM FROM quote_info WHERE job_title = 'sales_rep')
-- note you have to make sure this count is the same as the count of records for #temp1

WHILE @Row <= @RowCount
  BEGIN
     INSERT #Temp2
     SELECT QNumID, Rep
     FROM #Temp1


  END


0
 
LVL 6

Accepted Solution

by:
Jon_Raymond earned 500 total points
ID: 18796810
Sorry, didn't fimish:

WHILE @Row <= @RowCount
  BEGIN
     INSERT #Temp2
     SELECT QNumID, Rep
     FROM #Temp1

    SEt @Row=@Row+1
  END

--Then loop again
-- some of this is pseudo code

SET @Row=1
WHILE @Row <= @RowCount
  BEGIN
     
     UPDATE t1
     SET rep=t2.rep + ','
   
    FROM #Temp1 t1
    JOIN #temp2 t2 On -- ID fields
    WHERE -- IDs fields = @row for #temp1 and @row for @tem2


    SEt @Row=@Row+1
  END

--Don't have to to finish - and it gets tricky - but this is an approach

Jon
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to check data in sql table 11 45
passing parameter in sql procedure 9 56
Order by but want it in specific order 2 26
Query 14 54
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now