?
Solved

SQL Script Help

Posted on 2014-09-24
5
Medium Priority
?
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

765 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