Solved

SQL Script Help

Posted on 2014-09-24
5
155 Views
Last Modified: 2014-09-25
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
0
Comment
Question by:abarefoot
5 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 40342879
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
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40342907
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40343238
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
 
LVL 1

Expert Comment

by:aneesa83
ID: 40343376
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
 
LVL 1

Author Closing Comment

by:abarefoot
ID: 40344474
Thanks this worked perfect
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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