Created
August 12, 2022 12:08
-
-
Save mrhat24/8e2ca191287bad8399ed653ad6eafca6 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| const sourceData: { | |
| id: string; | |
| addressId: number; | |
| isCrashedMeter: boolean; | |
| meterSerialNum: string; | |
| meterModelId: number; | |
| }[] = await em.query(` | |
| SELECT | |
| m.id as id, | |
| address_id as addressId, | |
| meter_serial_num as meterSerialNum, | |
| meter_model_id as meterModelId, | |
| isCrashedMeter = CAST((case | |
| when (d.reading_date is null or d.reading_date <= m.edge) | |
| and (c.reading_date is null or c.reading_date <= m.edge) | |
| then 1 | |
| else 0 | |
| end) as bit) | |
| from (select *, edge = dateadd(minute, -time_zone, | |
| dateadd(day, -@0, convert(datetime2, convert(date, dateadd(minute, time_zone, getutcdate()))))) | |
| from meter.meter | |
| where status in ('Active', 'Defective') and region_id = @ 1 ${readingSourceRequestFilter} | |
| ) m | |
| inner join ( | |
| select MAX(reading_date) reading_date, meter_id | |
| from meter.meter_reading_daily_profile | |
| GROUP BY meter_id | |
| ) d on m.id = d.meter_id | |
| inner join ( | |
| select MAX(reading_date) reading_date, meter_id | |
| from meter.meter_reading_current_profile | |
| GROUP BY meter_id | |
| ) c on m.id = c.meter_id; | |
| `, [daysOffline, regionId, readingSource]); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment