SQL looks simple, but difficult inside

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL looks simple, but difficult inside

Author

Topic

jeetu78
Starting Member

3 Posts

Posted - 2014-03-12 : 02:32:40

I have a staging and final table. based on a date , when i left join with final table, it gives the new and common records.
But i am not getting the ones which exists in final ,but not in staging for that day.

--drop table dbo.staging

create table dbo.Staging
( currency varchar(10) ,
value numeric ( 10, 2) ,
dt datetime )

insert into dbo.Staging

select 'EUR ', 5000.33 ,' 2014-01-13 ' UNION
select 'YEN ', 40.53 ,' 2014-01-13' UNION
select 'LIRA ', 33.33 ,' 2014-01-13' UNION
select 'INR ', 358.0 ,' 2014-01-13' UNION
select 'EUR ', 4000.0 ,' 2014-02-23' UNION
select 'YEN ', 30.0 ,' 2014-02-23' UNION
select 'RUB ', 10.0 ,' 2014-02-23' UNION
select 'BKR ', 999.0 ,' 2014-02-23'

--drop table final
create table Final( Currency varchar(10) , Today_value numeric ( 10, 2) , LastUpdatedt datetime )
insert into final
select 'EUR', 5000.33 ,'2014-01-13' UNION
select 'INR', 358.0 ,'2014-01-13' UNION
select 'YEN',40.53 ,'2014-01-13' UNION
select 'LIRA', 33.33 ,'2014-01-13'

ON 2014-02-23

SELECT s.currency , s.value, s.dt
FROM staging s left join Final f on s.currency=f.currency
WHERE s.dt ='2014-02-23'

EUR,4000.0,'2014-02-23'
YEN,30.00,'2014-02-23'
RUB,10.0,'2014-02-23'
BKR, 999.0,'2014-02-23'

LIRA,33.33,'2014-02-23'
INR,358,'2014-02-23'

I want these additional green highlighted rows. But not getting.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-12 : 02:49:04

U can Insert Staging data is LIRA,33.33,'2014-01-13'
INR,358,'2014-02-13'

so how can u get LIRA,33.33,'2014-02-23'
INR,358,'2014-02-23' this data using join ..

Veera

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-12 : 02:49:16

because those 2 lines in yellow is of different date

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

jeetu78
Starting Member

3 Posts

Posted - 2014-03-12 : 02:53:32

Yes i know It is of different date. But I want them to be reported as missing from this date. Full outer join doesnt work here

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-12 : 02:56:23

[code]

declare @dDate as datetime
set @dDate='2014-02-23'

;with dateCTE
AS(
SELECT s.currency , s.value, s.dt , row_number() OVER(Partition by s.currency Order by s.dt desc) as rn
FROM staging s
WHERE s.dt <=@dDate)

SELECT s.currency
, s.value
, @dDate
FROM dateCTE as s
WHERE rn=1

[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-12 : 02:56:41

[code]
currency value (No column name)
BKR 999.00 2014-02-23 00:00:00.000
EUR 4000.00 2014-02-23 00:00:00.000
INR 358.00 2014-02-23 00:00:00.000
LIRA 33.33 2014-02-23 00:00:00.000
RUB 10.00 2014-02-23 00:00:00.000
YEN 30.00 2014-02-23 00:00:00.000
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources