Friday, February 24, 2012

How to combine two columns into one in an sql

I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.

Here is the stored Proc:

SELECTDATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME),COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

It returns a table:

MONTH DATE TOTAL NUMBER OF CALLS
======= ===== ===========
1 1 10

1 2 15

My question is: is it possible to combine the Month and Date column into one column. e.g.

Date Total Number of Calls
==== ==============
1/1 10
1/2 15

Please Help, Thanks in advance :)

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME)) AS [Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

|||

thx, I did some configuration. works like a charm

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))AS[Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME) ,DatePart(dd, Callstarttime),CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

No comments:

Post a Comment