Grouping Data for Consolidated Notification

We have a requirement where we need to send a single consolidated list of items belonging to a user which get active on particular date.

So, for example Item 1, Item 2, Item 3 gets active. These entries are inserted as Events. Now, we need to send single notification to the user with the email as:

=========================

Dear User,

Your following items got active today:

Item 1

Item 2

Item 3

Thanks,

Customer Care

=========================

For this we set the DigestDelievery to true and the emails indeed were consolidated. But, what it did was that it send single email notification repeating the entire content for each item as follows:

=========================

Dear User,

Your following items got active today:

Item 1

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 2

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 3

Thanks,

Customer Care

=========================

Please let me know if we are missing any setting or any changes need to be made in the .xslt for this to work.

regards,

Rajiv

I assume you are using a built-in XSLT formatter.

Since I have no idea about how to use/configure XSLT, in my application I created a custom content formatter in C# (this is really easy), and over there (in the .cs class) I "manually" built the resulting HTML string and inserted my items in a loop. The result HTML string is then sent as the email body.

Advantages:

1. I have 1 message "header" and 1 "footer". What's in between, gets "populated" at runtime in a loop, whether it's just 1 item or many.

2. Maybe, it's just as easy when using XSLT, ... I just don't know. But the emails my customers get contain hyperlinks which bring them right to the web page(s) for those particular item(s). Your management will love this feature!

3. Although I did not have to use this in my project, but if you need this, you can easily fetch additional data from some other non-NS data sources and "plug" it into your email message. It's possible because in a custom content formatter (C# or VB.NET, - your choice) you can use whatever .NET techniques you need, such as ADO.NET, System.IO (if you need to read from, say, some XML files), and whatever else you might want to do: a custom content formatter is just a regular .NET assembly, and you can use it as such.

|||Hi Rajiv -

From a SSNS perspective, it sounds like you have everything configured for digest delivery properly. Since it's not being formatted the way you wish, the issue is in the content formatter.

If you are using the built-in XSLT content formatter, try adjusting the
XSLTransform document.

Try placing the header and footer text directly in the XSLT document and the notification data in an <xsl:template> Match on "notification". You can then use teh <xsl:apply-templates> to call the notification section.

HTH...

Joe|||

Thanks for reply, I was able to resolve the issue as you have suggested.

regards,

Rajiv

Group Update of Prices

I am trying to write a SP that will update the price of products that are in a particular category.

I have 3 tables;
PRODUCT
- productID, price

CATEGORY
- categoryID, categoryName, parentID

PRODUCT_CATEGORY_MAP
- productID, categoryID

What I want to do, is allow admin to pass is a new price and categoryID, and it will update all the products that are within the supplied category.

I will start with:
UPDATE PRODUCT SET price = @.price WHERE categoryID = @.categoryID
however I do not have categoryID within the PRODUCT table

Do I place a SELECT statement after the WHERE?

DECLARE @.productID int
UPDATE PRODUCT SET price = @.price WHERE (SELECT @.productID = productID FROM PRODUCT_CATEGORY_MAP WHERE categoryID=@.categoryID)

Not sure if that would work or not or if I am on the right track.
Thanks for any help,
Mick

You just join in the other tables like this (given that you only need categoryID and that's in the map table, there is no need to join Category too, although you could if you were filtering on Category name for example)

UPDATE Product SET price = @.price
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID = @.categoryID

|||

Ok, I ended up figuring it out, but using IN instead of INNER JOIN. I like yours better.

So I want to extend this a little more...
Say I have the categories
ID Name ParentID
1 Cat1 0
2 Cat2 1

I have assigned a product to Cat2
productID categoryID
1 2

I now want my SP to work so that if a user selects the category Cat1, it will also update all child products.
I have a SP that gets all the child categories...
SELECT categoryID FROM Category
WHERE categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))

I have this so far, but it only updates categories in the selected category, not it's childs as well.
UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID IN
(SELECT categoryID FROM Category C
WHERE C.categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))
AND PCM.categoryID=@.categoryID)

Thanks again,
Mick

|||

UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id

--or

UPDATE Product SET ourUSDPrice = @.newPrice
Where Exists (Select ProductId From Product P INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id)

|||Mani has posted two ways to make your query work - do you have the GetChildren function working, so that it gets all the children at all levels? I wasn't sure from your post whether you were asking about that or not.

Group on particular date every year

Hi,
We have a load of personal records like (e.g.) these
Name, Function, Stardate,Enddate
Fred,assistent, div1, 1/1/2001, 31/12/2004
Richard,assistent, div2, 1/1/2001, 28/2/2003
Richard,director, div2, 1/3/2003, 31/7/2003
...
Now we need a matrix showing how many people works at date 1/7 of a
particular year and what function they had
2001 2002 2003 2004
ass dir ass dir ass dir ass dir
Div1 1 0 1 0 1 0 0 0
Div2 1 0 1 0 0 1 0 0
---
Total 2 0 2 0 1 1 0 0
The biggest problem I have is determining who is at 1/7 in what function and
group on that info.
Can somebody get me started? I'm trying to work with the IIF function but
with no good results.
Thak you very much
DanielDaniel,
If you aren't using SQL Server 2005, then this won't work. SQL Server
2005 introduced a function called PIVOT. Check out the SQL below to see
how it works.
SQL:
SELECT
div as Division, [2001-assistant], [2001-director], [2002-assistant],
[2002-director], [2003-assistant], [2003-director], [2004-assistant],
[2004-director]
FROM
(
SELECT div, '2001-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2001-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2001-01-07
00:00:00', 102))
UNION
SELECT div, '2002-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2002-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2002-01-07
00:00:00', 102))
UNION
SELECT div, '2003-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2003-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2003-01-07
00:00:00', 102))
UNION
SELECT div, '2004-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2004-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2004-01-07
00:00:00', 102))
) AS sourceTable
PIVOT
(count(name) FOR yearFunction IN
([2001-assistant],[2001-director],[2002-assistant],[2002-director],[2003-assistant],[2003-director],[2004-assistant],[2004-director]))
AS pivotTable
ORDER BY
div
Results: (formatting might get messed up because of word wrap)
Division 2001-assistant 2001-director 2002-assistant 2002-director
2003-assistant 2003-director 2004-assistant 2004-director
-- -- -- -- --
-- -- -- --
div1 1 0 1 0 1
0 1 0
div2 1 0 1 0 1
1 0 0
Hope your using 2005. Hope this helps.
-Josh
Daniel wrote:
> Hi,
> We have a load of personal records like (e.g.) these
> Name, Function, Stardate,Enddate
> Fred,assistent, div1, 1/1/2001, 31/12/2004
> Richard,assistent, div2, 1/1/2001, 28/2/2003
> Richard,director, div2, 1/3/2003, 31/7/2003
> ...
> Now we need a matrix showing how many people works at date 1/7 of a
> particular year and what function they had
> 2001 2002 2003 2004
> ass dir ass dir ass dir ass dir
> Div1 1 0 1 0 1 0 0 0
> Div2 1 0 1 0 0 1 0 0
> ---
> Total 2 0 2 0 1 1 0 0
> The biggest problem I have is determining who is at 1/7 in what function and
> group on that info.
> Can somebody get me started? I'm trying to work with the IIF function but
> with no good results.
> Thak you very much
> Daniel

group into 1 row

I have a query that I need to group the results for a particular client into
1 row. The problem is that I'm creating some columns on the fly using Case
statements, and the columns contain a Y or N as their data. Because of
this, I can't get the results in 1 row. I want to know if there is a way to
do this.
I've come up with a sample query that uses the Northwind db that will show
you what I'm trying to do. I'd like the results of the query below in 1
row. I know it doesn't make sense if you look at the result for the query
below, but it's just a representative example for what I'm doing.
select CategoryName,
Drink1 = Case ProductName when 'Chai' then 'Y' end,
Drink2 = Case ProductName when 'Chang' then 'Y' end
from [Products by Category]
where ProductName in ('chai','chang')
Thanks, Andresomething like this?
select CategoryName,
Drink1 = max(Case ProductName when 'Chai' then 'Y' end),
Drink2 = max(Case ProductName when 'Chang' then 'Y' end)
from [Products by Category]
where ProductName in ('chai','chang')
group by categoryName
I used max because the other value is only null. Use suitably for your issue
.
Hope this helps.|||Yes, exactly like that! Thanks.

GROUP by clause, /sub query problems

I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :
SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID

FROM (SELECT b.Deal_ID

FROM btransactions b

WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN

orders o ON o .Rep_ID = s.ID INNER JOIN

b ON o.ID = b.Deal_ID

GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID

HAVING (SUM(b.orderamount) > 0)
?
.NetSports

You have a space character following your "o" table alias throughoutyour query. Remove that extra space whereever it appears and seeif that clears up your problem.

GROUP BY and nText

Hi Everyone,

I was wondering if you could help with the following. Basically I have a database of Blog posts. On particular page say I want to list all the posts, easy. Now, if I want to list the posts but also have a column that also counts the number of comments left for that particular post (Comments stored in another table) then I can get this to work. The problem I am having is that the SQL I am using (below) fails if I include the column PostContent as its nText. I need it included as this contains the posts HTML. If I remove the PostContent from the SELECT and GROUP BY statements it works fine.

Any ideas on how to get this to work with PostContent included. Oh and I cant convert to varchar as it wont hold enough.

Code Snippet

SELECT P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent, Count(CommentId) AS TotalComments
FROM dbo.BlogPosts AS P LEFT OUTER JOIN dbo.BlogComments as C on P.PostId = C.PostId
WHERE P.UserId = 3
GROUP BY P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent
ORDER BY PostDate DESC

Cheers.

Hi,

why do you want to group them - you want to fetch all the blogs, right?

Simply remove grouping and add the count as subselect:

Code Snippet

SELECT P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent, (select Count(CommentId) from dbo.BlogComments as C where P.PostId = C.PostId ) AS TotalComments

FROM dbo.BlogPosts AS P

WHERE P.UserId = 3

ORDER BY PostDate DESC

If you want to show the last post grouped by user then this is another story ;-)

|||

If you use sql server 2005 then use the following query,

Code Snippet

SELECT

P.PostId

, P.UserId

, P.PostDate

, P.PostTitle

, Cast(P.PostContent as Nvarchar(Max)) as PostContent

, Count(CommentId) AS TotalComments

FROM

dbo.BlogPosts AS P

LEFT OUTER JOIN dbo.BlogComments as C on P.PostId = C.PostId

WHERE

P.UserId = 3

GROUP BY

P.PostId, P.UserId, P.PostDate, P.PostTitle, Cast(P.PostContent as Nvarchar(Max))

ORDER BY

PostDate DESC

|||

Thanks guys. Make sense now - I was totally going about it the wrong way. I have gone for Ivan's approach for simplicity.

Cheers

Group by across tables

I have two tables in one table callhist there records for every contact made to a particular prospect and the resulting disposition. In the other table (contacts) there are the records for each prospect containing information about the prospect.

I need to results like this.
Here are the columns I need to Display.

Campaign Name(from Contacts Table)
Dials (Select count(*) from calhist (based on parameters))
Contacts(Select count(*) from calhist (based on parameters))
Pres(Select count(*) from calhist (based on parameters))
Sales(Select count(*) from calhist (based on parameters))

and this is the way I would like it to be displayed

Campaigname|Dials|Contacts|Pres|Sales

and Help would be appreciated.

ThanksFirst you should create your select query, taking into account all the WHERE parametrised clauses, but leaving aside any grouping. Then, on top of this query you should contruct your groupping query, in a "select from select" fashion. In the end you should have something like that:

SELECT Sum(P.field1), Max(P.Field2),... P.KeyFieldx
FROM (SELECT field1, field2, ... KeyFieldx FROM table1 INNER JOIN table2 on... WHERE .... = [Your Param:]) P
Group by P.KeyFieldx

M.