Dates must be shown as string with comma separtor

Question

Dates must be shown as string with comma separtor

sujavani.l

SSC Journeyman

Points: 92
More actions
March 26, 2011 at 1:16 am

#233774

I have table
User ID Date
1 01\03\2011
1 02\03\2011
1 03\03\2011
1 07\03\2011
1 09\03\2011
1 010\03\2011
1 011\03\2011
The output must be varchar. and the result like
User ID Date
1 01-Mar-2011 to 03-Mar-2011,07-Mar-2011,09Mar-2011 to 11Mar-2011

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Search this site for detecting gaps and islands - you're going to be interested in the islands.

(Posting tip: when posting dates, include a "SET DATEFORMAT DMY" so that people in other areas of the world are talking the same thing. Also, read the first link in my signature for how to post to get people to want to help you with your problem.)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Your data as posted:

User ID Date
1 01\03\2011
1 02\03\2011
1 03\03\2011
1 07\03\2011
1 09\03\2011
1 010\03\2011
1 011\03\2011

Are these 2 entries in your actual data or simply a mistake in your entering here in the forum?

1 010\03\2011

1 011\03\2011

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

WayneS (3/26/2011)
Search this site for detecting gaps and islands - you're going to be interested in the islands.
(Posting tip: when posting dates, include a "SET DATEFORMAT DMY" so that people in other areas of the world are talking the same thing. Also, read the first link in my signature for how to post to get people to want to help you with your problem.)

To add to what Wayne said, you're also going to want to look into FOR XML to get the comma delimited list once you find your islands.

Check out this article:

http://beyondrelational.com/blogs/jacob/archive/2008/08/14/for-xml-path-how-to-generate-a-delimited-string-using-for-xml-path.aspx

(For some reason the SSC search isn't working for me today)

Well, since search isn't behaving, I went to google to go locate the article for you. Here's the direct link to the grouping islands article by Jeff Moden.

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

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

CELKO (3/26/2011)
Why did you think that any Client/Server architecture (SQL or anything else) would allow formatting in the back end? This is not just SQL. It shows you never made any effect at all to even learn the most fundamental concepts.
Please read a book -- any book -- on RDBMS so you will never post a request to violate First Normal Form again. Then read the ISO-8601 Standards that you should have already known so you will not use dialect date formats.

Joe, I have to wonder, since you are constantly telling people to read the ISO-8601 Standards, how much of a royalty do you make?

Better yet, since you want so many people to read them, how about purchasing them for us. Personally, I have better things to spend my money on other than ISO standards.

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 5

sujavani.l (3/26/2011)
I have table
User ID Date
1 01\03\2011
1 02\03\2011
1 03\03\2011
1 07\03\2011
1 09\03\2011
1 010\03\2011
1 011\03\2011
The output must be varchar. and the result like
User ID Date
1 01-Mar-2011 to 03-Mar-2011,07-Mar-2011,09Mar-2011 to 11Mar-2011

Sujavani,

Before we get started on your problem, take a look at the answers you've received so far. They're all non-coded suggestions. Search for this... look into that, etc, etc. If you really want help, spend a little time on the presentation of your problem. You can find out what I'm talking about by studying the article at the first link in my signature line below. You might also want to be a wee bit more careful with the data you submit like the 010 and 011 months you added to your test data. You have to show that you care or no one else will

Ok... on to your problem. First, we need some readily consumable data to test with. Here's one way to do that. A different but just as effective method is listed in the article that I suggested you study. 😉

--===== Conditionally drop the test table to make reruns easier.

-- This is NOT a part of the solution.

IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create the test table and popululate it with data.

-- Again, this is NOT a prart of the solution.

SELECT UserID, [Date]

INTO #TestTable

FROM ( --=== Original data provided by the OP plus some more

SELECT 1,'01\03\2011' UNION ALL

SELECT 1,'02\03\2011' UNION ALL

SELECT 1,'03\03\2011' UNION ALL

SELECT 1,'07\03\2011' UNION ALL

SELECT 1,'09\03\2011' UNION ALL

SELECT 1,'10\03\2011' UNION ALL

SELECT 1,'11\03\2011' UNION ALL

SELECT 2,'01\03\2011' UNION ALL

SELECT 3,'03\03\2011' UNION ALL

SELECT 3,'04\03\2011' UNION ALL

SELECT 3,'08\03\2011' UNION ALL

SELECT 3,'09\03\2011' UNION ALL

SELECT 3,'10\03\2011' UNION ALL

SELECT 3,'11\03\2011' UNION ALL

SELECT 4,'09\03\2011' UNION ALL

SELECT 4,'10\03\2011'

)d (UserID, [Date])

;

Now, how to solve your problem. Read the comments in the code below for a full understanding...

{EDIT} and , "No", this is NOT dynamic SQL even though the bloody forum code painted most of it Red.:crazy:

WITH

cteRealDates AS

( --=== Convert the "Date" to a real datetime so we can work on it.

-- Also, create the month serial number using the number of months sin 1900-01-01.

SELECT UserID,

RealDate = CAST(REPLACE([Date],'\', '/') AS DATETIME),

MonthSerial = DATEDIFF(mm,0,CAST(REPLACE([Date],'\', '/') AS DATETIME))

FROM #TestTable

),

cteMonthGroups AS

( --=== Create the "month groups" simply by subtracting a running count in the correct order

-- from the increasing values of the month serial number

SELECT UserID,

RealDate,

MonthGroup = MonthSerial - ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UserID, MonthSerial)

FROM cteRealDates

),

cteDateRanges AS

( --=== Find the Min/first and Max/last date of each group

-- and begin the required formatting

SELECT UserID,

DateRange = REPLACE(CONVERT(CHAR(11),MIN(RealDate),106),' ','-')

+ CASE

WHEN COUNT(*) > 1

THEN ' to ' + REPLACE(CONVERT(CHAR(11),MAX(RealDate),106),' ','-')

ELSE ''

END,

MonthGroup

FROM cteMonthGroups

GROUP BY UserID, MonthGroup

) --=== Select the unique UserID and concatenate all date ranges for the UserID

SELECT dr1.UserID,

STUFF(

( --=== Concatenate the correct date ranges

-- in order for this given UserID

SELECT ',' + dr2.DateRange

FROM cteDateRanges dr2

WHERE dr2.UserID = dr1.UserID

ORDER BY dr2.MonthGroup

FOR XML PATH('')

)

,1,1,'')

FROM cteDateRanges dr1

GROUP BY dr1.UserID

ORDER BY dr1.UserID

;

The output from my example is like this...

UserID(No column name)

103-Jan-2011 to 03-Mar-2011,03-Jul-2011,03-Sep-2011 to 03-Nov-2011

203-Jan-2011

303-Mar-2011 to 03-Apr-2011,03-Aug-2011 to 03-Nov-2011

403-Sep-2011 to 03-Oct-2011

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)