CODEWITHSUNDEEP
sql-server-2008
M. Rain
M. Rain

Reputation: 325

How to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008

I have a Table named #Temp in SQL Server 2008 like this

**Ticker    Fin_Yr  Qrtr_No Qrtr_Date   Total_Amt**
AAMRATECH   2012    Q1  9/30/2011   11.875521
AAMRATECH   2012    Q2  12/31/2011  48.622772
AAMRATECH   2012    Q3  3/31/2012   45.541563
AAMRATECH   2012    Q4  6/30/2012   58.854779
AAMRATECH   2013    Q1  9/30/2012   12.871006
AAMRATECH   2013    Q2  12/31/2012  31.21
AAMRATECH   2013    Q3  3/31/2013   50.82
ABBANK      2011    Q1  3/31/2011   627.580957
ABBANK      2011    Q2  6/30/2011   954.764193
ABBANK      2011    Q3  9/30/2011   1377.842964
ABBANK      2011    Q4  12/31/2011  1394.742598
ABBANK      2012    Q1  3/31/2012   502.130441
ABBANK      2012    Q2  6/30/2012   730.696567
ABBANK      2012    Q3  9/30/2012   1010.512829
ABBANK      2012    Q4  12/31/2012  1468.547778
ABBANK      2013    Q1  3/31/2013   205.86
ACI         2011    Q1  3/31/2011   55.231
ACI         2011    Q2  6/30/2011   43.39
ACI         2011    Q3  9/30/2011   108.702
ACI         2011    Q4  12/31/2011  238.113071
ACI         2012    Q1  3/31/2012   2.383
ACI         2012    Q2  6/30/2012   -168.315
ACI         2012    Q3  9/30/2012   -334.197
ACI         2012    Q4  12/31/2012  545.12
ACI         2013    Q1  3/31/2013   21.939

And I want a Result Like this

**Ticker    Growth_Amt**
AAMRATECH   11.59037295
ABBANK     -59.00268472
ACI         820.6462442

I can Solve this by this way

Select Ticker, MAX(qrtr_date) 'Date_1', CONVERT(Varchar(2),'') 'QrtrNo', CONVERT(Date,'') 'Date_2', CONVERT(float,0.00) 'Val1', CONVERT(float,0.00) 'Val2', 
CONVERT(float,0.00) 'Growth_Amt'
into #Get_Diff 
from #temp group by Ticker order by Ticker

Update #Get_Diff Set QrtrNo=(Select Qrtr_No from #temp where #temp.Qrtr_Date=#Get_Diff.Date_1 and #temp.Ticker=#Get_Diff.Ticker)
Update #Get_Diff Set Date_2=(Select MAX(Qrtr_Date) from #temp where #temp.Qrtr_Date<#Get_Diff.Date_1 and #temp.Ticker=#Get_Diff.Ticker and #temp.Qrtr_No=#Get_Diff.QrtrNo)
Update #Get_Diff set Val1=(Select Total_Amt from #temp where #temp.Ticker=#Get_Diff.Ticker and #temp.Qrtr_Date=#Get_Diff.Date_1)
Update #Get_Diff set Val2=(Select Total_Amt from #temp where #temp.Ticker=#Get_Diff.Ticker and #temp.Qrtr_Date=#Get_Diff.Date_2)
update #Get_Diff set Growth_Amt=(CASE WHEN ((CONVERT(float,(Val1-Val2))/ABS(Val2))*100) IS NULL THEN 0 ELSE ((CONVERT(float,(Val1-Val2))/ABS(Val2))*100) End)  Where Val2<>0
Select Ticker, Growth_Amt from #Get_Diff order by Ticker
drop table #Get_Diff
drop table #temp

Here, First I am getting the maximum qrtr_date of every Ticker and the corresponding Fin_Yr, Qrtr_No and Total Amt. Then Getting the same values for Previous Fin_Yr And Then Difference between this 2 Values

Is this possible to get the result within a single query? Thanks

This script can be used to create the #Temp table:

CREATE TABLE #Temp (
    Ticker varchar(50)
    ,Fin_Yr varchar(50)
    ,Qrtr_No varchar(50)
    ,Qrtr_Date datetime
    ,Total_Amt float
)

