Apply a function to all subqueries

1

Hi,

I have a 200 queries within one main query. Rather than use WHERE function to pull a certain month in each query, how can I add a statement at the top for it to be applied to all the queries?

e.g. As you can see the WHERE function, I only want data for a certain period. Rather than each query having a WHERE function to pull a certain date, what can I use so I am only using one conditional line to be applied to all the below queries?

--GL387903
drop table #GL387903
select lmf.[Lease_Key],
sum (Cash_Excess_Mileage_Fee_Amt * -1) as Excess_Mileage__Comm_387903
into #GL387903
from dm_servicing.Lease.Vw_Lease_Month_Extend_Fact lmf
join dm_servicing.dbo.vw_Portfolio_Channel_Dim pcd on lmf.Portfolio_Channel_Key = pcd.Portfolio_Channel_Key
join [EDW].[dbo].[Pool_Detail] le on le.[Pool_Key] = lmf.pool_key
Where lmf.Month_Key = 201505
and pcd.Finance_Channel_Desc in ('Chrysler Comm Fleet Lease Non Eligible', 'Chrysler Comm Fleet Lease Eligible')
and le.Effective_Thru_Date = '9999-12-31' and le.Owned_Ind = 'y'
group by lmf.[Lease_Key]
having sum (Cash_Excess_Mileage_Fee_Amt * -1) <> 0
order by lease_key

--GL387904
drop table #GL387904
select lmf.[Lease_Key],
sum (Cash_Wear_And_Tear_Amt * -1) as Cash_Wear_And_Tear_Amt__Comm_387904
into #GL387904
from dm_servicing.Lease.Vw_Lease_Month_Extend_Fact lmf
join dm_servicing.dbo.vw_Portfolio_Channel_Dim pcd on lmf.Portfolio_Channel_Key = pcd.Portfolio_Channel_Key
join [EDW].[dbo].[Pool_Detail] le on le.[Pool_Key] = lmf.pool_key
Where lmf.Month_Key = 201505
and pcd.Finance_Channel_Desc in ('Chrysler Comm Fleet Lease Non Eligible', 'Chrysler Comm Fleet Lease Eligible')
and le.Effective_Thru_Date = '9999-12-31' and le.Owned_Ind = 'y'
group by lmf.[Lease_Key]
having sum (Cash_Wear_And_Tear_Amt * -1) <> 0
order by lease_key

2

Can you do

select lmf.[Lease_Key],
sum (Cash_Excess_Mileage_Fee_Amt * -1) as Excess_Mileage__Comm_387903,
sum (Cash_Wear_And_Tear_Amt * -1) as Cash_Wear_And_Tear_Amt__Comm_387904,
...
into #MyTempTable
from dm_servicing.Lease.Vw_Lease_Month_Extend_Fact lmf
join dm_servicing.dbo.vw_Portfolio_Channel_Dim pcd on
...
Where lmf.Month_Key = 201505
and pcd.Finance_Channel_Desc in ('Chrysler Comm Fleet Lease Non Eligible', 'Chrysler Comm Fleet Lease Eligible')
and le.Effective_Thru_Date = '9999-12-31' and le.Owned_Ind = 'y'
group by lmf.[Lease_Key]

and then query that for rows where [Excess_Mileage__Comm_387903] <> 0 and so on?

Hopefully getting all the SUM()s in one single query, rather than re-querying the data repeatedly, will be like night & day performance-wise.

3

What I am trying to do is... "Where lmf.Month_Key = 201505" not having to manually change the month in each table. Rather than manually change lets say from "201505" to "201506" in each temp table, I can use one line of code at the very top to pull all "201506." I'll have 240 something temp tables so changing 240 something WHERE functions to "201506" will be a lot of work.

4

Forget having a gazillion temp tables,instead allow SQL to process all the months and fees in one statement.

declare @start_month int
declare @end_month int

set @start_month = 201501
set @start_month = 201512

select 
    Lease_Key, Month_Key, 
    case when Excess_Mileage__Comm * -1 = 0 then null else Excess_Mileage__Comm end as Excess_Mileage__Comm,
    case when Cash_Wear_And_Tear_Amt__Comm * -1 = 0 then null else Cash_Wear_And_Tear_Amt__Comm end as Cash_Wear_And_Tear_Amt__Comm
    --,...other_fees...
--into #GL_Fee_Amts
from (
    select lmf.[Lease_Key],
    lmf.Month_Key,
    sum (Cash_Excess_Mileage_Fee_Amt * -1) as Excess_Mileage__Comm,
    sum (Cash_Wear_And_Tear_Amt * -1) as Cash_Wear_And_Tear_Amt__Comm
    --,...other_fees...
    from dm_servicing.Lease.Vw_Lease_Month_Extend_Fact lmf
    join dm_servicing.dbo.vw_Portfolio_Channel_Dim pcd on lmf.Portfolio_Channel_Key = pcd.Portfolio_Channel_Key
    join [EDW].[dbo].[Pool_Detail] le on le.[Pool_Key] = lmf.pool_key
    Where lmf.Month_Key between @start_month and @end_month
    and pcd.Finance_Channel_Desc in ('Chrysler Comm Fleet Lease Non Eligible', 'Chrysler Comm Fleet Lease Eligible')
    and le.Effective_Thru_Date = '9999-12-31' and le.Owned_Ind = 'y'
    group by lmf.[Lease_Key], lmf.Month_Key
) as derived_table
order by Lease_Key, Month_Key
5

I understood that was your question, and you could replace "201505" with:

DECLARE @MyCutoffDate VARCHAR(6)
...
SELECT @MyCutoffDate = '201505'
...
Where lmf.Month_Key = @MyCutoffDate

but the issue here is not that you have the same parameter lots of time, it is that you should not be re-issuing basically the same query multiple times.

It might be that for the 240 temp tables you currently have there is more than one underlying table that you query (whereas in your example, and our replies, there was only one), but it would still be better to query once, if possible, to a single #TempTable and then go from there.

For example, if you have a SUM() from multiple tables, you could do:

SELECT 'DefinitionA' AS [SumName],
       SUM(MyColumnA)
FROM   MyTableA
UNION ALL
SELECT 'DefinitionB',
       SUM(MyColumnB)
FROM   MyTableB
UNION ALL
...

which will give you all the SUM()s in a single table which you can then use for further processing.

To SUM() Multiple Columns in a table then you need:

SELECT SUM(ColumnA) AS [SumA],
       SUM(ColumnB) AS [SumB],
...
FROM MyTable

and you will then need to use some additional code if you want to convert that into an Key/Value "flat" table (as per my example above). But it will be a lot faster and more efficient than querying the table multiple times