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
Solved

need select statement

Posted on 2007-03-26
5
221 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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