inner join help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

inner join help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 08:23:13

hey guys

I need some help

This is the table

SELECT TOP 1000 [DriverId]
,[FDMSAccountNo]
,[Period]
,[Value]
,[Volume]
FROM [FDMS].[dbo].[Fact_ProcessingCost]

This is my query .

with cte as (
SELECT [FDMSAccountNo],

Case when year(Period)= '2011' then SUM ([Value]) else 0 end as [2011] ,
Case when year(Period) = '2012' then SUM ([Value]) else 0 end as [2012]

,[Period]
,[DriverId]
,[Value]
,[Volume]

FROM [FDMS].[dbo].[Fact_ProcessingCost]

group by [DriverId]
,[FDMSAccountNo]
,[Period]
,[Value]
,[Volume])

select FDMSAccountNo,SUM([2011]) as [2011 ProcessingCost],SUM([2012]) as [2012 ProcessingCost],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by FDMSAccountNo

How can i get this query, added onto this query below. Period is a date field 2011-08-01, which is the same as hst_date_processed.

with cte as
(
SELECT [hst_merchnum],
Case when year(hst_date_processed) = '2011' then SUM ([Net_Intg]) else 0 end as [Interchange_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Net_Intg]) else 0 end as [Interchange_2012] ,
Case when year(hst_date_processed) = '2011' then SUM ([Net_Sales]) else 0 end as [NetSales_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Net_Sales]) else 0 end as [NetSales_2012],
Case when year(hst_date_processed) = '2011' then SUM ([Scheme_Fees]) else 0 end as [Scheme_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Scheme_Fees]) else 0 end as [Scheme_2012],
Case when year(hst_date_processed) = '2011' then SUM ([Funding_Amt]) else 0 end as [Funding_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Funding_Amt]) else 0 end as [Funding_2012]
,[hst_prod_code]
,[hst_plan_code]
,[hst_date_processed]
,[hst_sales_amt]
,[hst_returns_amt]
,[Net_Sales]
,[hst_sales_tran]
,[hst_returns_tran]
,[Net_Trans]
,[hst_sales_icg]
,[hst_returns_icg]
,[Net_Intg]
,[Scheme_Fees]
,[Funding_Amt]
,[FDMS_SRG_KEY]
,[Period_Key]
,[Plan_Key]
,[ID]
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)
and hst_date_processed <= dateadd(year, -1, getdate()))
or hst_date_processed >= dateadd(year, datediff(year, 0, getdate()), 0)

group by hst_merchnum,
[hst_prod_code]
,[hst_plan_code]
,[hst_date_processed]
,[hst_sales_amt]
,[hst_returns_amt]
,[Net_Sales]
,[hst_sales_tran]
,[hst_returns_tran]
,[Net_Trans]
,[hst_sales_icg]
,[hst_returns_icg]
,[Net_Intg]
,[Scheme_Fees]
,[Funding_Amt]
,[FDMS_SRG_KEY]
,[Period_Key]
,[Plan_Key]
,[ID]
)
select hst_merchnum,
SUM([Interchange_2011]) as [2011 InterChange],
SUM([Interchange_2012]) as [2012 Interchange],
SUM([Interchange_2012]) - SUM([Interchange_2011]) as [Interchange_Comparison],
SUM([Funding_2011]) as [2011 Funding_Amt],
SUM([Funding_2012]) as [2012 Funding_Amt],
SUM([Funding_2012]) - SUM([Funding_2011]) as [Funding_Comparison],
SUM([NetSales_2011]) as [2011 NetSales],
SUM([NetSales_2012]) as [2012 NetSales],
SUM([NetSales_2012]) - SUM([NetSales_2011]) as [NetSales_Comparison],
SUM([Scheme_2011]) as [2011 Scheme_Fees],
SUM([Scheme_2012]) as [2012 Scheme_Fees],
SUM([Scheme_2012]) - SUM([Scheme_2011]) as [Scheme_Comparison]
from cte
group by hst_merchnum

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 08:41:04

i think the inner join should be something like

inner join
[FDMS].[dbo].[Fact_ProcessingCost] on FDMSAccountNo = hst_merchnum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 09:05:10

quote:
Originally posted by masond

i think the inner join should be something like

inner join
[FDMS].[dbo].[Fact_ProcessingCost] on [Period] = hst_merchnum

