April 30, 2012 at 11:50 am
I have two historical tables that I want to pull data from. I want to pull column A from table A and column B from table B. Both are showing entries for Country codes. Table B has multiple sources for each security, and we want to look at all of the entries for that specific security.
For example, in Table A, APPL might have a country code of US. In Table B, from import1, it might have a country code of US, from import2 it might have a country code of CA, and import3 might be NULL.
I know in order to have all of the information in one row, I'll probably have to pivot the information. The tricky part is, in both tables, it's possible to have multiple values for the same import, just different effective dates, so I want to only look at the max(effective_date).
April 30, 2012 at 12:11 pm
Really don't have enough information to really help. Please read the first article I reference below in my signature block for the information we need and how to post it.
April 30, 2012 at 12:54 pm
That makes sense, thanks for that post.
I'm trying to mostly just get a push in the right direction rather than get the answer. I don't learn well if I'm spoon fed 🙂
I think this is getting me in the right direction. The second table is definitely the smaller result set:
select * from
(
SELECT a.[security_alias]
,b.ticker
,max(a.[effective_date]) as 'date'
,a.[update_source]
,a.[nra_tax_country]
,'Master' as 'Source'
FROM [SECURITY].[dbo].[secmaster_history] a inner join
security.dbo.security_master b on a.security_alias = b.security_alias
where a.update_source not in('COMP_SEC','sa')
and a.nra_tax_country not in('US', 'UNITED STATES')
group by a.[update_source],a.[security_alias],a.[nra_tax_country], b.ticker
union
select a.security_alias,b.ticker, max(a.update_date) as 'date', a.update_source, a.country_of_risk,'Detail' as 'Source'
from security_master_detail_hist a inner join security.dbo.security_master b on a.security_alias = b.security_alias
where a.update_source not in('COMP_SEC','sa')
and a.country_of_risk is not null
and a.country_of_risk <> 'US'
group by a.update_source, a.security_alias, a.country_of_risk, b.ticker
--order by a.security_alias, date desc, a.update_source
) PIV
PIVOT
(
MAX(nra_tax_country) FOR update_source in ([SMFBB],[GENPOS],[GVACSA],[GVASMF],[PORCSA],[PORPOS],[PORSMF])
) AS chld
order by security_alias
April 30, 2012 at 1:02 pm
something along these lines?
select [DATA]
from TableA
where effectivedate = (select MAX(EffectiveDate) from TableA)
union all
select [DATA]
from TableB
where effectivedate = (select MAX(EffectiveDate) from TableB)
--EDIT: I didn't refresh before my reply when you added your sample query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply