Solved

need select statement

Posted on 2007-03-26
5
229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 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