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 RptDailySummary 2
@FromTxDate DATETIME,
@ToTxDate DATETIME
AS
BEGIN
SELECT TxDate,TxType,C urrencyName,Sav Balance
FROM
(
(
SELECT dbo.Dateonly(Tx Date) AS
TxDate,dbo.Curr encyName(Curren cyID)AS
CurrencyName,Tx Type,SUM(SavBal ance) AS SavBalance
FROM ActiveLedger
GROUP BY CurrencyID,TxTy pe,dbo.DateOnly (TxDate)
HAVING dbo.DateOnly(Tx Date) BETWEEN
dbo.DateOnly(@F romTxDate) AND dbo.DateOnly(@T oTxDate)
)
UNION
( SELECT dbo.Dateonly(Tx Date) AS
TxDate,dbo.Curr encyName(Curren cyID)AS
CurrencyName,Tx Type,SUM(SavBal ance) AS SavBalance
FROM PassiveLedger
GROUP BY CurrencyID,TxTy pe,dbo.DateOnly (TxDate)
HAVING dbo.DateOnly(Tx Date) BETWEEN
dbo.DateOnly(@F romTxDate) AND dbo.DateOnly(@T oTxDate)
)
)
GROUP BY TxDate,Currency Name,TxType
ORDER BY CurrencyName,Tx Type
END

[Marc Gravell]

You may wish to try microsoft.publi c.sqlserver.pro gramming

Marc

[Laura T.]

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

SELECT TxDate,TxType,C urrencyName,Sav Balance
FROM
(
(
SELECT dbo.Dateonly(Tx Date) AS
TxDate,dbo.Curr encyName(Curren cyID)AS
CurrencyName,Tx Type,SUM(SavBal ance) AS SavBalance
FROM ActiveLedger
GROUP BY CurrencyID,TxTy pe,dbo.DateOnly (TxDate)
HAVING dbo.DateOnly(Tx Date) BETWEEN
dbo.DateOnly(@F romTxDate) AND dbo.DateOnly(@T oTxDate)
)
UNION
( SELECT dbo.Dateonly(Tx Date) AS
TxDate,dbo.Curr encyName(Curren cyID)AS
CurrencyName,Tx Type,SUM(SavBal ance) AS SavBalance
FROM PassiveLedger
GROUP BY CurrencyID,TxTy pe,dbo.DateOnly (TxDate)
HAVING dbo.DateOnly(Tx Date) BETWEEN
dbo.DateOnly(@F romTxDate) AND dbo.DateOnly(@T oTxDate)
)
) AS UnionTable
GROUP BY TxDate,Currency Name,TxType
ORDER BY CurrencyName,Tx Type

"Jonathan Woods" <ye******@gmail .comha scritto nel messaggio
news:11******** *************@a 75g2000cwd.goog legroups.com...

Hi there,

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

CREATE PROCEDURE RptDailySummary 2
@FromTxDate DATETIME,
@ToTxDate DATETIME
AS
BEGIN
SELECT TxDate,TxType,C urrencyName,Sav Balance
FROM
(
(
SELECT dbo.Dateonly(Tx Date) AS
TxDate,dbo.Curr encyName(Curren cyID)AS
CurrencyName,Tx Type,SUM(SavBal ance) AS SavBalance
FROM ActiveLedger
GROUP BY CurrencyID,TxTy pe,dbo.DateOnly (TxDate)
HAVING dbo.DateOnly(Tx Date) BETWEEN
dbo.DateOnly(@F romTxDate) AND dbo.DateOnly(@T oTxDate)
)
UNION
( SELECT dbo.Dateonly(Tx Date) AS
TxDate,dbo.Curr encyName(Curren cyID)AS
CurrencyName,Tx Type,SUM(SavBal ance) AS SavBalance
FROM PassiveLedger
GROUP BY CurrencyID,TxTy pe,dbo.DateOnly (TxDate)
HAVING dbo.DateOnly(Tx Date) BETWEEN
dbo.DateOnly(@F romTxDate) AND dbo.DateOnly(@T oTxDate)
)
)
GROUP BY TxDate,Currency Name,TxType
ORDER BY CurrencyName,Tx Type
END