SELECT Field1 AS [@AttributeNameHere]

I have now had some time to check the math for the formula.

The short story is that if the anchor date is a leapyear the algorithm fails. If you...

Why not use the OUTPUT clause?

After further investigation, it seems you don't need the CASE constructor either.

Another simple math trick will do.

SELECT (DATEPART(DAYOFYEAR, DATEDIFF(DAY, -53690, Date) / 7 * 7 - 53687) + 6) /...

*removed*

However, the fix is really simple. Since all we need in an anchor date of a monday, write this case statement.

--===== Test the new formula ============================================================

DECLARE @Bitbucket INT;

...

This is one excellent algorithm to get the ISO week number.

However doing this by using 17530101 instead of 19000101 throws an error

Arithmetic overflow error converting expression to data type datetime.

It...

Or add a WHERE clause on the Result column?

SELECTAntibiotic,

SUM(1) AS [No. of tests],

SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS [No. of resistant sample],

100E * SUM(CASE WHEN...

Excellent article(s) Jeff!

I emailed you to your ameritech adress last week wondering about when your "nested set killer" will be published. Sorry to say the email bounced so you...

Removed: Didn't test properly.

You're welcome.

DECLARE@IPvalue BIGINT = 3222211197,

@IPstring VARCHAR(15) = '192.15.10.125'

-- SwePeso (value to string)

SELECTCAST(CAST(SUBSTRING(Data, 1, 1) AS TINYINT) AS VARCHAR(3))

+ '.' + CAST(CAST(SUBSTRING(Data, 2, 1) AS TINYINT) AS VARCHAR(3))

+ '.' + CAST(CAST(SUBSTRING(Data, 3,...

Isn't this faster?

SELECTPatient_ID,

COUNT(*) AS Number_Of_Visits

FROM(

SELECT DISTINCTPatient_ID,

DATEDIFF(DAY, '19000101', Er_Date) - DENSE_RANK() OVER (PARTITION BY Patient_ID ORDER BY DATEDIFF(DAY, '19000101', Er_Date)) AS DayKey

FROM@TempTable

) AS d

GROUP BYPatient_ID

ORDER BYPatient_ID

I hate to be the party pooper but change one value to a negative value and try again.

Also see http://weblogs.sqlteam.com/peterl/archive/2008/11/19/How-to-get-the-productsum-from-a-table.aspx

Which...