Databases
StackOverflow2013
Postsdbo
POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
    primarykey
    data
    text
    <p>I have a Table named #Temp in SQL Server 2008 like this</p> <pre><code>**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 </code></pre> <p>And I want a Result Like this</p> <pre><code>**Ticker Growth_Amt** AAMRATECH 11.59037295 ABBANK -59.00268472 ACI 820.6462442 </code></pre> <p>I can Solve this by this way</p> <pre><code>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&lt;#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&lt;&gt;0 Select Ticker, Growth_Amt from #Get_Diff order by Ticker drop table #Get_Diff drop table #temp </code></pre> <p>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 </p> <p>Is this possible to get the result within a single query? Thanks</p> <p>This script can be used to create the #Temp table:</p> <pre><code>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') </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTheQ
    1. USM. Rain
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
      1. USM. Rain
      1. VTFavorite
    1. COhow did you calculate `Growth_Amt`?
      singulars
      1. POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
      1. USJohn Woo
    2. CO@JW웃 ((CONVERT(float,(Val1-Val2))/ABS(Val2))*100)
      singulars
      1. POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
      1. USM. Rain
    3. COAdded the script for creating the #temp-table, so people can try to help you a bit easier. I don't have the time right now, sorry.
      singulars
      1. POHow to Get a Single SQL Query From Complex Query with Temp Table in SQL Server 2008
      1. USTheQ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload