March 15, 2018 at 2:58 pm
I am trying to merge different sets of sql queries to one query and when i try to do , i shows incorrect count. Here is below code needed to merge to one code.
/******* Below sql has common Table but letterdate comes from different code , i wnant to name each logic as column and make it as one sql*****/
Select l.LoanNumber
,max(l.LetterDate) LetterDate
from tblLetterWriter l
where l.LetterID in('SW022', 'SW053')
group by l.LoanNumber
GO
select l.LoanNumber
,max(l.LetterDate) LetterDate
from tblLetterWriter l
where l.LetterID in('SW025', 'SW052')
group by l.LoanNumber
GO
select l.LoanNumber
,max(l.LetterDate) LetterDate
from tblLetterWriter l
where l.LetterID = 'SW046'
group by l.LoanNumber
GO
select l.LoanNumber
,max(l.LetterDate) LetterDate
from tblLetterWriter l
where l.LetterID = 'SW054'
group by l.LoanNumber
GO
select l.LoanNumber
,max(l.LetterDate) LetterDate
from tblLetterWriter l
where l.LetterID = 'SW052'
group by l.LoanNumber
GO
select l.LoanNumber
,max(l.LetterDate) LetterDate
from tblLetterWriter l
where l.LetterID = 'VN006'
group by l.LoanNumber
/***** I tried to do this but comes incorrect count *****/
Drop table #test
Select * into #test from (select *,CASE WHEN l.LetterID in('SW021', 'SW054') THEN LETTERDATE ELSE NULL END AS Dt_Flood_LPI_Notification_Letter1
,row_number() OVER (PARTITION BY L.LoanNumber Order by Letterdate desc) as Row
from dbo.tblLetterWriter l
) A where row=1
March 16, 2018 at 7:49 am
I don;t quite understand what you are trying to do. Please post table create statements and inserts for sample data (not production data) that would be representative of what you want to accomplish, and then the expected results based on that sample data. It's the best way to help us help you.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
March 16, 2018 at 8:38 am
SELECT l.LoanNumber,
MAX(CASE WHEN l.LetterID IN ('SW022', 'SW053') THEN l.LetterDate ELSE NULL END) LetterDate_SW022_SW053,
MAX(CASE WHEN l.LetterID IN ('SW025', 'SW052') THEN l.LetterDate ELSE NULL END) LetterDate_SW025_SW052,
MAX(CASE WHEN l.LetterID = 'SW046' THEN l.LetterDate ELSE NULL END) LetterDate_SW046,
MAX(CASE WHEN l.LetterID = 'SW054' THEN l.LetterDate ELSE NULL END) LetterDate_SW054,
MAX(CASE WHEN l.LetterID = 'SW052' THEN l.LetterDate ELSE NULL END) LetterDate_SW052,
MAX(CASE WHEN l.LetterID = 'VN006' THEN l.LetterDate ELSE NULL END) LetterDate_VN006
FROM tblLetterWriter l
WHERE l.LetterID IN ('SW022', 'SW053', 'SW025', 'SW052', 'SW046', 'SW054', 'SW052', 'VN006')
GROUP BY l.LoanNumber
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 16, 2018 at 12:58 pm
ChrisM@Work - Friday, March 16, 2018 8:38 AMSELECT l.LoanNumber,
MAX(CASE WHEN l.LetterID IN ('SW022', 'SW053') THEN l.LetterDate ELSE NULL END) LetterDate_SW022_SW053,
MAX(CASE WHEN l.LetterID IN ('SW025', 'SW052') THEN l.LetterDate ELSE NULL END) LetterDate_SW025_SW052,
MAX(CASE WHEN l.LetterID = 'SW046' THEN l.LetterDate ELSE NULL END) LetterDate_SW046,
MAX(CASE WHEN l.LetterID = 'SW054' THEN l.LetterDate ELSE NULL END) LetterDate_SW054,
MAX(CASE WHEN l.LetterID = 'SW052' THEN l.LetterDate ELSE NULL END) LetterDate_SW052,
MAX(CASE WHEN l.LetterID = 'VN006' THEN l.LetterDate ELSE NULL END) LetterDate_VN006
FROM tblLetterWriter l
WHERE l.LetterID IN ('SW022', 'SW053', 'SW025', 'SW052', 'SW046', 'SW054', 'SW052', 'VN006')
GROUP BY l.LoanNumber
Thank you so much . It worked 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply