Union or Join or or

[bhanab]

Hello,

I am trying to join these two sql statments. I get the following error msg - Incorrect syntax near the keyword 'WHERE'. These statments work Independenantaly without the derived “AS T1” bit.

Can anyone help me to get this to work ?


SELECT DateAdd(Month, DateDiff(Month, 0, Event_Date), 0) AS [Event_Month],
COUNT(*) AS Total_Logged
FROM ([BE].[dbo].[SOE$]
WHERE Team = 'DS'
GROUP BY DateAdd(Month, DateDiff(Month, 0, Event_Date), 0)
ORDER BY [Event_Month]) AS T1

UNION

Select COUNT(*) AS Total_mailed
From ([BE].[dbo].[SOE$]
WHERE Team = 'DS'
AND Action like '%Mail%'
AND Action not like '%Call%') AS T2
GROUP BY DateAdd(Month, DateDiff(Month, 0, Event_Date), 0)
Order BY DateAdd(Month, DateDiff(Month, 0, Event_Date), 0)

[ttgirl]

I don't think UNION is the right command for what you're trying to accomplish. Can you provide some sample data and desired results so we can tell what you're trying to achieve?

[bhanab]

Hello ttgirl,

Tx for your reply. I managed to get it to work like this.


Select a.[Date], a.Totalpermonth, b.Totalpermonth
From (SELECT DateAdd(Month, DateDiff(Month, 0, Event_Date), 0) AS [Date],
COUNT(*) AS Totalpermonth
FROM [BE].[dbo].[SOE$]
WHERE Team = 'DS'
GROUP BY DateAdd(Month, DateDiff(Month, 0, Event_Date), 0)
) a, (SELECT DateAdd(Month, DateDiff(Month, 0, Event_Date), 0) AS [Date],
COUNT(*) AS Totalpermonth
FROM [BE].[dbo].[SOE$]
WHERE Team = 'DS'
And Action like '%Mail%'
AND Action not like '%Call%'
GROUP BY DateAdd(Month, DateDiff(Month, 0, Event_Date), 0)
) b
Where a.[Date] = b.[Date]
Order by date

The desired results:

Date Totalpermonth Totalpermonth
----------------------- ------------- -------------
2006-07-01 4650 3330
2006-08-01 3785 2684


I just have to fix up the names.