If the sequence of ID column values start at 1 and are in the same order as the TimeStamps values then the following should work. The COALESCE is needed for...

If you can UNPIVOT your data into something like the following:

DECLARE @Unpivot TABLE (RowId int, ColName varchar(50), ColVal int)

INSERT INTO @Unpivot(RowId, ColName, ColVal)

SELECT 1, 'Col1', 10 UNION ALL

SELECT 1,...

Here's an alternative expression:

SET @searchDate = DATEADD(year, DATEDIFF(year, @searchDate, 0), @searchDate)

Note that the two expressions give different answers if the @searchDate is a 29th February. Mine gives '1900-02-28', Gianluca's...

I have tweaked Craig's test data generator so that the number of digits is fixed for a given prefix. In my tests below, generated just over 2 million rows. Note...

I've made some assumptions in my possible solution. If my assumptions are wrong, you may well be able to tweak the query to get it working for you.

I have assumed:

1)...

You don't need a recursive CTE to do this. Here is one way to do it.

;WITH cteMembership AS (

SELECT MemberID, EffDate, ExpDate,

...

I'm not sure you can avoid looping or recursion to achieve the desired result, at least not efficiently. The following uses a single WHILE loop rather than nested loops. The...

Another option would be to extract the timestamp data for just the checkpoint rows into a CTE and also add a sequential row counter using ROW_NUMBER() with the sequence numbering...

Is this what you want?

DECLARE @mydate DATETIME

SELECT @mydate = GETDATE()

DECLARE @StartDate datetime

SELECT @StartDate = DATEADD(month, DATEDIFF(month, 0, @mydate), 0)

SELECT

number AS [Period],

...

No, that would not be a good idea. The IDENTITY property does not guarantee that there will not be any gaps. And in practice, there will be gaps. If the...

It does work as advertised.

Books Online: Returns the starting position of the specified expression in a character string.

Also, it works...

I can imagine some reasonable uses for an IDENTITY column where duplicates are allowed / expected.

Say we have some sort of versioning system where the version number comprises a major...

If you really want to truncate rather than round, i.e. if you want 0.21 rather than 0.22 given an input of 0.21923828125, then you would need to explicitly truncate using...

This alternative doesn't use the quirky update, and doesn't have any specific indexing requirements in order to produce the right answer, though appropriate indexes on Code and UserId columns would...

SQL ORACLE (8/13/2010)

---

I came across the following issue:

The following scripts give me correct returns:

SELECT COUNT(a.colA) FROM TableA a

SELECT COUNT(b.colA) FROM TableB b

However, the script below is syntax correct, but returns...