November 28, 2007 at 11:37 am
Hi all. I have these two queries, that work separately. When trying to combine them I have 121 rows returned, rather than the desired 11 rows that each returns separately. I actually need it to run with 9 months, from January through September, but include two here for brevity.
These work individually:
--jan
select #GovRev.Code_t,
#GovRev.CommissionableRevenue,
smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR], -- for month revenue occurred
(#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR]) as JanDiff --totals
from #GovRev, smcsarchive..CEM_SepYTD_TBRbyBranch
WHERE #GovRev.RevDate = '1/1/07' --for month running report, 2007
and #GovRev.Code_t = smcsarchive..CEM_SepYTD_TBRbyBranch.brcode
ORDER BY #GovRev.Code_t
--feb
select #GovRev.Code_t,
#GovRev.CommissionableRevenue,
smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR], -- for month revenue occurred
(#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR]) as FebDiff --totals
from #GovRev, smcsarchive..CEM_SepYTD_TBRbyBranch
WHERE #GovRev.RevDate = '2/1/07' --for month running report, 2007
and #GovRev.Code_t = smcsarchive..CEM_SepYTD_TBRbyBranch.brcode
ORDER BY #GovRev.Code_t;
This one doesn't work, but I seem to be getting closer to the desired result, which is one column of branch codes on the left, followed on the right by columns for revenue, tbr and difference for each month.
select jan.Code_t, jan.CommissionableRevenue
, feb.CommissionableRevenue --,feb.Code_t
FROM
(select #GovRev.Code_t,
#GovRev.CommissionableRevenue ,
smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR], -- for month revenue occurred
(#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR]) as JanDiff --totals
from #GovRev, smcsarchive..CEM_SepYTD_TBRbyBranch
WHERE #GovRev.RevDate = '1/1/07' --for month running report, 2007
and #GovRev.Code_t = smcsarchive..CEM_SepYTD_TBRbyBranch.brcode
group by #GovRev.RevDate, #GovRev.CommissionableRevenue, #GovRev.Code_t, smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR]--, JanDiff
--ORDER BY #GovRev.Code_t
)jan,
(select --#GovRev.Code_t,
#GovRev.CommissionableRevenue ,
smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR], -- for month revenue occurred
(#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR]) as FebDiff --totals
from #GovRev, smcsarchive..CEM_SepYTD_TBRbyBranch
WHERE #GovRev.RevDate = '2/1/07' --for month running report, 2007
and #GovRev.Code_t = smcsarchive..CEM_SepYTD_TBRbyBranch.brcode
group by #GovRev.RevDate, #GovRev.CommissionableRevenue, #GovRev.Code_t, smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR]--, FebDiff
--ORDER BY #GovRev.Code_t
)feb
Any suggestions?
tia
November 28, 2007 at 12:15 pm
your second example is throwing out so many rows because you are doing a cross join between your two derived tables.
Not exactly sure what your output needs to be but here's a guess after looking at your code examples.
select #GovRev.Code_t,
case when Rev.date = '1/1/07' then #GovRev.CommissionableRevenue end as JanCommis,
case when Rev.date = '1/1/07' then smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR] end as JanRev, -- for month revenue occurred
case when Rev.date = '1/1/07' then (#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR]) end as JanDiff --totals
case when Rev.date = '2/1/07' then #GovRev.CommissionableRevenue end as FebCommis,
case when Rev.date = '2/1/07' then smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR] end as FebRev, -- for month revenue occurred
case when Rev.date = '2/1/07' then (#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR]) end as JanDiff --totals
from #GovRev, smcsarchive..CEM_SepYTD_TBRbyBranch
WHERE #GovRev.Code_t = smcsarchive..CEM_SepYTD_TBRbyBranch.brcode
and #GovRev.RevDate in ('1/1/07', '2/1/07') --for month running report, 2007
ORDER BY #GovRev.Code_t😀
November 28, 2007 at 12:37 pm
That's almost it. I made some minor changes and am getting closer still:
select #GovRev.Code_t,
(case when #GovRev.RevDate = '1/1/07' then #GovRev.CommissionableRevenue end) as JanCommis,--
(case when #GovRev.RevDate = '1/1/07' then smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR] end) as JanRev, -- for month revenue occurred
(case when #GovRev.RevDate = '1/1/07' then (#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan TBR]) end) as JanDiff, --totals
(case when #GovRev.RevDate = '2/1/07' then #GovRev.CommissionableRevenue end) as FebCommis,
(case when #GovRev.RevDate = '2/1/07' then smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR] end) as FebRev, -- for month revenue occurred
(case when #GovRev.RevDate = '2/1/07' then (#GovRev.CommissionableRevenue - smcsarchive..CEM_SepYTD_TBRbyBranch.[Feb TBR]) end) as FebDiff --totals
from #GovRev, smcsarchive..CEM_SepYTD_TBRbyBranch
WHERE #GovRev.Code_t = smcsarchive..CEM_SepYTD_TBRbyBranch.brcode
and #GovRev.RevDate in ('1/1/07', '2/1/07') --for month running report, 2007
ORDER BY #GovRev.Code_t
I get this (hope you can see it well enough):
Code_t JanCommis JanRev JanDiff FebCommis FebRev FebDiff
GMCALOC 8107541.57 8107541.57 0.0 NULL NULL NULL
GMCALOC NULL NULL NULL 11786622.65 11786622.65 0.0
But need this:
Code_t JanCommis JanRev JanDiff FebCommis FebRev FebDiff
GMCALOC 8107541.57 8107541.57 0.0 11786622.65 11786622.65 0.0
November 28, 2007 at 9:14 pm
November 29, 2007 at 2:16 am
Use table aliases to make your query far more readable, and correct your join syntax:
SELECT gr.Code_t,
SUM(case when gr.RevDate = '1/1/07' then gr.CommissionableRevenue end) as JanCommis,--
SUM(case when gr.RevDate = '1/1/07' then s.[Jan TBR] end) as JanRev, -- for month revenue occurred
SUM(case when gr.RevDate = '1/1/07' then (gr.CommissionableRevenue - s.[Jan TBR]) end) as JanDiff, --totals
SUM(case when gr.RevDate = '2/1/07' then gr.CommissionableRevenue end) as FebCommis,
SUM(case when gr.RevDate = '2/1/07' then s.[Feb TBR] end) as FebRev, -- for month revenue occurred
SUM(case when gr.RevDate = '2/1/07' then (gr.CommissionableRevenue - s.[Feb TBR]) end) as FebDiff --totals
FROM #GovRev gr
INNER JOIN smcsarchive..CEM_SepYTD_TBRbyBranch s ON s.brcode = gr.Code_t
WHERE gr.RevDate in ('1/1/07', '2/1/07') --for month running report, 2007
GROUP BY gr.Code_t
ORDER BY gr.Code_t
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 29, 2007 at 8:15 am
Here's a snippet of the end result. Thank you for the lesson!
SELECT pr.Code,
--jan
SUM(case when pr.RevDate = '1/1/07' then pr.CommissionableRevenue end) as JanCommis,--
SUM(case when pr.RevDate = '1/1/07' then s.[Jan TBR] end) as JanRev, -- for month revenue occurred
SUM(case when pr.RevDate = '1/1/07' then ROUND((pr.CommissionableRevenue - s.[Jan TBR]),2) end) as JanDiff, --totals
--feb
SUM(case when pr.RevDate = '2/1/07' then pr.CommissionableRevenue end) as FebCommis,
SUM(case when pr.RevDate = '2/1/07' then s.[Feb TBR] end) as FebRev, -- for month revenue occurred
SUM(case when pr.RevDate = '2/1/07' then ROUND((pr.CommissionableRevenue - s.[Feb TBR]),2) end)
FROM #PreRev pr
INNER JOIN smcsarchive..CEM_SepYTD_TBRbyBranch s ON s.brcode = pr.Code
WHERE pr.RevDate in ('1/1/07', '2/1/07') --for month running report, 2007
GROUP BY pr.Code
ORDER BY pr.Code
November 29, 2007 at 8:20 am
Yay! 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply