Convert Query To MSSQL

[shaunie]

Hi,

 

I'm not sure if this is the best place to ask but I have a query I have written in MySQL, and I need to convert it to MSSQL, does anyone have experience of this?

SELECT DATE_FORMAT(DateReported, '%b %Y') AS DateReported, DATE_FORMAT(DateCompleted, '%b %Y') AS DateCompleted, LocationName,
        IF(status = 'open', 1, 0) AS open,
        IF(status = 'closed', 1, 0) AS closed,
        IF(status = 'cancelled', 1, 0) AS cancelled
    FROM event
    GROUP BY DATE_FORMAT(DateReported, '%b %Y'), LocationName
    ORDER BY FIELD(DATE_FORMAT(DateReported, '%M'), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')

[Barand]

Date formatting is a PIA with MSSQL. You use convert with the right incantations of predefined format number

 

http://msdn.microsoft.com/en-us/library/ms187928.aspx

[shaunie]

Thanks for your reply, I have managed to convert the date columns (after a lot of hassle!):

SUBSTRING(CONVERT(VARCHAR(20), Event.ReportedDateTime, 113), 4,  AS [DateReported],

My main issue the IF statements which I have attempted to rewrite as

 

CASE Event.Status
  WHEN 'OPEN' THEN 'OPEN'
  WHEN 'CLOSED' THEN 'CLOSED'
  ELSE 'CANCELLED'
END

also I keep getting errors for not grouping some of the columns:

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

[Barand]

MySQL is far more lenient with GROUP syntax than MSSQL. With MSSQL, if it is in the query it must either be one of the aggregated columns or it must be in the GROUP BY clause

 

As you aren't doing any aggregations (SUM, COUNT etc) why are you using GROUP BY in the first place?

[kicken]

This bit:

IF(status = 'open', 1, 0) AS open,
        IF(status = 'closed', 1, 0) AS closed,
        IF(status = 'cancelled', 1, 0) AS cancelled

would translate as:

CASE WHEN status='open' THEN 1 ELSE 0 END as open,
CASE WHEN status='closed' THEN 1 ELSE 0 END as closed,
CASE WHEN status='cancelled' THEN 1 ELSE 0 END as cancelled

Then you have three separate 1/0 columns, one for each status. Same as the original query.