Jeff Moden (9/8/2012)

---

Look at the original code the op posted. Why would you BGROUP BY ID and take an avarage if there wasn't going to be more than one...

Jeff Moden (9/7/2012)

---

Dwain,

You made the same mistake that I originally made. Try your code on the following test data and marvel at the pain of an accidental CROSS JOIN.

DECLARE...

You're welcome.

A brief explanation:

The Tally table is...

Have you tried mine?

Note that I just made a quick...

In that case, this should work nicely for you:

DECLARE @Miview TABLE

(Username VARCHAR(20), Start DATETIME, Activity VARCHAR(20))

INSERT INTO @Miview

SELECT 'Tom', '2012-09-07 12:00', 'Logon'

UNION ALL SELECT 'Fred', '2012-09-07...

Perhaps something like this:

DECLARE @t TABLE (ID INT IDENTITY, strcol VARCHAR(20))

INSERT INTO @t

SELECT 'aa001' UNION ALL SELECT 'ab001'

UNION ALL SELECT 'aa001a0' UNION ALL SELECT 'aa001b0'

;WITH Tally (n) AS (

...

swathi nareddy (9/6/2012)

---

Can I use below function instead of DelimitedSplit8K() function

CREATE FUNCTION SPLIT

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue...

Try googling "SQL Crosstabs" or look at these articles from Jeff Moden:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

If you provide sample data, I'm sure someone can provide you a working, tested solution from the DDL...

Here is another way to return m/d format (mm/dd without leading 0s):

SELECT STUFF(REPLACE('/'+CONVERT(VARCHAR(5), YourDate, 101), '/0', '/'), 1, 1, '')

On the GROUP BY, I would use something like this to...

If you have 0 logins on a day, do you want to show that date (with 0 unique logins) row?

Lynn Pettis (9/6/2012)

---

Why does he have to be so anal all the time? It gets real tiring reading it day after day. I wish SQLTeam would take him...

Tatsu (9/6/2012)

---

All of the solutions to Ash-N's question are really good. I am just not sure Ash-N asked the right question. That would be "What is the best way to...

Interesting problem. Fortunately I developed a script "UNIQUEnTuples" in this article that will help:

http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/

First, while you did do a good job of showing your expected results, you should always...

SQLTestUser (9/6/2012)

---

I have a table as follows

Logtable

ID Date ...

chaseurpuli (9/6/2012)

---

Thanks a lot for your response Dwain. Your code is successful in keeping the families together and ordering the items by date as long as the parents have different...