That does not seem right - Period seems like it is a date, date range, or something that indicates date range. hst_merchnum seems like that is an account number or other identifying number that indicates a merchant. You wouldn't/shouldn't join those.

In the final output of your first query, you have hst_merchnum column followed by a few aggregates, grouped by hst_merchnum. In the final output of the second query, you have FDMSAccountNo column followed by three aggregation columns. When you want to combine these, what are the columns in the combined output that you want to see?

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 09:10:49

hi Sunitabeck

Sorry for the mistake

FDMSAccountNo = hst_merchnum
period =hst_date_processed

this query is correct , and i need produces the exact layout and results

with cte as
(
SELECT [hst_merchnum],
Case when year(hst_date_processed) = '2011' then SUM ([Net_Intg]) else 0 end as [Interchange_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Net_Intg]) else 0 end as [Interchange_2012] ,
Case when year(hst_date_processed) = '2011' then SUM ([Net_Sales]) else 0 end as [NetSales_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Net_Sales]) else 0 end as [NetSales_2012],
Case when year(hst_date_processed) = '2011' then SUM ([Scheme_Fees]) else 0 end as [Scheme_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Scheme_Fees]) else 0 end as [Scheme_2012],
Case when year(hst_date_processed) = '2011' then SUM ([Funding_Amt]) else 0 end as [Funding_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Funding_Amt]) else 0 end as [Funding_2012]
,[hst_prod_code]
,[hst_plan_code]
,[hst_date_processed]
,[hst_sales_amt]
,[hst_returns_amt]
,[Net_Sales]
,[hst_sales_tran]
,[hst_returns_tran]
,[Net_Trans]
,[hst_sales_icg]
,[hst_returns_icg]
,[Net_Intg]
,[Scheme_Fees]
,[Funding_Amt]
,[FDMS_SRG_KEY]
,[Period_Key]
,[Plan_Key]
,[ID]
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)
and hst_date_processed <= dateadd(year, -1, getdate()))
or hst_date_processed >= dateadd(year, datediff(year, 0, getdate()), 0)

group by hst_merchnum,
[hst_prod_code]
,[hst_plan_code]
,[hst_date_processed]
,[hst_sales_amt]
,[hst_returns_amt]
,[Net_Sales]
,[hst_sales_tran]
,[hst_returns_tran]
,[Net_Trans]
,[hst_sales_icg]
,[hst_returns_icg]
,[Net_Intg]
,[Scheme_Fees]
,[Funding_Amt]
,[FDMS_SRG_KEY]
,[Period_Key]
,[Plan_Key]
,[ID]
)
select hst_merchnum,
SUM([Interchange_2011]) as [2011 InterChange],
SUM([Interchange_2012]) as [2012 Interchange],
SUM([Interchange_2012]) - SUM([Interchange_2011]) as [Interchange_Comparison],
SUM([Funding_2011]) as [2011 Funding_Amt],
SUM([Funding_2012]) as [2012 Funding_Amt],
SUM([Funding_2012]) - SUM([Funding_2011]) as [Funding_Comparison],
SUM([NetSales_2011]) as [2011 NetSales],
SUM([NetSales_2012]) as [2012 NetSales],
SUM([NetSales_2012]) - SUM([NetSales_2011]) as [NetSales_Comparison],
SUM([Scheme_2011]) as [2011 Scheme_Fees],
SUM([Scheme_2012]) as [2012 Scheme_Fees],
SUM([Scheme_2012]) - SUM([Scheme_2011]) as [Scheme_Comparison]
from cte
group by hst_merchnum

I just need to add this query below, on to the one above

with cte as (
SELECT [FDMSAccountNo],

Case when year(Period)= '2011' then SUM ([Value]) else 0 end as [2011] ,
Case when year(Period) = '2012' then SUM ([Value]) else 0 end as [2012]

,[Period]
,[DriverId]
,[Value]
,[Volume]

FROM [FDMS].[dbo].[Fact_ProcessingCost]

group by [DriverId]
,[FDMSAccountNo]
,[Period]
,[Value]
,[Volume])

select FDMSAccountNo,SUM([2011]) as [2011 ProcessingCost],SUM([2012]) as [2012 ProcessingCost],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by FDMSAccountNo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 10:27:50

its very much similar to what i suggested here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=178143


