Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Script Help

Posted on 2014-09-24
5
Medium Priority
?
173 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 2000 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 49

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
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…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

572 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