Solved

need select statement

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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