;with cte as 
(
SELECT [hst_merchnum],
SUM(Case when year(hst_date_processed) = '2011' then [Net_Intg] else 0 end) as [Interchange_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Intg] else 0 end) as [Interchange_2012] ,
SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Scheme_Fees] else 0 end) as [Scheme_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Scheme_Fees] else 0 end) as [Scheme_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Funding_Amt] else 0 end) as [Funding_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Funding_Amt] else 0 end) as [Funding_2012]
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)
and hst_date_processed <= dateadd(year, -1, getdate()))
or hst_date_processed >= dateadd(year, datediff(year, 0, getdate()), 0)
group by hst_merchnum
),
cte1 as (
SELECT [FDMSAccountNo],

SUM(Case when year(Period)= '2011' then [Value] else 0 end) as [2011] ,
SUM(Case when year(Period) = '2012' then SUM ([Value] else 0 end) as [2012] 
FROM [FDMS].[dbo].[Fact_ProcessingCost]

group by [FDMSAccountNo]
)

select c1.FDMSAccountNo,[2011] as [2011 ProcessingCost],
[Interchange_2011] as [2011 InterChange],
[Interchange_2012] as [2012 Interchange],
[Interchange_2012] - [Interchange_2011] as [Interchange_Comparison],
[Funding_2011] as [2011 Funding_Amt],
[Funding_2012] as [2012 Funding_Amt],
[Funding_2012] - [Funding_2011] as [Funding_Comparison],
[NetSales_2011] as [2011 NetSales],
[NetSales_2012] as [2012 NetSales],
[NetSales_2012] - [NetSales_2011] as [NetSales_Comparison],
[Scheme_2011] as [2011 Scheme_Fees],
[Scheme_2012] as [2012 Scheme_Fees],
[Scheme_2012] - [Scheme_2011] as [Scheme_Comparison],
[2012] as [2012 ProcessingCost],
[2012] - [2011] as [Comparision]
from cte c
inner join cte1 c1
on c1.FDMSAccountNo = c.hst_merchnum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 10:33:54

HI visakh16

Iam getting the error msg "Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'else'.

Any reason why ?

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 10:48:31

doesnt it have to be

cte1 as (
SELECT [FDMSAccountNo],

SUM(Case when year(Period)= '2011' then [Value] else 0 end) as [2011] ,
SUM(Case when year(Period) = '2012' then [Value] else 0 end) as [2012]
FROM [FDMS].[dbo].[Fact_ProcessingCost]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 10:54:38

One right bracket in Visakh's query -see in red below:

....
SELECT [FDMSAccountNo],

SUM(Case when year(Period)= '2011' then [Value] else 0 end) as [2011] ,
SUM(Case when year(Period) = '2012' then SUM ([Value]) else 0 end) as [2012] 
FROM [FDMS].[dbo].[Fact_ProcessingCost]

group by [FDMSAccountNo]
)

select c1.FDMSAccountNo,[2011] as [2011 ProcessingCost],
.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 10:59:30

quote:
Originally posted by masond

doesnt it have to be

cte1 as (
SELECT [FDMSAccountNo],

SUM(Case when year(Period)= '2011' then [Value] else 0 end) as [2011] ,
SUM(Case when year(Period) = '2012' then [Value] else 0 end) as [2012]
FROM [FDMS].[dbo].[Fact_ProcessingCost]

yep...that was a typo
you can renove the SUM inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 11:00:56

hey guys

Thank you for your help

I think the query should be

;with cte as
(
SELECT [hst_merchnum],
SUM(Case when year(hst_date_processed) = '2011' then [Net_Intg] else 0 end) as [Interchange_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Intg] else 0 end) as [Interchange_2012] ,
SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Scheme_Fees] else 0 end) as [Scheme_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Scheme_Fees] else 0 end) as [Scheme_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Funding_Amt] else 0 end) as [Funding_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Funding_Amt] else 0 end) as [Funding_2012]
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)
and hst_date_processed <= dateadd(year, -1, getdate()))
or hst_date_processed >= dateadd(year, datediff(year, 0, getdate()), 0)
group by hst_merchnum
),
cte1 as (
SELECT [FDMSAccountNo],

SUM(Case when year(Period)= '2011' then [Value] else 0 end) as [ProcessingCost_2011] ,
SUM(Case when year(Period) = '2012' then [Value] else 0 end) as [ProcessingCost_2012]
FROM [FDMS].[dbo].[Fact_ProcessingCost]

group by [FDMSAccountNo]
)

