My understanding is that it doesn't matter whether you count all columns (*), a single column or a scalar value, the count returned is always the number of rows in...

Lynn - Looks like the third (hidden) line on my last post!

Interesting... Milliseconds bombs out on the same arithmetic overflow (which is useless anyway).

I guess there are too many seconds since the base date.

Fortunately minutes still works at least...

This rather curious solution returns the same results set (it is even sorted on YrQtr without using ORDER BY) and yields a slightly improved execution plan and faster elapsed time....

Found your problem interesting so I decide to convert your posted data to something consumable and came up with this.

DECLARE @d TABLE

(PeriodKey INT, Period VARCHAR(20), Amount MONEY)

INSERT INTO @d

SELECT...

Lynn:

Just a slight change to the code shown here.

To make it work with seconds, however, you need a more recent date as using the "zero" date of 1900-01-01 results in...

dwain.c (3/26/2012)

--------------------------------------------------------------------------------

Jeff,

The following thought occurred to me last night but I didn't get a chance to test it until this morning.

Isn't the modulo function designed to always return a positive...

Try this and see if it helps get you past it.

DECLARE @Year [nvarchar](4000)

--SET NOCOUNT ON

--SET ANSI_WARNINGS Off

If that works you can uncomment the two SET statements.

I just used deadlock priority for the first time in a SP that was causing me serious headaches.

Say it ain't so!

Never look a gift horse in the mouth.

SELECT custid, visitid,

REPLACE(STUFF( (SELECT TOP 15 ',' + pagename

FROM @x x2

WHERE x1.custid = x2.custid and x1.visitid = x2.visitid

ORDER BY x2.custid, x2.visitid, x2.pagenum...

Doesn't PIVOT only work for a fixed number of columns?

What if your visitor visits 250 pages? I think the solution I proposed will work for that case.

Unfortunately I can't help you with that as I'm no PIVOT-master (merely a Paduan) and I don't have access to SQL Server 2008 at this moment.

Still want to know why...

Just shooting in the dark here but maybe if you rearranged your statements as follows it might get you closer:

ALTER PROCEDURE [dbo].[timelord_year]

@Year [nvarchar](4000)

SET NOCOUNT ON

SET ANSI_WARNINGS Off

;WITH

cteToDateTime(DT) AS

(

SELECT...

A search of this forum should probably have yielded this solution which I've already posted:

SELECT DATEADD(hour, DATEDIFF(hour, 0, '2012-03-27 12:57:21.097'), 0)

No need to use CAST or CONVERT!

While a PIVOT will probably get you to the intermediate result, I say skip the intermediate result if you can with this:

DECLARE @x TABLE

(custid CHAR(3), visitid INT, pagenum INT, pagename...