My query used to be
SELECT
PROVIDER,
LEFT([START_TIME],13) as [HOUR],
DEVICE_ID as BIKEID
FROM
table1
GROUP BY
PROVIDER_NAME, DEVICE_ID, LEFT([START_TIME],13)
and it was working fine.
Due to certain conditions I changed rule at Device_id
CASE
WHEN NAME = 'xyz' THEN VEHICLE_ID
ELSE DEVICE_ID
END AS BIKEID
And my changes look like
SELECT
PROVIDER,
LEFT([START_TIME], 13) AS [HOUR],
CASE
WHEN NAME = 'xyz' THEN VEHICLE_ID
ELSE DEVICE_ID
END AS BIKEID
--DEVICE_ID as BIKEID
FROM
table1
GROUP BY
CASE
WHEN NAME = 'xyz'
THEN (PROVIDER, VEHICLE_ID, LEFT([START_TIME], 13))
ELSE (PROVIDER, DEVICE_ID, LEFT([START_TIME], 13))
END
I did something wrong at GROUP BY
.
Anyone please suggest how to fix this.
Thanks
Is this what you want?
SELECT PROVIDER,
LEFT([START_TIME],13) as [HOUR],
(CASE WHEN NAME = 'xyz' THEN VEHICLE_ID
ELSE DEVICE_ID
END) as BIKEID
FROM table1
GROUP BY PROVIDER_NAME,
(CASE WHEN NAME = 'xyz' THEN VEHICLE_ID
ELSE DEVICE_ID
END),
LEFT([START_TIME], 13)