Merge Two Querries to have only 1 output

Question

Post reply

Merge Two Querries to have only 1 output

bherbert

Old Hand

Points: 386
More actions
November 2, 2015 at 1:26 pm

#304826

We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.
Query 1:
set nocount on
select
case
when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 7 then convert(char(20),('001 000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 8 then convert(char(20),('001 00000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 9 then convert(char(20),('001 0000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 10 then convert(char(20),('001 000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 11 then convert(char(20),('001 00000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 12 then convert(char(20),('001 0000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 13 then convert(char(20),('001 000'+MICRACCTNUMBER))
end
from SAVINGS
where MICRACCTNUMBER is not null
and ProcessDate = (select
CONVERT(char(4),DATEPART(yyyy,getdate()-1))
+case
CONVERT(char(2),DATEPART(month,getdate()-1))
when '1' then '01'
when '2' then '02'
when '3' then '03'
when '4' then '04'
when '5' then '05'
when '6' then '06'
when '7' then '07'
when '8' then '08'
when '9' then '09'
else CONVERT(char(2),DATEPART(month,getdate()-1))
end +
case
CONVERT(char(2),DATEPART(day,getdate()-1))
when '1' then '01'
when '2' then '02'
when '3' then '03'
when '4' then '04'
when '5' then '05'
when '6' then '06'
when '7' then '07'
when '8' then '08'
when '9' then '09'
else CONVERT(char(2),DATEPART(day,getdate()-1))
end)
and CLOSEDATE is null
order by PARENTACCOUNT
Query 2:
set nocount on
select
case
when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 7 then convert(char(20),('001 000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 8 then convert(char(20),('001 00000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 9 then convert(char(20),('001 0000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 10 then convert(char(20),('001 000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 11 then convert(char(20),('001 00000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 12 then convert(char(20),('001 0000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 13 then convert(char(20),('001 000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 14 then convert(char(20),('001 00'+MICRACCTNUMBER))
end
from LOAN
where ProcessDate = (select
CONVERT(char(4),DATEPART(yyyy,getdate()-1))
+case
CONVERT(char(2),DATEPART(month,getdate()-1))
when '1' then '01'
when '2' then '02'
when '3' then '03'
when '4' then '04'
when '5' then '05'
when '6' then '06'
when '7' then '07'
when '8' then '08'
when '9' then '09'
else CONVERT(char(2),DATEPART(month,getdate()-1))
end +
case
CONVERT(char(2),DATEPART(day,getdate()-1))
when '1' then '01'
when '2' then '02'
when '3' then '03'
when '4' then '04'
when '5' then '05'
when '6' then '06'
when '7' then '07'
when '8' then '08'
when '9' then '09'
else CONVERT(char(2),DATEPART(day,getdate()-1))
end)
AND MICRACCTNUMBER is not null
and CLOSEDATE is null
order by PARENTACCOUNT
Again, the only difference is the Table the info is coming from...

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

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

g.britton SSChampion Points: 13863 More actions · Answer 1

you would need to use dynamic sql, since you can't pass a database name in a variable.

Gerald Britton, Pluralsight courses

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Sergiy

SSC Guru

Points: 110208

November 2, 2015 at 1:45 pm

#1837205

UNION ALL ?

_____________
Code for TallyGenerator

Michal-469362 Old Hand Points: 362 More actions · Answer 3

Why don't you just do this:

set nocount on

select q.[MICRACCTNUMBER]

from (

select [PARENTACCOUNT],'001 '+right('000000000000'+MICRACCTNUMBER,16) as [MICRACCTNUMBER]

from SAVINGS

where MICRACCTNUMBER is not null

and ProcessDate = CONVERT(varchar(8), GETDATE()-1, 112)

and CLOSEDATE is null

union all

select [PARENTACCOUNT],'001 '+right('000000000000'+MICRACCTNUMBER,16) as [MICRACCTNUMBER]

from LOAN

where ProcessDate = CONVERT(varchar(8), GETDATE()-1, 112)

AND MICRACCTNUMBER is not null

and CLOSEDATE is null

) as q

order by q.[PARENTACCOUNT]