By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,775 Members | 1,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,775 IT Pros & Developers. It's quick & easy.

Want to Group By Union Two Query

P: n/a
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

Jan 30 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You may wish to try microsoft.public.sqlserver.programming

Marc
Jan 30 '07 #2

P: n/a
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

"Jonathan Woods" <ye******@gmail.comha scritto nel messaggio
news:11*********************@a75g2000cwd.googlegro ups.com...
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

Jan 30 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.