Want to Group By Union Two Query

[Jonathan Woods]

Hi there,

I have no idea why the following TSQL does not work.Any Idea? I am
using SQL 2000 Server

CREATE PROCEDURE RptDailySummary2
@FromTxDate DATETIME,
@ToTxDate DATETIME
AS
BEGIN
SELECT TxDate,TxType,CurrencyName,SavBalance
FROM
(
(
SELECT dbo.Dateonly(TxDate) AS
TxDate,dbo.CurrencyName(CurrencyID)AS
CurrencyName,TxType,SUM(SavBalance) AS SavBalance
FROM ActiveLedger
GROUP BY CurrencyID,TxType,dbo.DateOnly(TxDate)
HAVING dbo.DateOnly(TxDate) BETWEEN
dbo.DateOnly(@FromTxDate) AND dbo.DateOnly(@ToTxDate)
)
UNION
( SELECT dbo.Dateonly(TxDate) AS
TxDate,dbo.CurrencyName(CurrencyID)AS
CurrencyName,TxType,SUM(SavBalance) AS SavBalance
FROM PassiveLedger
GROUP BY CurrencyID,TxType,dbo.DateOnly(TxDate)
HAVING dbo.DateOnly(TxDate) BETWEEN
dbo.DateOnly(@FromTxDate) AND dbo.DateOnly(@ToTxDate)
)
)
GROUP BY TxDate,CurrencyName,TxType
ORDER BY CurrencyName,TxType
END

 

[Marc Gravell]

You may wish to try microsoft.public.sqlserver.programming

Marc

 

[Laura T.]

From what I remember, T-SQL syntax needs a name, alias, for the joined
query, like as Table1:

SELECT TxDate,TxType,CurrencyName,SavBalance
FROM
(
(
SELECT dbo.Dateonly(TxDate) AS
TxDate,dbo.CurrencyName(CurrencyID)AS
CurrencyName,TxType,SUM(SavBalance) AS SavBalance
FROM ActiveLedger
GROUP BY CurrencyID,TxType,dbo.DateOnly(TxDate)
HAVING dbo.DateOnly(TxDate) BETWEEN
dbo.DateOnly(@FromTxDate) AND dbo.DateOnly(@ToTxDate)
)
UNION
( SELECT dbo.Dateonly(TxDate) AS
TxDate,dbo.CurrencyName(CurrencyID)AS
CurrencyName,TxType,SUM(SavBalance) AS SavBalance
FROM PassiveLedger
GROUP BY CurrencyID,TxType,dbo.DateOnly(TxDate)
HAVING dbo.DateOnly(TxDate) BETWEEN
dbo.DateOnly(@FromTxDate) AND dbo.DateOnly(@ToTxDate)
)
) AS UnionTable
GROUP BY TxDate,CurrencyName,TxType
ORDER BY CurrencyName,TxType