Re: sql having clause

Something like

SELECT id, sum(metereduse), year(getdatesql(readdate)) AS Y, month(getdatesql(readdate)) AS M
 FROM dailyuse
GROUP BY id, year(getdatesql(readdate)), month(getdatesql(readdate))
HAVING month(getdatesql(readdate)) = MONTH(CURRENT DATE) - 1
ORDER BY id, Y, M

You use the UDF quite a lot, will cause a performance issue, I fear, and not needed in the ORDER BY clause.


Op 17-4-2020 om 19:50 schreef Smith, Mike:

I have a table that has id, usage, date(decimal date) this is a daily file.
I am summing the usage by id and grouping by id, year and month.
I have a udf that I apply to the date to convert it to a true date getdatesql(mydate)

So I might have
ID useage year month
1 10 2019 1
1 15 2019 2
2 5 2020 1
2 30 2020 2

SELECT id, sum(metereduse), year(getdatesql(readdate)),
month(getdatesql(readdate)) FROM dailyuse
GROUP BY id, year(getdatesql(readdate)), month(getdatesql(readdate))
ORDER BY id, year(getdatesql(readdate)), month(getdatesql(readdate))

I am not sure if I can or how I might do this, but
I would like to add a having clause that
Would identify the id, and usage that has say a 50% change between a given period. Could be a last month, this month,
Or last year, month vs this year, month



NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.