Issue in forming an SQL Query

Riddhi Valecha

Hi all...

I have to form a sql query.

My requirement is -
I have a table -

ID	Date	Value
1	2014-09-01	4
2	2014-11-09	5
3	2014-08-06	66
4	2014-08-09	55
5	2014-08-08	78
6	2014-12-01	79
7	2014-12-13	90
8	2014-11-11	92
9	2014-12-11	87
10	2014-09-09	34

ID- Primary Key, Auto Increment.
I want a result like this -

Date

Value

September-2014

November-2014

August-2014

December-2014

Please help.

Thanks a ton in advance.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vikram Agrawal11y ago
Accepted answer
Hi Riddhi,

You can get results in this format by using the following query.

SELECT CAST(DATENAME(MM,DATE_COLUMN) AS VARCHAR(20)) + '-' + CAST(YEAR(DATE_COLUMN) AS VARCHAR(5)) DATE, VALUE FROM MY_TABLE

Thanks.

Mark it as accepted answer if it is helpful to you
0
Pradeep Shet11y ago
Hi Riddi,

The query would be
SELECT MONTH(DATE) + '-' + YEAR(DATE) AS Date, Value
FROM table
ORDER BY DATE

I hope this helps you. Plz mark it as answered if it helped you.
0
Riddhi Valecha11y ago
Hi Deepak...

I did not get your logic.

Please explain.

I tried, but this is not working.
0
Deepak Verma11y ago
Hello Riddhi,
Execute the below statements in SQL Server:
DECLARE @table TABLE( ID     INT IDENTITY(1, 1), [Date] DATE, Value INT)

DECLARE @result TABLE( [Date] DATE, Value INT)

INSERT INTO @table ([date], value)
VALUES      ( '2014 - 09 - 01', 4),
            ( '2014 - 11 - 09', 5),
            ( '2014 - 08 - 06',   66),
            ( '2014 - 08 - 09',   55),
            ( '2014 - 08 - 08',   78),
            ( '2014 - 12 - 01',   79),
          ( '2014 - 12 - 13',   90),
            ( '2014 - 11 - 11',   92),
            ( '2014 - 12 - 11',   87),
            ( '2014 - 09 - 09',   34)

SELECT *
FROM   @table

DECLARE @Date DATE

WHILE( EXISTS(SELECT id
              FROM   @table) )
BEGIN
      SET @Date = (SELECT TOP 1 [date]
                   FROM   @table)

      INSERT INTO @result
                  ([date],
                   Value)
      SELECT [date],
             value
      FROM   @table
      WHERE DATEPART(month, [Date]) = DATEPART(month, @Date)
             AND DATEPART(year, [Date]) = DATEPART(year, @Date)

      DELETE FROM @table
      WHERE DATEPART(month, [Date]) = DATEPART(month, @Date)
             AND DATEPART(year, [Date]) = DATEPART(year, @Date)
END

SELECT DateName(MONTH, [Date]) + '-' + CAST(Datepart(Year, [Date]) AS VARCHAR(4)) [Date],
       Value

FROM   @result
+1