TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Kavitha M
NA
21
6.5k
Get data - Group by from inner query
May 1 2015 2:22 AM
Hi all,
I have the following query to select top 10 customers
SELECT top 10 A.Div as 'Div'
, A.CID as 'Customer_ID'
, A.CNAME as 'Customer_Name'
, B.REP as 'Primary_Rep'
,count(distinct B.SONO) as 'TY_Orders_Count'
, ISNULL(SUM(CASE A.SIVTYP_0
WHEN 'INV' THEN (B.QTY_0)
WHEN 'SCM' THEN (-B.QTY_0)
ELSE 0 END),0) as 'TY_Qty_UnitCount'
, ISNULL(SUM( D.PRICE *(CASE A.SIVTYP_0
WHEN 'INV' THEN (B.QTY_0)
WHEN 'SCM' THEN (-B.QTY_0)
ELSE 0 END)),0) as 'TY_COST'
from INVOICE_TABLE A
INNER JOIN INVDETAILS_TABLE B on A.IID = B.IID
INNER JOIN PRODUCTS_TABLE D on Year(B.INVDAT) = D.YEA_0 and D.PROD_ID = B.PROD_ID
where (B.INVDAT between '04/10/2015' AND '04/17/2015')
AND A.Div in ('AS','DF','GH','JH','KL')
group by
A.Div
,A.CID
,A.CNAME
,B.REP
This query returns top 10 customers in whole data. NOw I want to get top 10 customers for each division seperately
i.e top 10 customers in 'AS',top 10 customers in 'DF',top 10 customers in 'GH',top 10 customers in 'JH',top 10 customers in 'KL'
I tried with this query, but unable to get data for eac division
can anyone help me to do this
Thanks in advance
Reply
Answers (
1
)
Get Division wise top customers - Sales Report
Database Restore Problem