joining 2 queries

How would i join these 2 queries:
QUERY 1:
SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
FROM FCST INNER JOIN
PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
PERIODS.PERIOD_DATE)
returns:
year | month | FCST
QUERY 2:
SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
AS Exits
FROM MOMA200F INNER JOIN
TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
TAGACGV1.CDTAG
GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy, DATEADD(yyyy, -
1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND DATEPART(mm,
MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
(MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV)
returns:
year | month | exits
What i should see is:
year | month | exits | FCST
Many thanks in advance
PS: If you can point me to some good resources for "complicated" queries, i
would be thankfull
EricSELECT Q1.Year, Q1.Month, Q2.Exits, Q1.FCST
FROM
(SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
FROM FCST INNER JOIN
PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
PERIODS.PERIOD_DATE)) Q1(year, Month, FCST)
INNER JOIN
(SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
AS Exits
FROM MOMA200F INNER JOIN
TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
TAGACGV1.CDTAG
GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy, DATEADD(yyyy, -
1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND DATEPART(mm,
MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
(MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV))
Q2(Year, Month, Exits)
ON Q1.Year = Q2.Year AND Q1.Month = Q2.Month
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"ericvdb" <eric.vdb@.gmail.com> wrote in message
news:O7A9qmNFFHA.2176@.TK2MSFTNGP15.phx.gbl...
> How would i join these 2 queries:
> QUERY 1:
> SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
> PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
> FROM FCST INNER JOIN
> PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
> GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
> PERIODS.PERIOD_DATE)
> returns:
> year | month | FCST
> QUERY 2:
> SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
> MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
> AS Exits
> FROM MOMA200F INNER JOIN
> TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
> TAGACGV1.CDTAG
> GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
> MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
> HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy,
> DATEADD(yyyy, -
> 1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
> DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND
> DATEPART(mm,
> MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
> (MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
> ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV)
> returns:
> year | month | exits
>
> What i should see is:
> year | month | exits | FCST
>
> Many thanks in advance
> PS: If you can point me to some good resources for "complicated" queries,
> i
> would be thankfull
> Eric
>|||Hi,
Thanks for the fast reply.
I tried your sollution but it throws an error:
Server: Msg 1033, Level 15, State 1, Line 23
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
Thanks
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:eWKELqNFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> SELECT Q1.Year, Q1.Month, Q2.Exits, Q1.FCST
> FROM
> (SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
> PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
> FROM FCST INNER JOIN
> PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
> GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
> PERIODS.PERIOD_DATE)) Q1(year, Month, FCST)
> INNER JOIN
> (SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
> MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
> AS Exits
> FROM MOMA200F INNER JOIN
> TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
> TAGACGV1.CDTAG
> GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
> MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
> HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy,
DATEADD(yyyy, -
> 1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
> DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND
DATEPART(mm,
> MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
> (MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
> ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV))
> Q2(Year, Month, Exits)
> ON Q1.Year = Q2.Year AND Q1.Month = Q2.Month
>
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "ericvdb" <eric.vdb@.gmail.com> wrote in message
> news:O7A9qmNFFHA.2176@.TK2MSFTNGP15.phx.gbl...
queries,
>|||Right. Remove the order by clause from the derived table and try again
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"ericvdb" <eric.vdb@.gmail.com> wrote in message
news:uN2BnIOFFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Thanks for the fast reply.
> I tried your sollution but it throws an error:
> Server: Msg 1033, Level 15, State 1, Line 23
> The ORDER BY clause is invalid in views, inline functions, derived tables,
> and subqueries, unless TOP is also specified.
> Thanks
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:eWKELqNFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> DATEADD(yyyy, -
> DATEPART(mm,
> queries,
>