Solved

SQL Script Help

Posted on 2014-09-24
5
152 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 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

12 Experts available now in Live!

Get 1:1 Help Now