PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
It is currently Wed Mar 20, 2019 5:44 pm

All times are UTC - 5 hours

Post new topic Reply to topic  [ 2 posts ] 
Author Message
PostPosted: Mon Nov 07, 2016 3:53 pm 
Forum Newbie

Joined: Sat Aug 20, 2016 7:43 am
Posts: 2
I have the query below which returns all of the columns I am expecting. However, the date_met column is seeming to return a.) the same date for all results and b.) the wrong date for all results. It looks like it's really just picking the most current date in the date_start column, considering it's putting 2016-11-06, which IS a valid date_start, but it's outside of the main queries BETWEEN...

I am utterly terrible at sub-queries (which I'm pretty sure I do technically need), but I'm unsure where exactly I'm going wrong:

Syntax: [ Download ] [ Hide ]
    the_thresholds.threshold_snow AS snow_threshold,
    SUM(certifications.total_snow) AS rollingTotal,
    SUM(certifications.total_snow) >= the_thresholds.threshold_snow AS targetMet,
    IF(SUM(certifications.total_snow) >= the_thresholds.threshold_snow,
    0) AS dateMet
    `customer_settings`.`snowfall_thresholds` AS the_thresholds
        INNER JOIN
    `snowfall_certification`.`the_certifications` AS certifications ON `the_thresholds`.`zipcode` = `certifications`.`zipcode`
        INNER JOIN
    `zipcodes`.`ziplatlongs` AS ziplatlongs ON `the_thresholds`.`zipcode` = `ziplatlongs`.`zipcode`
    DATE(`certifications`.`date_start`) BETWEEN '2015-10-01' AND '2016-05-31'
GROUP BY `the_thresholds`.`zipcode`

As I said, it's returning all of the columns I expect, I just can't get it to put the correct dateMet when the SUM(certifications.total_snow) >= the_thresholds.threshold_snow was reached.

PostPosted: Tue Nov 08, 2016 10:32 am 
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13589
Location: New York, NY, US
dateMet will always be the same, either: MIN(certifications.date_start) or 0 depending on the condition IF(SUM(certifications.total_snow) >= the_thresholds.threshold_snow)


Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours

Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group