데이터가 나오면 poi를 이용하여 엑셀파일로 저장을 하게 해둔 상황입니다
지금의 쿼리문 입니다
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (
ORDER BY measurement_date DESC, measurement_time DESC ) AS rownum ,
(CASE
WHEN device_plot = '11' THEN 'Plot1'
WHEN device_plot = '22' THEN 'Plot2'
WHEN device_plot = '33' THEN 'Plot3'
WHEN device_plot = '44' THEN 'Plot4'
END)as deviceplot , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
, STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':') measurementtime
, MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END) temperaturevalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END) temperaturevalueM
, MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END) temperaturevalueB
, MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value END) humidityvalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value END) humidityvalueM
FROM
smarcle_data_log
WHERE
1 = 1
AND device_plot_tmb IN ('1', '2', '3')
AND(SUBSTRING(measurement_time, 3, 2) % 10)= 0
AND measurement_date BETWEEN REPLACE(20210115, '-', '') AND REPLACE(20210115, '-', '')
GROUP BY
device_plot, measurement_date, measurement_time)a
쿼리문 입니다..
이상태로 엑셀 다운로드를 하게 되면 값은 잘 나오나 빈칸이 나오는곳이 null 값이 표출되어서 도움을 주시면 감사하겠습니다
!
CASE 문을 써서 똑같이
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (
ORDER BY measurement_date DESC, measurement_time DESC ) AS rownum ,
(CASE
WHEN device_plot = '11' THEN 'Plot1'
WHEN device_plot = '22' THEN 'Plot2'
WHEN device_plot = '33' THEN 'Plot3'
WHEN device_plot = '44' THEN 'Plot4'
WHEN temperature_value = 'NULL' THEN ''
END)as deviceplot , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
, STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':') measurementtime
, MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END) temperaturevalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END) temperaturevalueM
, MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END) temperaturevalueB
, MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value END) humidityvalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value END) humidityvalueM
FROM
smarcle_data_log
WHERE
1 = 1
AND device_plot_tmb IN ('1', '2', '3')
AND(SUBSTRING(measurement_time, 3, 2) % 10)= 0
AND measurement_date BETWEEN REPLACE(20210115, '-', '') AND REPLACE(20210115, '-', '')
GROUP BY
device_plot, measurement_date, measurement_time, temperature_value)a
처리를 해보았는데 값이 제대로 나오지 않아서 도움을 구합니다 ㅠㅠ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY measurement_date DESC , measurement_time DESC ) AS rownum , CASE device_plot WHEN '11' THEN 'Plot1' WHEN '22' THEN 'Plot2' WHEN '33' THEN 'Plot3' WHEN '44' THEN 'Plot4' ELSE '' END AS deviceplot , CONVERT ( CHAR (10), CONVERT (DATETIME, measurement_date), 111) measurementdate , STUFF(STUFF(measurement_time, 3, 0, ':' ), 6, 0, ':' ) measurementtime , CONCAT( MAX ( CASE device_plot_tmb WHEN '1' THEN temperature_value END ), '' ) temperaturevalueT , CONCAT( MAX ( CASE device_plot_tmb WHEN '2' THEN temperature_value END ), '' ) temperaturevalueM , CONCAT( MAX ( CASE device_plot_tmb WHEN '3' THEN temperature_value END ), '' ) temperaturevalueB , CONCAT( MAX ( CASE device_plot_tmb WHEN '1' THEN humidity_value END ), '' ) humidityvalueT , CONCAT( MAX ( CASE device_plot_tmb WHEN '2' THEN humidity_value END ), '' ) humidityvalueM FROM smarcle_data_log WHERE 1=1 AND device_plot_tmb IN ( '1' , '2' , '3' ) AND SUBSTRING (measurement_time, 3, 2) % 10 = 0 AND measurement_date BETWEEN '20210115' AND '20210115' GROUP BY device_plot, measurement_date, measurement_time ) a ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY measurement_date DESC , measurement_time DESC ) AS rownum , CASE device_plot WHEN '11' THEN 'Plot1' WHEN '22' THEN 'Plot2' WHEN '33' THEN 'Plot3' WHEN '44' THEN 'Plot4' ELSE '' END AS deviceplot , CONVERT ( CHAR (10), CONVERT (DATETIME, measurement_date), 111) measurementdate , STUFF(STUFF(measurement_time, 3, 0, ':' ), 6, 0, ':' ) measurementtime , ISNULL ( CAST ( MAX ( CASE device_plot_tmb WHEN '1' THEN temperature_value END ) AS VARCHAR ), '' ) temperaturevalueT , ISNULL ( CAST ( MAX ( CASE device_plot_tmb WHEN '2' THEN temperature_value END ) AS VARCHAR ), '' ) temperaturevalueM , ISNULL ( CAST ( MAX ( CASE device_plot_tmb WHEN '3' THEN temperature_value END ) AS VARCHAR ), '' ) temperaturevalueB , ISNULL ( CAST ( MAX ( CASE device_plot_tmb WHEN '1' THEN humidity_value END ) AS VARCHAR ), '' ) humidityvalueT , ISNULL ( CAST ( MAX ( CASE device_plot_tmb WHEN '2' THEN humidity_value END ) AS VARCHAR ), '' ) humidityvalueM FROM smarcle_data_log WHERE 1=1 AND device_plot_tmb IN ( '1' , '2' , '3' ) AND SUBSTRING (measurement_time, 4, 1) = '0' AND measurement_date BETWEEN '20210115' AND '20210115' GROUP BY device_plot, measurement_date, measurement_time ) a ; |