|
|
Posted by Andrew Tatum on 10/30/06 21:45
Sorry that I do not use CAST. Convert works for the time being. I don't
consider myself to be "advanced" by any means, however, I don't think I
need some "basic training." Here is a look at the entire query, as it
may help you understand it easier.
DECLARE @startdate smalldatetime, @Merchant varchar, @NonMerchant
varchar
SET @startdate = '10/01/2006'; // Start Date
SET @Merchant = '1,3,4,5'; // Includes Active, Out of Business, Cease
to do business, Inactive
SET @NonMerchant = '2,6'; // Includes Inhouse, School Messengers
SELECT [Merchant],
[University],
CONVERT(Varchar, [Month]) + '/' + CONVERT(Varchar, [Year])
[SendOn],
SUM([Total Scheduled]) [Total Scheduled],
SUM([Total Rejected]) [Total Rejected],
SUM([Toal Received]) [Toal Received],
[Enrollment Message?]
FROM (
SELECT
MessageInfo.Advertiser [Merchant],
MessageInfo.Name [University],
DATEPART(month, MessageInfo.SendOn) [Month],
DATEPART(year, MessageInfo.SendOn) [Year],
MessageInfo.Total [Total Scheduled],
MessageInfo.Refused [total rejected],
(MessageInfo.Total - MessageInfo.Refused) [Toal Received],
CASE WHEN AdMessageRequests.IsEnrollmentMessage = 1 THEN
'Yes'
ELSE 'No'
END [Enrollment Message?]
FROM MessageInfo
JOIN AdMessageRequests ON AdMessageRequests.MessageId =
MessageInfo.id
JOIN Advertisers ON Advertisers.id = MessageInfo.AdvertiserId
JOIN AdvertiserTypes ON Advertisers.AdvertiserTypeId =
AdvertiserTypes.Id
WHERE MessageInfo.SendON < @startdate
AND Advertisers.AdvertiserTypeId IN @Merchant
) mess
GROUP By [Merchant], [University], mess.[Month], mess.[Year],
[Enrollment Message?]
ORDER BY [Merchant], [University], mess.[Year], mess.[Month],
[Enrollment Message?]
Basically, I am trying to avoid this:
WHERE AdminAdvertiserList.AdvertiserTypeId IN (
1, // (Name: Active)
// 2, // (Name: In-House)
3, // (Name: Out of Business)
4, // (Name: Cease to do Business)
5 // (Name: Inactive)
// 6, // (Name: School Messenger)
)
I have to comment things out all the time. It really makes no sense...
and it causes a lot of human error.
Navigation:
[Reply to this message]
|