I have written a sql query and I need it to return 0 when it doesn't find any matches to my criteria. I have tried adding iif statements, tried sum, and just Count, all of these methods work fine to return the values when it finds matches, but i need it also to return a 0 when there are no matches. Here is what I got. -
-
SELECT "CAL Recieved" as Tags, [CAL Input Log].[Received Date],sum(iif([cal input log].[received date],1,0)) AS [Count]
-
FROM [CAL Input Log]
-
GROUP BY [CAL Input Log].[Received Date]
-
HAVING ((([CAL Input Log].[Received Date])=(Date()-1)));
-
UNION ALL
-
SELECT "CAL Initiated" as Tags , [CAL Input Log].[Initiated Date], Sum(iif([cal input log].[received date],1,0)) AS [Count]
-
FROM [CAL Input Log]
-
GROUP BY [CAL Input Log].[Initiated Date]
-
HAVING ((([CAL Input Log].[Initiated Date])=(Date()-1)));
-
UNION ALL
-
SELECT "CAL resub" as Tags , [CAL Input Log].[RESUB Recived Date], Count([CAL Input Log].[RESUB Recived Date]) AS [Count]
-
FROM [CAL Input Log]
-
GROUP BY [CAL Input Log].[RESUB Recived Date]
-
HAVING ((([CAL Input Log].[RESUB Recived Date])=(Date()-1)));
-
UNION ALL
-
SELECT "CDZ Recieved" as Tags, [CDZ Input Log].[Received Date], Count([CDZ Input Log].[Received Date]) AS [Count]
-
FROM [CDZ Input Log]
-
GROUP BY [CDZ Input Log].[Received Date]
-
HAVING ((([CDZ Input Log].[Received Date])=(Date()-1)));
-
UNION ALL
-
SELECT "CDZ Initiated" as Tags , [CDZ Input Log].[Initiated Date], Count([CDZ Input Log].[Initiated Date]) AS [Count]
-
FROM [CDZ Input Log]
-
GROUP BY [CDZ Input Log].[Initiated Date]
-
HAVING ((([CDZ Input Log].[Initiated Date])=(Date()-1)));
-
UNION ALL SELECT "CDZ resub" as Tags , [CDZ Input Log].[RESUB Recieved Date], Count([CDZ Input Log].[RESUB Recieved Date]) AS [Count]
-
FROM [CDZ Input Log]
-
GROUP BY [CDZ Input Log].[RESUB Recieved Date]
-
HAVING ((([CDZ Input Log].[RESUB Recieved Date])=(Date()-1)));
-
-
I also have another issue completly seperate, I need to another clause in there for "Open Tags" but it needs to search (from [CAL Input Log]) when "recieved date" is not null, and "complete date" is null and "resub complete date" is null. then count the matches. Hope this all makes sense.
Thanks for help with either or both issues in advance.
THANKS
3 10005 nico5038 3,080
Recognized Expert Specialist
I'll start with the first problem. When there's no match nothing will appear, this can only be handled by an additional table holding all unique "CAL Recieved", "CAL ...", .. values and being LEFT (or Right) Joined with the results of this UNION query.
Using such an OuterJoin forcing the "CAL..." values to show always will give a Null value you can transform into zero.
The second problem should be handled with a query just selecting the dates as described. First create a GroupBy query and when having set the GroupBy and Count switch to the SQL mode. After the FROM and before the HAVING clause insert a WHERE clause with the IsNull() and Not IsNull() dates. This will "strip" the rows you don't want to include before grouping and counting.
Finally add this query to the UNION.
Nic;o)
Thank You everything worked out.
nico5038 3,080
Recognized Expert Specialist
Well done, learning fast :-)
Success with your application !
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Random Person |
last post by:
Does anyone know how to use VBA to relink tables between two MS Access
databases? We have two databases, one with VBA code and the other with
data tables. The tables are referenced by linked tables in the database
where the code resides. If we move the database with the data tables to
a new directory, the links are no longer valid.
I...
|
by: Daveyk0 |
last post by:
Hello there,
I have a front end database that I have recently made very many changes
to to allow off-line use. I keep copies of the databases on my hard
drive and link to them rather than the live databases on the network.
Is there a way, via code, when I get back in-house from being on the
road to click a button, and select the backends...
|
by: Paul |
last post by:
hi, there,
for example,
char *mystr="##this is##a examp#le";
I want to replace all the "##" in mystr with "****". How can I do this?
I checked all the string functions in C, but did not find one.
|
by: Lokkju |
last post by:
I am pretty much lost here - I am trying to create a managed c++
wrapper for this dll, so that I can use it from c#/vb.net, however, it
does not conform to any standard style of coding I have seen. It is
almost like it is trying to implement it's own COM interfaces...
below is the header, and a link to the dll+code:
Zip file with header,...
|
by: ransoma22 |
last post by:
I developing an application that receive SMS from a connected GSM
handphone, e.g Siemens M55, Nokia 6230,etc through the data cable.
The application(VB.NET) will receive the SMS automatically, process and
output to the screen in my application when a message arrived. But the
problem is how do I read the SMS message immediately when it arrived...
| |
by: onecorp |
last post by:
I have a SQL table comprised of 31 columns.
The first column is simply an id column, the next 30 columns are labelled
,....
The numerical columns have a tinyint type and the data stored is either 1 or
null.
I wish to count the number of times a one appears in one column
simultaneously with another column:
eg count the number of times 1...
|
by: Ping |
last post by:
Hi,
I'm wondering if it is useful to extend the count() method of a list
to accept a callable object? What it does should be quite intuitive:
count the number of items that the callable returns True or anything
logically equivalent (non-empty sequence, non-zero number, etc).
This would return the same result as len(filter(a_callable,...
|
by: pereges |
last post by:
Hi, I am wondering which of the two data structures (link list or
array) would be better in my situation. I have to create a list of
rays for my ray tracing program.
the data structure of ray looks like this:
typedef struct
{
vector origin; /* vector is an array of 3 doubles */
|
by: satyabhaskar |
last post by:
hi all,
In my web page i have created radio buttons dynamically on to the page .....following is my code
string Course, Semester, Section;
int rowsCount;
string con = ConfigurationManager.ConnectionStrings.ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
try
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |