Merge several codes to one code

Question

Merge several codes to one code

komal145

SSCrazy Eights

Points: 9918
More actions
March 15, 2018 at 2:58 pm

#333943

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

komal145 SSCrazy Eights Points: 9918 More actions · Answer 3

ChrisM@Work - Friday, March 16, 2018 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

Thank you so much . It worked 🙂