SQL Script Help

I've run into this issue a few times and I think a sub-query would do the trick but I don't know how to create sub-quires.  The problem I'm having in this case is we have Orders and Quotes.  They are really the same thing but a quote is flagged as Projected Order.  I need to create a report that shows how many orders and quotes a taker has entered.  I have created case statements but this creates two rows for each taker.  One row showing Order with whatever my else statement has  for quotes, the other showing quotes and whatever my else statement has for orders.  I thought maybe I could make the Else something like 9999 so I could filter those out but not sure how to do that.

SELECT DISTINCT
 oe_hdr.taker,
case when (oe_hdr.projected_order = 'n') then COUNT(DISTINCT oe_hdr.order_no) else '9999' end as order_count,
case when (oe_hdr.projected_order = 'n') then COUNT(DISTINCT oe_line.oe_line_uid) else '9999' end as order_line_count,
case when (oe_hdr.projected_order = 'y') then COUNT(DISTINCT oe_hdr.order_no) else '9999' end as quote_count,
case when (oe_hdr.projected_order = 'y') then COUNT(DISTINCT oe_line.oe_line_uid) else '9999' end as quote_line_count

FROM         oe_hdr INNER JOIN
                      oe_line ON oe_hdr.order_no = oe_line.order_no
WHERE     (oe_hdr.date_created >= GETDATE() - 7)
GROUP BY oe_hdr.location_id, oe_hdr.taker, oe_hdr.projected_order

Results from the above script:
TAKER                  order_count                  order_line_count                   quote_count               quote_line_count
Bob                              1                                         2                                             9999                             9999
Bob                            9999                              9999                                                2                                    4

This is what I'm wanting:
TAKER                  order_count                 order_line_count                    quote_count                 quote_line_count
Bob                                1                                       2                                               2                                       4
LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLCommented:
definitely do-able.  I have to run out, but this is an excellent example:
http://www.sqlfingers.com/2011/01/single-line-rowcounts.html
0
John_VidmarCommented:
SELECT	a.taker
,	order_count		=	COUNT(DISTINCT case a.projected_order WHEN 'n' then a.order_no		end)
,	order_line_count	=	COUNT(DISTINCT case a.projected_order WHEN 'n' then b.oe_line_uid	end)
,	quote_count		=	COUNT(DISTINCT case a.projected_order WHEN 'y' then a.order_no		end)
,	quote_line_count	=	COUNT(DISTINCT case a.projected_order WHEN 'y' then b.oe_line_uid	end)
FROM	oe_hdr			a
JOIN	oe_line			b	ON	a.order_no = b.order_no
WHERE	a.date_created >= (GETDATE() - 7)
GROUP
BY	a.taker

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
I suspect you do not need DISTINCT in lines 3 and 5 above

WHERE      a.date_created >= (GETDATE() - 7)
If you run this at 2:12PM on Monday, you would miss all items created before 2:12PM last Monday
I suggest:

WHERE a.date_created >=  dateadd(day,-7, cast(getdate() as date) )

(no points, just small observations)
0
aneesa83Commented:
SELECT 
 CASE WHEN  COUNT(DISTINCT TAKER) = 1
           AND COUNT(TAKER) = COUNT(*) 
         THEN MIN(TAKER)
    END AS TAKER,
    CASE WHEN  COUNT(DISTINCT order_count)>1
           AND COUNT(order_count) = COUNT(*) 
         THEN MIN(order_count)
    END AS order_count
  , CASE WHEN  COUNT(DISTINCT order_line_count) >1
           AND COUNT(order_line_count) = COUNT(*)
         THEN MIN(order_line_count)
    END AS order_line_count
  , CASE WHEN  COUNT(DISTINCT quote_count)>1
           AND COUNT(quote_count) = COUNT(*)
         THEN MIN(quote_count)
    END AS quote_count
  , CASE WHEN  COUNT(DISTINCT quote_line_count) >1
           AND COUNT(quote_line_count) = COUNT(*)
         THEN MIN(quote_line_count)
    END AS quote_line_count
FROM
    (SELECT * FROM @TEST)T

Open in new window

PLEASE NOTE

SELECT * FROM @TEST SHOULD BE YOUR QUERY, IT SHOULD WORK AS THE INNER QUERY
0
abarefootAuthor Commented:
Thanks this worked perfect
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.