select c1.FDMSAccountNo,[ProcessingCost_2011] as [ProcessingCost_2011],

[Interchange_2011] as [2011 InterChange],
[Interchange_2012] as [2012 Interchange],
[Interchange_2012] - [Interchange_2011] as [Interchange_Comparison],
[Funding_2011] as [2011 Funding_Amt],
[Funding_2012] as [2012 Funding_Amt],
[Funding_2012] - [Funding_2011] as [Funding_Comparison],
[NetSales_2011] as [2011 NetSales],
[NetSales_2012] as [2012 NetSales],
[NetSales_2012] - [NetSales_2011] as [NetSales_Comparison],
[Scheme_2011] as [2011 Scheme_Fees],
[Scheme_2012] as [2012 Scheme_Fees],
[Scheme_2012] - [Scheme_2011] as [Scheme_Comparison],

[ProcessingCost_2012] as [ProcessingCost_2012],
[ProcessingCost_2012] - [ProcessingCost_2011] as [Comparision]
from cte c
inner join cte1 c1
on c1.FDMSAccountNo = c.hst_merchnum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 11:04:00

quote:
Originally posted by masond

hey guys

Thank you for your help

I think the query should be

;with cte as
(
SELECT [hst_merchnum],
SUM(Case when year(hst_date_processed) = '2011' then [Net_Intg] else 0 end) as [Interchange_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Intg] else 0 end) as [Interchange_2012] ,
SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Scheme_Fees] else 0 end) as [Scheme_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Scheme_Fees] else 0 end) as [Scheme_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Funding_Amt] else 0 end) as [Funding_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Funding_Amt] else 0 end) as [Funding_2012]
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)
and hst_date_processed <= dateadd(year, -1, getdate()))
or hst_date_processed >= dateadd(year, datediff(year, 0, getdate()), 0)
group by hst_merchnum
),
cte1 as (
SELECT [FDMSAccountNo],

SUM(Case when year(Period)= '2011' then [Value] else 0 end) as [ProcessingCost_2011] ,
SUM(Case when year(Period) = '2012' then [Value] else 0 end) as [ProcessingCost_2012]
FROM [FDMS].[dbo].[Fact_ProcessingCost]

group by [FDMSAccountNo]
)

select c1.FDMSAccountNo,[ProcessingCost_2011] as [ProcessingCost_2011],

[Interchange_2011] as [2011 InterChange],
[Interchange_2012] as [2012 Interchange],
[Interchange_2012] - [Interchange_2011] as [Interchange_Comparison],
[Funding_2011] as [2011 Funding_Amt],
[Funding_2012] as [2012 Funding_Amt],
[Funding_2012] - [Funding_2011] as [Funding_Comparison],
[NetSales_2011] as [2011 NetSales],
[NetSales_2012] as [2012 NetSales],
[NetSales_2012] - [NetSales_2011] as [NetSales_Comparison],
[Scheme_2011] as [2011 Scheme_Fees],
[Scheme_2012] as [2012 Scheme_Fees],
[Scheme_2012] - [Scheme_2011] as [Scheme_Comparison],

[ProcessingCost_2012] as [ProcessingCost_2012],
[ProcessingCost_2012] - [ProcessingCost_2011] as [Comparision]
from cte c
inner join cte1 c1
on c1.FDMSAccountNo = c.hst_merchnum

yep
hope it gives you required output

please keep in mind this will not return you ant accounts which are present in only one of cte

ie in processingcost but not in financialhistory etc
so if you want them also to be returned you need to use one of OUTER JOIN types like LEFT,RIGHT of FULL depending on your requirement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 11:05:17

hey guys please find attached images of query

before i used to return results , now i am returning blank, The only difference i can see if what i am grouping it by

[url=http://postimage.org/image/lbfhadrk7/]

[/url]

[url=http://postimage.org/image/63zhq0zpj/]

[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 11:07:18

are you sure you've account numbers present in same format in both the table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 11:24:46

In the first screen print
I am using hst_merchum

Second one it looks like it using fdmsaccountno

Fdmsaccountno and hst_merchum are the same

But Merchum is better to use

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 12:06:56

then my suggestion should work unless you've something else added up yourself!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources