Solved

sum if mysql

Posted on 2012-03-20
1
288 Views
Last Modified: 2012-03-21
Hi I am trying to do a calculation of the number of helpdesk calls that are open at the end of the day.

I am using the following

sum(if(FROM_UNIXTIME(maindb.closedatex,"%Y-%m-%d") > dates.date,1,0 ))

This checks if the close date of the call is greater than my date field. However if a call is still open then the closdate is displayed as "1970-01-01"

Does anyone know how I can add this into the statement above?
0
Comment
Question by:Dan560
1 Comment
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37745157
if i understand you right you need

sum(if(FROM_UNIXTIME(maindb.closedatex,"%Y-%m-%d") > dates.date or maindb.closedatex=0, 1,0 ))

0 because 1970-01-01 00:00:00 translates to that in unix_timestamp.  or you can also do

"or from_unixtime(maindb.closedatex,'%Y-%m-%d') = '1970-01-01'"
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question