I have the following query: -
SELECTSELECT FirstListing,OnCallStart,OnCallEnd, Initials
-
FROM
-
(
-
SELECT
-
moncallAdd.FirstListing,
-
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
-
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
-
DATEADD(MINUTE, mOnCallAdd.duration,
-
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
-
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
-
Dateadd(second, moncalladd.Addtime,
-
Dateadd (Minute, moncalladd.AddTime,
-
Dateadd(Day, moncalladd.Adddate, '12/31/1899'))) as Added,
-
'Added' As Activity,
-
Initials
-
FROM
-
mdr.dbo.mOnCallAdd
-
WHERE DATEADD(MINUTE, mOnCalladd.StartOnCallTime,
-
DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899')) < GETDATE()
-
AND
-
DATEADD(MINUTE, mOnCalladd.duration,
-
DATEADD(MINUTE, mOnCalladd.StartOnCallTime,
-
DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899'))) > GETDATE() and
-
mOnCallAdd.schedname =@schedname
-
-
UNION
-
SELECT
-
moncallDelete.FirstListing,
-
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
-
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
-
DATEADD(MINUTE, mOnCallDelete.duration,
-
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
-
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
-
Dateadd(second,moncalldelete.Addtime,
-
Dateadd (Minute, moncalldelete.AddTime,
-
Dateadd(Day, moncalldelete.Adddate, '12/31/1899'))) as Added,
-
'Deleted' as Activity,
-
Initials
-
FROM
-
mdr.dbo.mOnCallDelete
-
WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
-
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE()
-
AND
-
DATEADD(MINUTE, mOnCallDelete.duration,
-
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
-
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) > GETDATE() and
-
mOnCallDelete.schedname = @schedname
-
)t
-
GROUP BY FirstListing,OnCallStart,OnCallEnd, Initials
-
HAVING SUM(CASE WHEN Activity='Added' THEN 1 ELSE 0 END) >0
-
AND SUM(CASE WHEN Activity='Deleted' THEN 1 ELSE 0 END) =0
-
and need to add a MAX on the Added portion of the table, can someone please assist me on adding that?
Thank you
Doug
1 1286
Max is in an aggregate function,you might need to revise your query if you want to use it only on the first portion of your SELECT.
Good Luck!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date...
|
by: hasanainf |
last post by:
Hi all,
What will be the best database design for an inventory control that
uses expiry date for its products.
Over a period of time, a particular product will have many expiry date
and that...
|
by: Li Pang |
last post by:
Hi
I can't remember how to block using local date setting, in another word, I want ot force to use a specific date setting
Anybody give a hand
Thanks
|
by: Lauren Quantrell |
last post by:
I already figured out (the hard way) I need to convert all my date
parameters into USA format before executing my stored procedures where
dates are used as parameters.
(Format(StartDate, "m/d/yyyy...
|
by: The_Monkey |
last post by:
I have created a form with a combo box to filter a subform using a date.
The filter works great but not with dates starting with a 0 e.g 01/07/2006
I have set the format to short date for the...
|
by: JJ |
last post by:
Here's the code.
$link="http://xbox360cheat.org";
$close_date=$_POST; #last content change check
if ($close_date == 0)
$close_date = date("Y-m-d H:m:s", mktime(12, 0, 0, date("m"), date...
|
by: Manogna |
last post by:
hi!
i got the following error while using the Date::Calc module in perl.
plz help me..
the file is ex.pl
contains code like this:
use strict;
use Date::Calc();
|
by: madhu3437 |
last post by:
i am in big problem
Under unix C Language
i want return date format like MM/DD/YYYY
this function should return above format .
using system date calculate last 6 days mean clearly i am...
|
by: MLH |
last post by:
Sometimes it works and sometimes it crashes.
If I want "Today is " & Date$ & "." to appear
in a query field, why might it work sometimes
and not others?
Would I be better to call a FN? Say,...
|
by: nhkam |
last post by:
I am using MS Access 2007
I have a transaction table which holds all records with posting date on each of them. I used a make table query to find out the max date hold in the transaction table and...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
| |