Joining Multiple Select Statements in SQL

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
65
Good afternoon,

I have a table for GET_CONF and GET_CXL.
DateGET_CONFGET_CXL
01-Jan-2011
02-Jan-2012
03-Jan-2029
04-Jan-2054
05-Jan-2061
06-Jan-2049
07-Jan-2036
08-Jan-2056
09-Jan-20109
10-Jan-20138

I have calculated a 7-day moving average for GET_CONF using the following:

VBA Code:
SELECT o.Date, o.GET_CONF, o.GET_CXL
(
SELECT avg(GET_CONF)
from Output i
Where i.Date <= o.date
and i.date >= (o.date - 6)
having count (*) >=7
) AS GET_CONF_AVG
FROM [Output] AS o;
All i want to do is have the 7-day average for GET_CXL added to the table.
I assume I have to perform an inner-join, but I continue to get an error.
I simply want to use the same formula and add the GET_CXL portion.

Thanks,
Mike
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,676
Office Version
2013
Platform
Windows
Just offhand I copied your formula for CONF and changed CONF to CXL. Does that not work?
I also added a comma at the end of the first line as it seems that was missing.
Code:
SELECT o.Date, o.GET_CONF, o.GET_CXL,
(
SELECT avg(GET_CONF)
from Output i
Where i.Date <= o.date
and i.date >= (o.date - 6)
having count (*) >=7
) AS GET_CONF_AVG,
(
SELECT avg(GET_CXL)
from Output i
Where i.Date <= o.date
and i.date >= (o.date - 6)
having count (*) >=7
) AS GET_CXL_AVG
FROM [Output] AS o;
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
65
I did it multiple times and got a syntax error every time. However your way works just fine. Don't know what I did wrong.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,102,690
Messages
5,488,285
Members
407,633
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top