INSERT INTO #Temp VALUES('AAMRATECH', '2012', 'Q1', '9/30/2011', '11.875521')
INSERT INTO #Temp VALUES('AAMRATECH', '2012', 'Q2', '12/31/2011', '48.622772')
INSERT INTO #Temp VALUES('AAMRATECH', '2012', 'Q3', '3/31/2012', '45.541563')
INSERT INTO #Temp VALUES('AAMRATECH', '2012', 'Q4', '6/30/2012', '58.854779')
INSERT INTO #Temp VALUES('AAMRATECH', '2013', 'Q1', '9/30/2012', '12.871006')
INSERT INTO #Temp VALUES('AAMRATECH', '2013', 'Q2', '12/31/2012', '31.21')
INSERT INTO #Temp VALUES('AAMRATECH', '2013', 'Q3', '3/31/2013', '50.82')
INSERT INTO #Temp VALUES('ABBANK', '2011', 'Q1', '3/31/2011', '627.580957')
INSERT INTO #Temp VALUES('ABBANK', '2011', 'Q2', '6/30/2011', '954.764193')
INSERT INTO #Temp VALUES('ABBANK', '2011', 'Q3', '9/30/2011', '1377.842964')
INSERT INTO #Temp VALUES('ABBANK', '2011', 'Q4', '12/31/2011', '1394.742598')
INSERT INTO #Temp VALUES('ABBANK', '2012', 'Q1', '3/31/2012', '502.130441')
INSERT INTO #Temp VALUES('ABBANK', '2012', 'Q2', '6/30/2012', '730.696567')
INSERT INTO #Temp VALUES('ABBANK', '2012', 'Q3', '9/30/2012', '1010.512829')
INSERT INTO #Temp VALUES('ABBANK', '2012', 'Q4', '12/31/2012', '1468.547778')
INSERT INTO #Temp VALUES('ABBANK', '2013', 'Q1', '3/31/2013', '205.86')
INSERT INTO #Temp VALUES('ACI', '2011', 'Q1', '3/31/2011', '55.231')
INSERT INTO #Temp VALUES('ACI', '2011', 'Q2', '6/30/2011', '43.39')
INSERT INTO #Temp VALUES('ACI', '2011', 'Q3', '9/30/2011', '108.702')
INSERT INTO #Temp VALUES('ACI', '2011', 'Q4', '12/31/2011', '238.113071')
INSERT INTO #Temp VALUES('ACI', '2012', 'Q1', '3/31/2012', '2.383')
INSERT INTO #Temp VALUES('ACI', '2012', 'Q2', '6/30/2012', '-168.315')
INSERT INTO #Temp VALUES('ACI', '2012', 'Q3', '9/30/2012', '-334.197')
INSERT INTO #Temp VALUES('ACI', '2012', 'Q4', '12/31/2012', '545.12')
INSERT INTO #Temp VALUES('ACI', '2013', 'Q1', '3/31/2013', '21.939')

Upvotes: 2

Views: 4625

Answers (2)

Andriy M
Andriy M

Reputation: 77737

Something like this might do as well:

  1. Rank the rows per Ticker.

  2. Get the subset of rows with the rankings of 1.

  3. Join the subset with the original set on the condition: same Ticker, same quarter, but last financial year.

  4. Calculate the Growth Amount.

My implementation of the above would be this:

WITH ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY Qrtr_Date DESC)
  FROM #Temp
)
, joined AS (
  SELECT
    r.Ticker,
    Growth_Amt = (r.Total_Amt - t.Total_Amt) * 100 / NULLIF(ABS(t.Total_Amt), 0)
  FROM ranked r
    INNER JOIN #Temp t ON r.Ticker  = t.Ticker
                      AND r.Qrtr_No = t.Qrtr_No
                      AND r.Fin_Yr  = t.Fin_Yr + 1
  WHERE r.rnk = 1
)
SELECT *
FROM joined
;

Upvotes: 1

TheQ
TheQ

Reputation: 7027

Now i had some time to spare. Here's one solution. It's not very pretty, but it's one single statement, and according to the execution plan it performs much better.

; WITH part1 AS (
    SELECT
        main.Ticker
        ,MAX(main.qrtr_date) AS Date_1
    FROM
        #Temp AS main
    GROUP BY
        Ticker
), part2 AS (
    SELECT
        part1.*
        ,(
            SELECT TOP 1
                Qrtr_No 
            FROM
                #Temp AS sub 
            WHERE
                sub.Ticker = part1.Ticker 
                and sub.Qrtr_Date = part1.Date_1
        ) AS QrtrNo
    FROM
        part1
), part3 AS (
    SELECT
        part2.*
        ,(
            SELECT
                MAX(Qrtr_Date)
            FROM
                #Temp AS sub
            WHERE 
                sub.Ticker = part2.Ticker 
                and sub.Qrtr_Date < part2.Date_1 
                and sub.Qrtr_No = part2.QrtrNo
        ) AS Date_2
    FROM
        part2
), part4 AS (
    SELECT
        part3.*
        ,(
            SELECT
                Total_Amt
            FROM
                #Temp AS sub
            WHERE
                sub.Ticker = part3.Ticker
                and sub.Qrtr_Date = part3.Date_1
        ) AS Val1
        ,(
            SELECT
                Total_Amt
            FROM
                #Temp AS sub
            WHERE
                sub.Ticker = part3.Ticker 
                and sub.Qrtr_Date = part3.Date_2
        ) AS Val2
    FROM
        part3
), part5 AS (
    SELECT
        part4.*
        ,(
            CASE
                WHEN Val2 = 0 
                THEN NULL 
                WHEN ((CONVERT(float,(Val1-Val2))/ABS(Val2))*100) IS NULL
                THEN 0 
                ELSE ((CONVERT(float,(Val1-Val2))/ABS(Val2))*100) 
            END
        ) AS Growth_Amt
    FROM
        part4
)
SELECT Ticker, Growth_Amt
FROM part5
ORDER BY Ticker

As you can see, I have used common table expressions to do the query one step at a time, pretty much exactly the same way as your updates.

When I run this query in the same batch as yours, the query cost is 16% relative to the batch.

Upvotes: 1

Related Questions