March 26, 2013 at 6:10 am
Hello All
I have below 3 sql queries which gives results on order by accounts,
Now the question is how to shows 1strecord from first query, then after that second query results (if sameaccount no exists) then third query results (if same account no exists),
so the expected results willshows like it is coming from single query but it will use 3 queries
Now rsults look like below
First Query Results
103455,3000,'Clev',200,'14/3/2012','Sr Surgant Gen','16/12/2012','pention premium'
103457,2000,'Silv',990,'13/3/2012','Cye Mers','17/11/2012','RollOver'
103461,1400,'Clev',110,'23/3/2012','Prince Chau','12/11/2012','Savings'
103462,5500,'Disl',450,'30/3/2012','NULL','12/10/2012','pention premium'
103463,3770,'Goat',220,'17/3/2012','Manhodu Kim','19/11/2012','Benefits'
103464,3890,'Perk',320,'26/3/2012','Silvra Gen','10/12/2012','pention premium'
Second Query Results
103455,'ZZZ',2000,5.54,'Y','14/3/2014','Active'
103463,'YYY',3000,3.54,'N','17/3/2014','Active'
103464,'XXX',5000,6.45,'N','16/5/2014','Active'
103464,'XXX',4000,3.45,'Y','16/3/2014','Active'
Third Query Results
103455,'ZZZ',2000,5.54,'Y','14/3/2012','INActive'
103457,'YYY',3000,3.54,'N','17/3/2012','InActive'
103461,'XXX',5000,6.45,'N','16/5/2012','InActive'
103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'
103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'
expected Results is (just one results, break by acct No across the three queres as below)
Expected Results (One results)
103455,3000,'Clev',200,'14/3/2012','Sr Surgant Gen','16/12/2012','pention premium'
103455,'ZZZ',2000,5.54,'Y','14/3/2014','Active'
103455,'ZZZ',2000,5.54,'Y','14/3/2012','INActive'
103457,2000,'Silv',990,'13/3/2012','Cye Mers','17/11/2012','RollOver'
103457,'YYY',3000,3.54,'N','17/3/2012','InActive'
103461,1400,'Clev',110,'23/3/2012','Prince Chau','12/11/2012','Savings'
103461,'XXX',5000,6.45,'N','16/5/2012','InActive'
103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'
103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'
103462,5500,'Disl',450,'30/3/2012','NULL','12/10/2012','pention premium'
103463,3770,'Goat',220,'17/3/2012','Manhodu Kim','19/11/2012','Benefits'
103463,'YYY',3000,3.54,'N','17/3/2014','Active'
103464,3890,'Perk',320,'26/3/2012','Silvra Gen','10/12/2012','pention premium'
103464,'XXX',5000,6.45,'N','16/5/2014','Active'
103464,'XXX',4000,3.45,'Y','16/3/2014','Active'
Queries
--------
SELECT
AcctNo
GiaaBalance
Office,
totalbal
ItemDate
Contact
LastContacted
PilPlan
FROM SemerSouther order by AcctNo
SELECT
AcctNo
VirtualName,
Capbal,
VadareRate,
PilPlan,
LoadDate,
'Active' Status
FROM TableClevland where LoadDate > GETDATE() order by AcctNo
SELECT
AcctNo
VirtualName,
Capbal,
VadareRate,
PilPlan,
LoadDate
'INActive' as Status
FROM TableClevland where LoadDate <= GETDATE() order by AcctNo
Please assist me
Thanks in advance
Dhani
March 26, 2013 at 6:15 am
Looks like a homework question, but lookup UNION and UNION ALL in books online.
http://msdn.microsoft.com/en-US/library/ms180026(v=sql.105).aspx
HTH,
Rob
March 26, 2013 at 7:14 am
You'll also probably want to CAST your data types so your UNION doesn't blow up with data type conversions and add NULL to your select if you have one result set returning fewer columns than the others.
March 26, 2013 at 7:35 am
You really only need two queries for this. Your second and third queries can be easily combined into a single query like this.
SELECT
AcctNo
VirtualName,
Capbal,
VadareRate,
PilPlan,
LoadDate,
case
when LoadDate > getdate() then 'Active'
else 'Inactive'
end as Status
FROM TableClevland where LoadDate > GETDATE() order by AcctNo
As for combining the rest of the data it seems a bit bizarre to me. You are going to have balances and account names in a single column. Your datatypes are mismatched all over the place. What could you possible use this for?
_______________________________________________________________
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