January 31, 2014 at 5:42 pm
Hello There
Could you please help me here
how to write condition for self table year records, such 2012 name and acctno match with 2013 name and acctno then total, provided below,
create table #tab1 (MasterKey int, AcctNo varchar(12),name varchar(25), SumaofShares numeric, request_dat datetime )
--drop table #tab1
insert into #tab1 values (1000, 100,'Tom', 2500, '10/01/2012')
insert into #tab1 values (1001, 101,'Bat', 1550, '08/11/2012')
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
insert into #tab1 values (1003, 103,'Vat', 1750, '04/15/2012')
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
insert into #tab1 values (1011, 105,'Tim',500, '06/18/2013')
insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')
insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')
insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013')
insert into #tab1 values (1015, 103,'Vat',150, '03/18/2013')
insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013')
insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')
insert into #tab1 values (9999, 111,'AAA',110, '12/15/2014')
create table #tab2 (IssueKey int, totalOutstanding numeric, sharedBenefits varchar(1) )
--drop table #tab2
insert into #tab1 values (1000, 500, 'V')
insert into #tab1 values (1001, 150, 'U')
insert into #tab1 values (1002, 100, 'N')
insert into #tab1 values (1003, 170, 'U')
insert into #tab1 values (1010, 100, 'U')
insert into #tab1 values (1011, 200, 'K')
insert into #tab1 values (1012, 340, 'U')
insert into #tab1 values (1013, 560, 'N')
insert into #tab1 values (1014, 280, 'V')
insert into #tab1 values (1015, 150, 'V')
insert into #tab1 values (1016, 840, 'V')
insert into #tab1 values (1017, 530, 'N')
i would like to get 4 columns output
how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values please.,
MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key
so the request is
need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below
1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts UN Veriverted'
2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts Veriverted'
3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts NONVERT'
4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 last year accounts'
5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 This year accounts'
for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as
insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')
for ex 2)
insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')
for ex 4) 2012 records there is not match acctno and name in 2013 recods
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
for ex 5) 2013 records there is no match of name and acct no with 2012 records
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013')
insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013')
insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')
Expected Results (just for format)
AcctTypeDescription,SumofShares, OtotalutStand
'2012 and 2013 accts UN Veriverted',2700,234
'2012 and 2013 accts Veriverted' ,2890,234
'2012 and 2013 accts NONVERT' ,4533,325
'2012 last year accounts' ,2334,567
'2012 This year accounts' ,2222,877
Please
Thank you in advance
asita
January 31, 2014 at 8:33 pm
Could somebody help me,
any idea on how to join the same table to compare accountno and name for 2012 and 2013
Thanka ton in advance
Dhani
February 1, 2014 at 11:31 am
could you please help me here,
am i doing anything wrong here with this below query i am just getting null in first column
select
case
when a.MasterKey IS not null and (a.AcctNo <> b.AcctNo and a.name <> b.name) then '2012 Accounts'
when b.MasterKey IS not null and a.AcctNo <> b.AcctNo and a.name <> b.name then '2013 Accounts'
when (a.AcctNo = b.AcctNo and a.name = b.name) then case when sharedBenefits ='N' then 'accts NONVERT'
when sharedBenefits ='V' then 'accts Veriverted'
when sharedBenefits ='U' then 'accts UNVeriverted'
end +' 2012 and 2013 accts'
end ,
a.*,b.*
from
(
select * from #tab1 t1 join #tab2 t2 on t1.MasterKey = t2.IssueKey where year(t1.request_dat )=2012
) a full outer join (
select * from #tab1 t1 join #tab2 t2 on t1.MasterKey = t2.IssueKey where year(t1.request_dat )=2013
) b on a.MasterKey=b.IssueKey -- a.AcctNo = b.AcctNo and a.name = b.name
please help me
Thank you in advance
dhani
February 1, 2014 at 4:04 pm
Hello could anybody please assist with this
Thank you in advance
Please
February 1, 2014 at 4:29 pm
You're requirements are a bit difficult to understand and the code for the inserts for #Tab2 is broken. That may be the deterrent to most folks here, not to mention that it's the weekend.
Let's peel on potato at a time. You posted the following...
1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts UN Veriverted'
...{snip}...
for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as
insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')
Since the data you want returned is identical to the data in #Tab1 for 2013, are you simply saying...
"If there is a row for "TOM" and his account in both 2012 and 2013 AND the ShareBenefits column from #Tab2 according to the matching MaterKey/IssueKey for 2013 has a "U" in it , return the row for 2013"?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2014 at 3:32 am
asita (2/1/2014)
Hello could anybody please assist with thisThank you in advance
Please
Can you confirm which version of SQL Server you are using? Your code fails with an error (Ambiguous column name 'sharedBenefits') when run on SQL Server 2012.
This query:
SELECT
case
when a.MasterKey IS not null and a.AcctNo <> b.AcctNo and a.name <> b.name then '2012 Accounts'
when b.MasterKey IS not null and a.AcctNo <> b.AcctNo and a.name <> b.name then '2013 Accounts'
when (a.AcctNo = b.AcctNo and a.name = b.name) then case when sharedBenefits ='N' then 'accts NONVERT'
when sharedBenefits ='V' then 'accts Veriverted'
when sharedBenefits ='U' then 'accts UNVeriverted'
end +' 2012 and 2013 accts'
end ,
a.*,b.*
from
(
select * from #tab1 t1 join #tab2 t2 on t1.MasterKey = t2.IssueKey where year(t1.request_dat )=2012
) a
full outer join (
select * from #tab1 t1 join #tab2 t2 on t1.MasterKey = t2.IssueKey where year(t1.request_dat )=2013
) b on a.MasterKey=b.IssueKey -- a.AcctNo = b.AcctNo and a.name = b.name
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 2, 2014 at 4:04 am
Jeff Moden (2/1/2014)
Your requirements are a bit difficult to understand ...
Exceptionally difficult. Extending Jeff's request for clarification, here's a stab at something which may help to describe the problem.
SELECT
CASE
WHEN a.name = b.name AND a.sharedBenefits ='N' THEN 'accts NONVERT' + ' 2012 and 2013 accts'
WHEN a.name = b.name AND a.sharedBenefits ='V' THEN 'accts Veriverted' + ' 2012 and 2013 accts'
WHEN a.name = b.name AND a.sharedBenefits ='U' THEN 'accts UNVeriverted' + ' 2012 and 2013 accts'
WHEN a.name <> b.name THEN 'Unhandled, no match on name'
WHEN a.MasterKey IS not null THEN '2012 Accounts, no match on account to 2013'
WHEN b.MasterKey IS not null THEN '2013 Accounts, no match on account to 2012'
END,
a.*, '#' '#', b.*
FROM (
SELECT *
FROM #tab1 t1
JOIN #tab2 t2 on t1.MasterKey = t2.IssueKey
WHERE year(t1.request_dat )=2012
) a
FULL OUTER JOIN (
SELECT *
FROM #tab1 t1
JOIN #tab2 t2 on t1.MasterKey = t2.IssueKey
WHERE year(t1.request_dat )=2013
) b ON a.AcctNo = b.AcctNo
ORDER BY ISNULL(a.MasterKey, b.MasterKey)
Note that the join between last year's rows and this year's rows is now on AcctNo.
Have a good look at the results. Tell us how to convert the output of this query into your result set.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 2, 2014 at 10:59 am
Hello Good Morning,
Thank you for your input, it has bit issue with results, that is completely my wrong, i haven't explained well in my earlier post, sorry for this.,
i hope it is bit helpful, i provided exact results also
create table #tab1 (MasterKey int, AcctNo varchar(12),name varchar(25), SumaofShares numeric, request_dat datetime )
--drop table #tab1
insert into #tab1 values (1000, 100,'Tom', 2500, '10/01/2012')
insert into #tab1 values (1001, 101,'Bat', 1550, '08/11/2012')
insert into #tab1 values (1002, 103,'Vat', 1750, '04/15/2012')
insert into #tab1 values (1003, 102,'Kit', 1600, '06/12/2012') --- no match
insert into #tab1 values (1004, 104,'Sim',200, '04/21/2013') --- no match
insert into #tab1 values (1005, 105,'Tom',500, '06/18/2013') --- no match
insert into #tab1 values (1006, 100,'Tom',800, '08/22/2013')
insert into #tab1 values (1007, 103,'Vat',150, '03/18/2013')
insert into #tab1 values (1008, 101,'Bat',550, '09/15/2013')
insert into #tab1 values (9999, 111,'AAA',110, '12/15/2014') --- 2014 so no match
create table #tab2 (IssueKey int, totalOutstanding numeric, sharedBenefitsIndicator varchar(1) )
--drop table #tab2
insert into #tab2 values (1000, 500, 'U')
insert into #tab2 values (1001, 150, 'N')
insert into #tab2 values (1002, 100, 'V')
insert into #tab2 values (1003, 170, 'U')
insert into #tab2 values (1004, 100, 'N')
insert into #tab2 values (1005, 200, 'V')
insert into #tab2 values (1006, 340, 'U')
insert into #tab2 values (1007, 560, 'V')
insert into #tab2 values (1008, 280, 'N')
insert into #tab2 values (1017, 530, 'N')
to be clear more specific required
1) between tabe 1 and table 2 the key is Masterkey IssueKey
2) expected results will show max 5 records first column as below
'2012 and 2013 accts ' +
WHEN table2.SharedBenefitsIndicator = 'U' THEN 'UN Veriverted'
WHEN table2.SharedBenefitsIndicator = 'V' THEN THEN 'Veriverted'
WHEN table2.SharedBenefitsIndicator = 'N' THEN THEN 'NONVERTED'
'2012 last year accounts'
'2013 this year accounts'
3) for above detail, first three rows, include 2 results
(A) when data in tab1 has accountnumber and name matching with 2012 vs 2013 record + (tab2 conditions)
4) for 4 and 5 rows, to be simplified that are not falled under above 3 plus yesr (requestdate)
expect results Outcome (please execute below quest)
Select 'Segments', 'SumofShares' , 'TotalOutStandingShares'
union
select '2012 and 2013 accts UN Veriverted','3000','840'
union
select '2012 and 2013 accts Veriverted','1900','660'
union
select '2012 and 2013 accts NONVERTED','2100' ,'430'
union
select '2012 last year accounts','1600','170'
union
select '2013 this year accounts','800','300'
order by 1 desc
Please help me , i great full to you
Thank you in advance
dhani
February 2, 2014 at 11:48 am
Try this. The results are very close to your desired results set, and I can't tell if it's because the arithmetic in the query requires modification or if the desired result set is faulty.
;WITH t2012 AS (
SELECT x.[Year], AcctNo, sharedBenefits, SumaofShares, totalOutstanding
FROM #tab1 t1
JOIN #tab2 t2 on t1.MasterKey = t2.IssueKey
CROSS APPLY (SELECT [Year] = YEAR(Request_dat)) x
WHERE x.[Year] = 2012
),
t2013 AS (
SELECT x.[Year], AcctNo, sharedBenefits, SumaofShares, totalOutstanding
FROM #tab1 t1
JOIN #tab2 t2 on t1.MasterKey = t2.IssueKey
CROSS APPLY (SELECT [Year] = YEAR(Request_dat)) x
WHERE x.[Year] = 2013
)
SELECT
x.Segments,
SumaofShares = SUM(ISNULL(t2012.SumaofShares,0) + ISNULL(t2013.SumaofShares,0)),
totalOutstanding = SUM(ISNULL(t2012.totalOutstanding,0) + ISNULL(t2013.totalOutstanding,0))
FROM t2012
FULL OUTER JOIN t2013 ON t2013.AcctNo = t2012.AcctNo
CROSS APPLY (
SELECT Segments = CASE
WHEN t2013.[Year] IS NULL THEN '2012 last year accounts'
WHEN t2012.[Year] IS NULL THEN '2013 this year accounts'
WHEN t2012.sharedBenefits = 'N' THEN '2012 and 2013 accts NONVERTED'
WHEN t2012.sharedBenefits = 'U' THEN '2012 and 2013 accts UN Veriverted'
WHEN t2012.sharedBenefits = 'V' THEN '2012 and 2013 accts Veriverted'
ELSE NULL END
) x
GROUP BY x.Segments
ORDER BY x.Segments DESC
What does 'Veriverted' mean? A Google search returns only this thread.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 2, 2014 at 9:42 pm
Hello Chris,
Thank you very much, its the answer, I greatful to you,
Thank you to all, who tried to helped me, Thanks a ton
by the way it is VeryVoted a segment in my client(I just changed a little) sorry.,
Best Regards,
Dhani
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply