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-23 : 08:32:05

Hey guys

I need some help,

I have created two queries, I need the results from the second query, attached to the first query via the hst_merchum

first query is

with cte as (
SELECT [hst_merchnum],

Case when year(hst_date_processed) = '2011' then SUM ([Net_Intg]) else 0 end as [2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Net_Intg]) else 0 end as [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]

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([2011]) as [2011 InterChange],SUM([2012]) as [2012 Interchange],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by hst_merchnum

Query two

with cte as (
SELECT [hst_merchnum],

Case when year(hst_date_processed) = '2011' then SUM ([Funding_Amt]) else 0 end as [2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Funding_Amt]) else 0 end as [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]

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([2011]) as [2011 Funding_Amt],SUM([2012]) as [2012 Funding_Amt],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by hst_merchnum

Looking forward to your response

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-23 : 09:02:16

why not just in one query ?

with cte as 
(
	SELECT 	[hst_merchnum],
		Case when year(hst_date_processed) = '2011' then SUM ([Net_Intg]) else 0 end as [NI_2011] ,
		Case when year(hst_date_processed) = '2012' then SUM ([Net_Intg]) else 0 end as [NI_2012] ,
		Case when year(hst_date_processed) = '2011' then SUM ([Funding_Amt]) else 0 end as [FA_2011] ,
		Case when year(hst_date_processed) = '2012' then SUM ([Funding_Amt]) else 0 end as [FA_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]
	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([NI_2011]) as [2011 InterChange],
	SUM([NI_2012]) as [2012 Interchange],
	SUM([NI_2012]) - SUM([NI_2011]) as [NI_Comparision],
	SUM([FA_2011]) as [2011 Funding_Amt],
	SUM([FA_2012]) as [2012 Funding_Amt],
	SUM([FA_2012]) - SUM([FA_2011]) as [FA_Comparision]
from 	cte
group by hst_merchnum

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-23 : 09:03:04

Because khtan

i am not that clever , and didnt think of that :P

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-23 : 09:04:52

no worry. You will next time

by the way, i didn't check the group by columns, hopefully they are the same for both of your query

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-23 : 09:17:51

Hi Khtan

My query now is

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]
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_Comparision],
SUM([Funding_2011]) as [2011 Funding_Amt],
SUM([Funding_2012]) as [2012 Funding_Amt],
SUM([Funding_2012]) - SUM([Funding_2011]) as [Funding_Comparision],
SUM([NetSales_2011]) as [2011 NetSales],
SUM([NetSales_2012]) as [2012 NetSales],
SUM([NetSales_2012]) - SUM([NetSales_2011]) as [NetSales_Comparision],
SUM([Scheme_2011]) as [2011 Scheme_Fees],
SUM([Scheme_2012]) as [2012 Scheme_Fees],
SUM([Scheme_2012]) - SUM([Scheme_2011]) as [Scheme_Comparision]
from cte
group by hst_merchnum

:)

Hopefully this should work, thank you for your support x

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-23 : 09:18:59

[code]
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]
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_2011]) - SUM([Interchange_2012]) as [Interchange_Comparision],
SUM([Funding_2011]) as [2011 Funding_Amt],
SUM([Funding_2012]) as [2012 Funding_Amt],
SUM([Funding_2012]) - SUM([Funding_2011]) as [Funding_Comparision],
SUM([NetSales_2011]) as [2011 NetSales],
SUM([NetSales_2012]) as [2012 NetSales],
SUM([NetSales_2012]) - SUM([NetSales_2011]) as [NetSales_Comparision],
SUM([Scheme_2011]) as [2011 Scheme_Fees],
SUM([Scheme_2012]) as [2012 Scheme_Fees],
SUM([Scheme_2012]) - SUM([Scheme_2011]) as [Scheme_Comparision]
from cte
group by hst_merchnum[/code]

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-23 : 09:29:55

HI Kthan

I spotted my mistake as soon as you posted

you might be able to help me with an other query though

The 2012 data i have is only up to august,
however the 2011 is the complete year. When i do my comparison it isn’t giving me a true reflection

i need to put something in that states

if i run the report today, also return the data up to that point in 2011

Eg
run report in sept, i need data up to sept 2011 to be summed up

run report in nov, i need data up to nov 2011 to be summed up

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-23 : 20:50:34

actually if you are only interested in current year and previous year, you should have put that condition in the WHERE clause.

FROM 	[FDMS].[dbo].[Fact_Financial_History]
WHERE    hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)

and for the criteria to sum up to the same period for comparison


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)

run this query, and you will understand what those dateadd/datediff will gives you

select	dateadd(year, datediff(year, 0, getdate()) - 1, 0),
	dateadd(year, -1, getdate()),
	dateadd(year, datediff(year, 0, getdate()), 0)

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 04:45:04

HI Khtan

thank you for your response

I am trying to understand your example but i am little confused.

I understand your using hst_date_processed as thats the column thats provides the dates

But how does your query return data from the previous year which relates to the report being run today

For eg

If i run the report in august 2012 it will look at jan-august 2012 data, i want it to sum from begining of jan 2011 to 2011 august. So i can get an exact comparison

Currently the query is summing up all of 2011 and the data i have for 2012, so 8months worth. Which isnt giving me a true reflection as i am missing a wuarter of the data for 2012, as i still have 4months left of the year.

No doubt your answer resolves my issues, but i just need it explained to me
So

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-24 : 04:52:31

if you run the report in Aug 24, 2012, this WHERE condition


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)

is actually


where	(
		hst_date_processed	>=	'2011-01-01'
	and	hst_date_processed	<=	'2011-08-24'
	)
or	hst_date_processed		>=	'2012-01-01'

so you will only get hst_date_processed that is between Jan 1, 2011 and Aug 24, 2011 AND also date that is 2012. Data that is between Aug 25, 2011 and Dec 31, 2011 is excluded. So when you sum() it up for 2011, you get the same no of month in 2011 as in 2012


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] ,

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 05:29:51

hi Khtan

I think i am being thick here,

As the report is going to be run adhoc i dont want to change the dates, which it looks like i would have to in your last where clause

Is there anyway to state that if i run the report in august 2012 , it pulls august 2011 also ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-24 : 05:32:20

quote:
Originally posted by masond

hi Khtan

I think i am being thick here,

As the report is going to be run adhoc i dont want to change the dates, which it looks like i would have to in your last where clause

Is there anyway to state that if i run the report in august 2012 , it pulls august 2011 also ?

this query actually does that. You don't need to hard code the date / value


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)

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-24 : 06:33:02

Hi Khtan

Will your post above apply for all my sums ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-24 : 06:48:01

yes

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources