August 2, 2011 at 3:25 pm
Hi guys,
I'm a fairly new DBA and am starting to get the hang of TSQL, but can't quite wrap my mind around stored procedures just yet.
I have 4 tables that I use for sales reporting and sales goals information.
My query below gives me the information I need for me report. I clean the rest of the data through Crystal Reports. Our current data structure is spread across 3 systems that don't communicate and we build small databases for things like budgets, sales, and whatever else we need.
This query:
WITH InvoicedAndPaid
AS
(
SELECT KEY1
,ltrim(rtrim(CustomText01)) as CustomText01
,ROW_NUMBER() OVER(PARTITION BY DocNo ORDER BY DocNo) AS RowNumber
,SubTotal
,OrderDate
,INVOICEDATE
,MonthInvBalZero
,YearInvBalZero
FROM DocumentHeadersItems AS AA
JOIN SorArCombined AS BB
ON AA.DocNo = ('AA' + BB.CustomerPoNumber)
JOIN CONTACT1 AS CC
ON AA.SoldToCMAccountNo = CC.ACCOUNTNO
WHERE CreatedBy != 'PARTS'
AND KEY1 IN ('EA')
AND SubTotal != 0
AND LEN(INVOICE) > 0
AND CUSTOMER != 'QAA'
AND (YearInvBalZero > 0)
AND (YearInvBalZero = DATEPART(YYYY,'2011-7-1'))
AND (MonthInvBalZero = DATEPART(MM,'2011-7-1'))
AND (YearInvBalZero = DATEPART(YYYY,'2011-7-30'))
AND (MonthInvBalZero = DATEPART(MM,'2011-7-30'))
)
SELECT bb.team_id
,bb.specialist_id
,dd.Budget
,cc.team_name
,KEY1
,CustomText01
,INVOICEDATE
,MonthInvBalZero
,YearInvBalZero
,CASE WHEN RowNumber = 1 THEN SubTotal ELSE 0 END as SubTotal
FROM InvoicedAndPaid as aa
join SalesReporting.dbo.team_members as bb
on rtrim(aa.CustomText01) = rtrim(bb.specialist_name)
join SalesReporting.dbo.team_master as cc
on bb.team_id = cc.team_id
join SalesReporting.dbo.sales_goals as dd
on bb.specialist_id = dd.specialist_id
WHERE dd.time_span LIKE 'M%'
AND dd.Org = aa.KEY1
AND dd.period_no = DATEPART(MM,'2011-7-30')
ORDER BY team_name, CustomText01, KEY1
Gives me this result:
CustomText01Budgetteam_nameKEY1INVOICEDATEMonthInvBalZeroYearInvBalZeroDocNoSubTotal
JOHN34656JOHNEA7/19/1172011AAAQ599511576.22
JERRY34656JOHNEA7/19/1172011AAAQ59951 650.00
TOMH0JOHNEA5/13/1172011AAAQ574232222.81
PAUL0JOHNEA5/13/1172011AAAQ57423 741.00
This gives me all of the results I need. What I'm looking at is if a salesman made a quote, if it went to our accounting system, and if it was invoiced and paid.
What I want to see is the salesman who didn't sell anyting in the same records and the salesman who did sell something. Every salesman has a budget he would like to meet. If he doesn't sell anything, his sales will count against the company budget. Ideally I want me data to show like this, where Jim hasn't sold anything this month where KEY1 = 'EA':
CustomText01Budgetteam_nameKEY1INVOICEDATEMonthInvBalZeroYearInvBalZeroDocNoSubTotal
JOHN34656JOHNEA7/19/1172011AAAQ599511576.22
JERRY34656JOHNEA7/19/1172011AAAQ59951 650.00
TOMH0JOHNEA5/13/1172011AAAQ574232222.81
PAUL0JOHNEA5/13/1172011AAAQ57423 741.00
JIM10000JOHNEA000EMPTY/NULL 0.00
I've tried using a FULL OUTER JOIN, LEFT JOIN, RIGHT JOIN...but I've been getting the same results. I think I'm joining too many tables together for the full outer join to work. Another thing I've thought about is using a stored procedure, but I don't have much knowledge on that.
Wondering if someone can push me in the right direction here. Let me know if you need to see data from the tables. I didn't put it on here since it was becoming crowded.
August 2, 2011 at 4:17 pm
The table holding the column CustomText01 needs to be on the left side of a LEFT OUTER JOIN and the join predicates might need to include an OR IS NULL condition.
The way I usually do tasks like that is to use a cte providing the names I want to display and use it in a LEFT OUTER JOIN (maybe together with another cte or subquery if the join won't be simple).
August 4, 2011 at 2:45 pm
Thanks for the tip. It didn't actually work, though.
I think since the query was looking for DocNo, CustomText01 and KEY1 to be joined together, I wasn't going to get the right results anyways.
I ended up recycling a stored procedure an old employee had created. First time for everything, I guess!
August 7, 2011 at 6:52 pm
Try it this way:
....
FROM InvoicedAndPaid as aa
RIGHT JOIN SalesReporting.dbo.team_members as bb on rtrim(aa.CustomText01) = rtrim(bb.specialist_name)
....
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply