dwalby Posted March 14, 2018 Share Posted March 14, 2018 Hi all, I'm looking to create a measure showing open requests over 3 months old, presumably this will involve a WHERE clause querying the h_datelogged column but I wasn't sure how to build this to show requests logged over 3 months ago? Thanks in advance Link to comment Share on other sites More sharing options...
Victor Posted March 14, 2018 Share Posted March 14, 2018 I'm sure @Dan Munns has some helpful ideas In case he is busy ()... since you mentioned you want requests older than 3 months (and days in a month can vary) the WHERE clause can be something like this: ... WHERE TIMESTAMPDIFF(MONTH, h_datelogged, CURDATE()) >= 3 Obviously, this is in addition to your other criteria like, status is open, etc... Link to comment Share on other sites More sharing options...
Dan Munns Posted March 14, 2018 Share Posted March 14, 2018 @Victor that seemed like a thinly veiled shot at my (less than mediocre) SQL skillz (with a z because I am down with the kids) Link to comment Share on other sites More sharing options...
dwalby Posted March 14, 2018 Author Share Posted March 14, 2018 @Victor my SQL skillz (I'm also down with the kids) must be worse than @Dan Munns! This is where I'm attempting to create the query within the measure, but doing this returns a 0 value. I'm assuming the query needs to be different in this section? Link to comment Share on other sites More sharing options...
Victor Posted March 14, 2018 Share Posted March 14, 2018 28 minutes ago, Dan Munns said: that seemed like a thinly veiled shot at my (less than mediocre) SQL skillz (with a z because I am down with the kids) @Dan Munns on the contrary, I know you worked on quite a few of these so you are now mastering them ... don't let anyone tell you otherwise!!! @dwalby hmm... I don't think it works in a measure, due to how sampling works... but should work fine in a widget or report... Link to comment Share on other sites More sharing options...
Dan Munns Posted March 14, 2018 Share Posted March 14, 2018 Can confirm widget works. For a measure could you not utilise a calls logged per month measure and filter it to show calls still open after x months in a table? Edit: on the contrary, I know you worked on quite a few of these so you are now mastering them ... don't let anyone tell you otherwise!!! I am sure you did most of the work to be honest @Victor Link to comment Share on other sites More sharing options...
Victor Posted March 14, 2018 Share Posted March 14, 2018 @Dan Munns not denying my input, but I will quote your recent statement... On 09/03/2018 at 9:56 AM, Dan Munns said: Always two there are, no more, no less. A master and an apprentice.... Oh how the tables have turned..... You are ready!! @dwalby sorry for hijacking the thread with Star Wars quotes, I just can't help it ... I hope you can pick up the useful bit here and there from all this ... Link to comment Share on other sites More sharing options...
dwalby Posted March 14, 2018 Author Share Posted March 14, 2018 @Victor Thanks for this, I'm sure I can get what I need from this Link to comment Share on other sites More sharing options...
Guest Posted March 14, 2018 Share Posted March 14, 2018 Hi @dwalby Admittedly I haven't tried this in a measure, but this may well work: h_datelogged < DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AND (h_status = 'status.open' OR h_status = 'status.onhold') Kind Regards Bob Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now