Click here to Skip to main content
15,891,136 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two table XYZ and ABC

create table #abc(id int, value int, dates date)
create table #xyz(id int, value int, dates date)

insert into #xyz values(1,10,'2017/02/01')
Insert into #xyz values(2,10,'2017/05/01')

Insert into #abc values(1,10,'2017/01/01')
Insert into #abc values(1,20,'2017/02/01')
Insert into #abc values(2,4,'2017/01/01')
Insert into #abc values(2,5,'2017/04/01')

XYZ
ID	Value	Dates
1	10	2017/02/01
2	10	2017/05/01

ABC			
ID	Values	Dates	ExpectedResult
1	10	2017/01/01	20
1	20	2017/02/01	0
2	4	2017/01/01	0
2	5	2017/04/01	15

i want to check if table ABC having (Month - 1 ) value comparing with table XYZ.
Uniqueness of table is identified by ID and Date  .
For eg-> Table XYZ  id-1 is having date 2017/02/01 i.e February data and my ABC table ID-1 is having 2017/01/01 i.e January data  then XYZ value + ABC value i.e 20 is the expected result.

Could you please tell me how do we compare month in this scenario. 


What I have tried:

I am not getting how to implement this in sql
Posted
Updated 29-Jan-18 1:08am
Comments
[no name] 29-Jan-18 5:08am    
Your first example seems good.What about second row of ABC.It has an ID 1 as wells and same for ID 2.Please describe all the possibilities.

1 solution

This Could be possible solution

SQL
Select * from #abc a,#xyz b where a.id =b.id and MONTH (a.dates) =month(b.dates) -1
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900