March 21, 2008 at 9:28 am
OK. A few things here:
No need to sub query to pull out a description, and no need to repeat the same case logic over and over again.
See if this gets you something equivalent
Select ConsultantID, ActivityDesc,
Jan = sum(case when month(PeriodEndDate) = 1 then PurchaseAmount else 0 end),
Feb = sum(case when month(PeriodEndDate) = 2 then PurchaseAmount else 0 end),
Mar = sum(case when month(PeriodEndDate) = 3 then PurchaseAmount else 0 end),
Apr = sum(case when month(PeriodEndDate) = 4 then PurchaseAmount else 0 end),
May = sum(case when month(PeriodEndDate) = 5 then PurchaseAmount else 0 end),
Jun = sum(case when month(PeriodEndDate) = 6 then PurchaseAmount else 0 end),
Jul = sum(case when month(PeriodEndDate) = 7 then PurchaseAmount else 0 end),
Aug = sum(case when month(PeriodEndDate) = 8 then PurchaseAmount else 0 end),
Sep = sum(case when month(PeriodEndDate) = 9 then PurchaseAmount else 0 end),
Oct = sum(case when month(PeriodEndDate) = 10 then PurchaseAmount else 0 end),
Nov = sum(case when month(PeriodEndDate) = 11 then PurchaseAmount else 0 end),
Dec = sum(case when month(PeriodEndDate) = 12 then PurchaseAmount else 0 end)
From
(
Select
ConsultantID, 2 as ActivitySort, PurchaseAmount, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 3, SaleAmountLevelone, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 4, SaleAmountLevelTwo, PeriodEndDate
From Volume V
) t1
INNER JOIN #ActivitySummary ON t1.ActivitySort = #ActivitySummary.ActivitySort
Where ConsultantID = @ConsultantID
and PeriodEndDate >= @StartDate and PeriodEndDate < @EndDate
Group By ConsultantID, ActivityDesc
By wrapping the union in a derived table, I can assign the ID to the record I want in order to join to the temp table to get the appropriate description for the row which I can then group on.
Also, how much data are we dealing with here? You may want to experiment with moving the where clauses to the unions depending on data size, and indexes.
And you still have the year crossing issue...
March 21, 2008 at 9:37 am
That worked fantastic. One of these days I will get this TSQL figured out...
One more quick question. for the column Name (eg. JAN) I would like to format as JAN 08 but using the year from the periodenddate column.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 21, 2008 at 9:47 am
alorenzini (3/21/2008)
That worked fantastic. One of these days I will get this TSQL figured out...One more quick question. for the column Name (eg. JAN) I would like to format as JAN 08 but using the year from the periodenddate column.
Yes, It would serve you well to fully understand WHY this examples works as it does, otherwise you fall into the trap of hopeful programming. Hopeful programming when you make random changes hoping that the next change will get the results you need without really understanding the underlying mechanics. I'd suggest thoroughly reading the BOL, and reading articles on set based programming to really get you started, then once you have mastered it, you can pass the knowledge on to the next generation of Padawan SQL learners 🙂
Anyway, off my soap box.
About column names, I usually leave that as an exercise for whatever GUI is going to be displaying the results.
Otherwise, if you really need to, then do a search on Dynamic SQL.
Down that path lies madness...
March 21, 2008 at 9:53 am
Believe me I will take that to heart. I kinda got dumped into this because the DBA Developer resigned. So I am trying to get up to speed as quickly as possible.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 21, 2008 at 10:04 am
Thanks for the feedback! I can sympathize with your situation. It is never easy getting dumped on, especially with SQL. I come from a procedural programming background, and the set based and subtle nature of SQL threw me for a loop (no pun intended) many times, but one day it "clicked", and I started thinking about problem solving is a set based way rather than a procedural way. And I'm still learning! That's what makes our jobs so fun 🙂
March 21, 2008 at 12:38 pm
The code as it sits now:
...
CREATE TABLE #Temp1 (
id_num int IDENTITY(1,1),
COnsultantID char(20),
SponsorID char(20),
RecruitCOunt INT,
AchieveDate DateTime
)
INSERT iNTO #Temp1
Select ConsultantID, SponsorID, COUNT(*) AS RecruitCOunt,AchieveDate FRom #RecruitCount
GROUp BY ConsultantID, SponsorID,achieveDate
Create TABLE #ActivitySummary (
ActivitySort INT,
ActivityDesc CHAR(20)
)
----Select * from #RecruitCount
--Select id_num int IDENTITY(1,1),ConsultantID, SponsorID, COUNT(*) AS RecruitCOunt,AchieveDate FRom #RecruitCount
--GROUp BY ConsultantID, SponsorID,achieveDate
INSERT INTO #ActivitySummary VALUES(1, 'Qualified Recruits')
INSERT INTO #ActivitySummary VALUES(2, 'Personal Sales')
INSERT INTO #ActivitySummary VALUES(3, 'First Line Sales')
INSERT INTO #ActivitySummary VALUES(4, 'Second Line Sales')
INSERT INTO #ActivitySummary VALUES(5, 'Third Line Sales')
Select ConsultantID, ActivityDesc,t1.activitySort,
'Jan 08' = sum(case when month(PeriodEndDate) = 1 then PurchaseAmount else 0 end),
'Feb 08' = sum(case when month(PeriodEndDate) = 2 then PurchaseAmount else 0 end),
'Mar 08' = sum(case when month(PeriodEndDate) = 3 then PurchaseAmount else 0 end),
'Apr 08' = sum(case when month(PeriodEndDate) = 4 then PurchaseAmount else 0 end),
'May 08'= sum(case when month(PeriodEndDate) = 5 then PurchaseAmount else 0 end),
'Jun 08' = sum(case when month(PeriodEndDate) = 6 then PurchaseAmount else 0 end),
'Jul 08' = sum(case when month(PeriodEndDate) = 7 then PurchaseAmount else 0 end),
'Aug 08'= sum(case when month(PeriodEndDate) = 8 then PurchaseAmount else 0 end),
'Sep 08' = sum(case when month(PeriodEndDate) = 9 then PurchaseAmount else 0 end),
'Oct 08' = sum(case when month(PeriodEndDate) = 10 then PurchaseAmount else 0 end),
'Nov 07' = sum(case when month(PeriodEndDate) = 11 then PurchaseAmount else 0 end),
'Dec 07' = sum(case when month(PeriodEndDate) = 12 then PurchaseAmount else 0 end)
From
(Select
ConsultantID, 1 as ActivitySort, tmp1.RecruitCount, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 2 as ActivitySort, PurchaseAmount, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 3, SaleAmountLevelone, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 4, SaleAmountLevelTwo, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 5, SaleAmountLevelThree, PeriodEndDate
From Volume V
) t1
INNER JOIN #ActivitySummary ON t1.ActivitySort = #ActivitySummary.ActivitySort
LEFT OUTER JOIN #Temp1 tmp1 ON t1.COnsultantID = tmp1.SponsorID
Where ConsultantID = @ConsultantID
and PeriodEndDate >= @StartDate and PeriodEndDate < @EndDate
Group By ConsultantID, ActivityDesc,t1.ACtivitySort
Order By t1.ActivitySort
DROP TABLE #ActivitySummary
DROP TABLE #Downline
DROP TABLE #DLLevel
DROP TABLE #RecruitCount
I am trying to add the RecruitCount for the Qualified recruites data which is determined in a temp table but what I am doing does not work.
...
Select
ConsultantID, 1 as ActivitySort, tmp1.RecruitCount, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 2 as ActivitySort, PurchaseAmount, PeriodEndDate
From Volume V
UNION ALL
Select...
The bolded column is suppose to be coming from a derived table.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 21, 2008 at 1:04 pm
Your issue is one of scoping.
You can't reference a field joined to in outer query in the inner query.
When working with derived tables, get your inner query working independent of the outer query, then use the outer query to do any other processing you want.
/*
Select ConsultantID, ActivityDesc,t1.activitySort,
'Jan 08' = sum(case when month(PeriodEndDate) = 1 then PurchaseAmount else 0 end),
'Feb 08' = sum(case when month(PeriodEndDate) = 2 then PurchaseAmount else 0 end),
'Mar 08' = sum(case when month(PeriodEndDate) = 3 then PurchaseAmount else 0 end),
'Apr 08' = sum(case when month(PeriodEndDate) = 4 then PurchaseAmount else 0 end),
'May 08'= sum(case when month(PeriodEndDate) = 5 then PurchaseAmount else 0 end),
'Jun 08' = sum(case when month(PeriodEndDate) = 6 then PurchaseAmount else 0 end),
'Jul 08' = sum(case when month(PeriodEndDate) = 7 then PurchaseAmount else 0 end),
'Aug 08'= sum(case when month(PeriodEndDate) = 8 then PurchaseAmount else 0 end),
'Sep 08' = sum(case when month(PeriodEndDate) = 9 then PurchaseAmount else 0 end),
'Oct 08' = sum(case when month(PeriodEndDate) = 10 then PurchaseAmount else 0 end),
'Nov 07' = sum(case when month(PeriodEndDate) = 11 then PurchaseAmount else 0 end),
'Dec 07' = sum(case when month(PeriodEndDate) = 12 then PurchaseAmount else 0 end)
From
(*/ Select
ConsultantID, 1 as ActivitySort, tmp1.RecruitCount, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 2 as ActivitySort, PurchaseAmount, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 3, SaleAmountLevelone, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 4, SaleAmountLevelTwo, PeriodEndDate
From Volume V
UNION ALL
Select
ConsultantID, 5, SaleAmountLevelThree, PeriodEndDate
From Volume V
/*) t1
INNER JOIN #ActivitySummary ON t1.ActivitySort = #ActivitySummary.ActivitySort
LEFT OUTER JOIN #Temp1 tmp1 ON t1.COnsultantID = tmp1.SponsorID
Where ConsultantID = @ConsultantID
and PeriodEndDate >= @StartDate and PeriodEndDate < @EndDate
Group By ConsultantID, ActivityDesc,t1.ACtivitySort
Order By t1.ActivitySort
*/
Try running the above first (it will fail...what can be done to fix it?), get that to work, then add the outer level.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply