December 13, 2007 at 9:09 am
I have this code in VS2008 and can format it using the datagrid, but when the the page is displayed in a sharepoint webpart, the commas are missing. So I figured that if I format the SQL query, it may fix my problem which leads me to another problem, everything I have tried doesn't work since I'm using the CASE statements and I'm not exactly knowledgable on the process...
Can anybody give me a push on how to wrap a user defined function or formatnumber to get the formatting I'm striving for? Most appreciated!
SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors],
COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END) AS [Returning Visitors],
COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) +
COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END) AS [Total Visits]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
The data is nothing more than a number in each of the 3 columns (without the commas):
New Visitors Returning Visitors Total Visits
53928 4327 58255
December 13, 2007 at 10:23 pm
Replace the 1234567890 with your INT column or formula. Could turn this into a function. Note that it's generally a bad idea to do this type of formatting in SQL... should be done in the app (if you have one).
SELECT LEFT(CONVERT(CHAR(16),CONVERT(MONEY,1234567890),1),13)
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2007 at 10:25 pm
This should do the trick.
SELECT
SUBSTRING(
CONVERT(varchar,
CAST(COUNT(DISTINCT
CASE WHEN visit_type = 0 THEN
visitor_id
END) AS money),1
),
1,
LEN(
CONVERT(
varchar,
CAST(COUNT(DISTINCT
CASE WHEN visit_type = 1 THEN
visitor_id
END) AS money),
1
)
) - 3
) AS [New Visitors],
SUBSTRING(
CONVERT(
varchar,
CAST(COUNT(DISTINCT
CASE WHEN visit_type = 1 THEN
visitor_id END) AS money),
1
),
1,
LEN(
CONVERT(
varchar,
CAST(COUNT(DISTINCT
CASE WHEN visit_type = 1 THEN
visitor_id END) AS money),
1
)
) - 3
) AS [Returning Visitors],
SUBSTRING(
CONVERT(
varchar,
CAST(COUNT(DISTINCT
CASE WHEN visit_type = 0 THEN
visitor_id
END) +
COUNT(DISTINCT
CASE WHEN visit_type = 1 THEN
visitor_id END)) AS money),
1
),
1,
LEN(
CONVERT(
varchar,
CAST(COUNT(DISTINCT
CASE WHEN visit_type = 0 THEN
visitor_id
END) +
COUNT(DISTINCT
CASE WHEN visit_type = 1 THEN
visitor_id
END)) AS money),
1
)
) - 3
) AS [Total Visits]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
**edited to fix horizontal scroll.
December 13, 2007 at 10:28 pm
hmm. Jeff it looks like you beat me to the punch.
It's based off the same principal but is much more simplistic. 😉
December 14, 2007 at 7:01 am
Jeff and Adam, thank-you for both of your responses, unfortunately, Adam's code did give me errors for some reason. May be my fault as I'm not to smart on SQL queries. The final code is below.
Jeff, I was doing the formatting in the VS08 application, in the DataView options using {0:#,#} in the DataFormatString, which works well in the aspx page, but for some reason, when you view the same page in a sharepoint webpart viewer, the formatting does not come through.
Doing the formatting in the original sql query, does seem to stick and is viewable in the final page in the sharepoint reporting page that I created. Thank-you to you both again for your time, I really appreciate your quick responses!
SELECT
LEFT(CONVERT(CHAR(16), CONVERT(MONEY, COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)), 1), 13)
AS [New Visitors],
LEFT(CONVERT(CHAR(16), CONVERT(MONEY, COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END)), 1), 13)
AS [Returning Visitors],
LEFT(CONVERT(CHAR(16), CONVERT(MONEY, COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)
+ COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END)), 1), 13)
AS [Total Visits]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
December 14, 2007 at 7:23 am
tthomas,
sorry about that I left some extra ")" in the last few case statements. Change
CASE WHEN visit_type = 1 THEN
visitor_id
END))
TO
CASE WHEN visit_type = 1 THEN
visitor_id
END)
in line 51 and 65.
December 14, 2007 at 7:41 am
Adam,
Thanks, you were correct, that made your code work as well! Again, I appreciate both of your responses! I have learned a lot! Happy Holidays!
Tommy
SELECT SUBSTRING(CONVERT(varchar, CAST(COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS money), 1), 1, LEN(CONVERT(varchar,
CAST(COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END) AS money), 1)) - 3) AS [New Visitors], SUBSTRING(CONVERT(varchar,
CAST(COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END) AS money), 1), 1, LEN(CONVERT(varchar,
CAST(COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END) AS money), 1)) - 3) AS [Returning Visitors], SUBSTRING(CONVERT(varchar,
CAST(COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) + COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END)
AS money), 1), 1, LEN(CONVERT(varchar, CAST(COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)
+ COUNT(DISTINCT CASE WHEN visit_type = 1 THEN visitor_id END) AS money), 1)) - 3) AS [Total Visits]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
December 14, 2007 at 7:48 am
December 14, 2007 at 8:12 am
And, thanks for the feedback!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2007 at 8:19 am
No problem on the feedback. I really appreciate those of you in the forums that take the time to help people that aren't as strong as the others in certain areas. I only hope that I can return the favor one day! Have a great day!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply