Hello all,
I'm trying to write a stored procedure that will prompt the user for a
date range and produce a report based on that date range and 4 incident
types. I only need count how may times these incidents happen within
the data range. The query looks like this
SELECT incident, @Enter_Beginnin g_Date AS [Beginning Date],
@Enter_Ending_D ate AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @Enter_Beginnin g_Date AND
@Enter_Ending_D ate) AND (incident = 'Customer Accident')
GROUP BY incident
This works fine, but I need to get incident = customer accident,
Customer Illness, Employee Accident and Employee Illness in my incident
table. When ever I try to add Incident = Customer Illness to this
query, I get no results. Any assistance will be greatly appreciated. 5 1276
First of all, how are you prompting the user for values thru Stored
procedures????? ???
Your question is pretty hard to answer as you havent provided any info
about your table or Data...!
Do you have records that qualifies Incident = 'Customer Illness'...!
It would help people to help you if you post some DDL/DML codes to
understand where the problem is....
sorry about the vagness,
The table I'm trying to query looks something like this
ID(int) Incident(nvarch ar) DateOccured (datetime)
1200 Customer Illness 1/1/2003
1201 Customer Illness 1/2/2003
1202 Customer Accident 1/2/2003
1203 Customer Accident 1/3/2003
1204 Employee Accident 1/5/2003
1205 Employee Illness 1/6/2003
The stored procedure prompts the user for a beginning
(@Enter_Beginni ng_Date) and ending date(@Enter_End ing_Date) which uses
the DateOccured column for the specified date range. I want the query
to count the number of occurences that happen within the specified date
range. I want to create a report that looks loke this
Incident Number of Occurences
Customer Illness 2
Customer Accident 2
Employee Accident 1
Employee Illness 1
When ever I query for one incident (Customer Accident), the query will
work fine, but when I insert another statement [(incident = Customer
Illness) AND (incident = Customer Accident)] I get no results, when I
know there are at least 3 incidents of Customer Illness. Does this help?
Based on the data you gave..
declare @Enter_Ending_D ate datetime
declare @Enter_Beginnin g_Date datetime
set @Enter_Ending_D ate='1/2/2003'
set @Enter_Beginnin g_Date='1/2/2003'
SELECT incident,
@Enter_Beginnin g_Date AS [Beginning Date],
@Enter_Ending_D ate AS [Ending Date],
COUNT(*) AS Occurances
FROM Incident
WHERE (DateOccured BETWEEN @Enter_Beginnin g_Date AND
@Enter_Ending_D ate) AND
(incident = 'Customer Illness') or (incident = 'Customer Accident')
--(incident = 'Customer Accident')
GROUP BY incident
/*************** *************** *
RESULTSET
Customer Accident 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 2
Customer Illness 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 1
On 26 Jan 2005 12:09:50 -0800, ndn_24_7 wrote:
(snip) The stored procedure prompts the user for a beginning (@Enter_Beginn ing_Date) and ending date(@Enter_End ing_Date) which uses the DateOccured column for the specified date range.
Hi ndn_27_7,
I presume you meant to write that the front-end app prompts the user for
these dates and passes them to SQL Server when calling the stored
procedure, as there is no way that a stored procedure will ever prompt for
input.
When ever I query for one incident (Customer Accident), the query will work fine, but when I insert another statement [(incident = Customer Illness) AND (incident = Customer Accident)] I get no results, when I know there are at least 3 incidents of Customer Illness. Does this help?
The WHERE clause is checked against each row individually. No single row
will ever have both 'Customer Illness' and 'Customer Accident' in the same
column (incident).
SELECT incident, @Enter_Beginnin g_Date AS [Beginning Date],
@Enter_Ending_D ate AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE DateOccured BETWEEN @Enter_Beginnin g_Date
AND @Enter_Ending_D ate
AND ( incident = 'Customer Accident'
OR incident = 'Customer Illness')
GROUP BY incident
or
SELECT incident, @Enter_Beginnin g_Date AS [Beginning Date],
@Enter_Ending_D ate AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE DateOccured BETWEEN @Enter_Beginnin g_Date
AND @Enter_Ending_D ate
AND incident IN ('Customer Accident', 'Customer Illness')
GROUP BY incident
(Note that IN is just a short form for writing a bunch of OR's)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
It worked very good, THank you for all your assistance This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use SUBSTRING(ProductName,
1, CHARINDEX('(', ProductName)-2).
I can get this result, but I had to use several views (totally
inefficient). I think this can be done in one efficient/fast query,
but I can't think of one.
In the case that one query is not...
|
by: Richard Holliingsworth |
last post by:
Hello:
Thanks for your quick response.
I'm trying to import a new Excel file into an A2K table and it's
truncating the data. One of the Excel columns is a text field that can
be up to 2000 characters. When I import it into A2K, that field is
truncated. If I try to inport the file into an EXISTING table that I
define myself where that field is a MEMO field, the import crashes.
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
by: Steve Patrick |
last post by:
Hi All
You guys are my last hope, despite spending money on books and hours reading
them I still can not achieve the results I need.
I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular locations
and as part of the process they are asked to list their preferred locations
1 to 4....
|
by: DiNa |
last post by:
hi i'm a newbie plez help me...
i'm facing this prob while running the program... can anybody help me...?
1026 - Could not find stored procedure 'dbo.SP_AccountListing'.SP_AccountListing
after i open the table then --> open vendor
public bool OpenVendorListing(AccountListingCls accListingCls)
{
| |
by: ra7l |
last post by:
Hi All ..
First Thanks to All For Help Me ..
ok ..This Code it Move Train but one errore small..
Where Correct Cods
Thanks All ..
:)
|
by: manaankit |
last post by:
is it possible to immediately close the socket created widout letting it go into wait state n dan reuse dat port.
plez tell me its urgent...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |