SQL Logic

Question

I have 2 tables

Table 1
ID	YEAR	Amount
111	2000	10
111	2001	20
222	2000	30
222	2001	40
Table 2
ID	Year	Sales
111	2000	10
111	2000	8
111	2000	4
111	2001	12
111	2001	18
111	2001	13
222	2000	30
222	2001	40
I like to get
ID	YEAR	Amount	Sales
111	2000	10	10
111	2001	20	18
222	2000	30	30
222	2001	40	40

Write know my query is

Select

a.amount,

b.sales ,

---case if a.amount = b.sales give sales for the same year

--case if a.amount <> b.sales give max sales for the same year

from table1 a inner join table2 b

on a.id =b.id and a.year =b.year

Any help will be appreciate .

 

---

simam

Answer 1

If you only need the max sales value for each ID and YEAR, try:

SELECT 
	t1.ID
	,t1.YEAR
	,t1.Amount
	,(SELECT MAX(Sales) 
		FROM dbo.Table2 AS t2 
		WHERE t2.ID = t1.ID 
			AND t2.YEAR = t1.YEAR) AS Sales
FROM dbo.Table1 AS t1
ORDER BY
	ID, YEAR;

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

Hi Simam,

The query would like:
select a.ID,a.Year,a.Amount,b.Sales
from table1 a inner join
(select ID,Year,MAX(Sales) as Sales from table2 group by ID,Year) b
on a.ID=b.ID and a.Year=b.Year

If you have any questions, please feel free to ask.

Regards,
Charlie Liao

If you have any feedback on our support, please click here.

---

Charlie Liao
TechNet Community Support

Answer 3

Dan/Charlie,

Thank you, but max will be my second case.

I would like to check first if a.amount = b.sales  and a.year = b.year give me that row

otherwise give me the max(sales).

First case
Table 1
ID	YEAR	Amount
111	2000	10
Table 2
ID	Year	Sales
111	2000	10
111	2000	20
111	2000	4
Out   come should be
ID	YEAR	Amount	Sales
111	2000	10	10
Second   case
Table 1
ID	YEAR	Amount
111	2001	20
Table 2
ID	Year	Sales
111	2001	12
111	2001	18
111	2001	13
Out   come should be
ID	YEAR	Amount	Sales
111	2001	20	18
Else
222	2000	30	30
222	2001	40	40

---

simam

Answer 4

Checkout this:

with cte as (
SELECT 
	t1.ID
	,t1.YEAR
	,t1.Amount
	,Sales
	,Case when t1.amount=t2.sales then 1 else 0 end compare 
	from table_1 t1 Join table_2 t2 
	on t2.YEAR = t1.YEAR
	and t1.id=t2.id
)
Select distinct a.id,a.year,a.amount,a.sales from cte a
where compare=1
Union all
SELECT distinct 
	t1.ID
	,t1.YEAR
	,t1.Amount
	,(SELECT MAX(Sales) 
		FROM cte AS t2 
		WHERE t2.ID = t1.ID 
			AND t2.YEAR = t1.YEAR
			and t2.sales<>t2.amount) AS Sales
FROM cte AS t1
Where cast(t1.id as varchar(4))+ cast(t1.year as varchar(4)) not in (select Cast(ID as varchar(4))+CAST(year as varchar(4)) from cte where compare=1)
order by ID,year

or

with cte as (
SELECT 
	t1.ID
	,t1.YEAR
	,t1.Amount
	,Sales
	,Case when t1.amount=t2.sales then 1 else 0 end compare 
	from table_1 t1 Join table_2 t2 
	on t2.YEAR = t1.YEAR
	and t1.id=t2.id
)
Select distinct a.id,a.year,a.amount,a.sales from cte a
where compare=1
Union all
select distinct a.ID,a.Year,a.Amount,b.Sales
from cte a inner join
(select ID,Year,MAX(Sales) as Sales from cte  group by ID,Year) b
on a.ID=b.ID and a.Year=b.Year
Where cast(a.id as varchar(4))+ cast(a.year as varchar(4)) not in (select Cast(ID as varchar(4))+CAST(year as varchar(4)) from cte where compare=1)
order by ID,year

Regards,Eshwar.

---

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

Answer 5

Thank you both of you, but for some reason not able to make it work.

Here is my main query

SELECT
                 F8.MeF_ExtObjectID
                ,F8.Returntype
                 ,F8.TIG_TAXPRD
                 ,F.MeF_ExtObjectID
                 ,F.TaxPeriodEndDate
                 ,F8.DUP150_RID
                 ,F8.TransCode
                 ,F8.AmendedReturn
                 ,F8.SupersededReturn
                 ,F8.TIG_TAXPRD
                 ,Cast(F.ForeignTaxCredit_F1120 AS BIGINT) AS ForeignTaxCredit_F1120
                 ,Cast(F8.TotalForeignTaxCredit AS BIGINT) AS TotalForeignTaxCredit 
 
                From dbo.PotentialErrors_F1120 F Left JOIN dbo.Mef_DCS_F1118SchB_PartIII_New F8
                 ON F.MeF_ExtObjectID = F8.MeF_ExtObjectID
                WHERE F8.Source = 'MEF' AND F8.Returntype = 'C'
                AND F.AmendedReturn is null
                AND F1120FilerEIN = '1111111' AND F8.DUP150_RID = '1'

---

simam