August 18, 2011 at 11:03 am
I have the following SP.
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
FROM
mdr.dbo.mOnCallAdd
WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))
BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND 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
FROM
mdr.dbo.mOnCallDelete
WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))
BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
mOnCallDelete.SchedName = @schedname
this SP was built on a pre existing SP that showed activity based on the value of "added" or "deleted," which was simply done by these two lines:
'Added' AS Activity,
'Deleted' AS Activity,
and what I'm noticing is that when I run my SP that it's returning values for both the added and deleted. I need to just show the ones that have been added, but not deleted. What would be the best way to do that with the SP that I currently have?
Thank you
August 18, 2011 at 11:09 am
You'd limit the top query. Add something to the Where clause that will filter out deleted calls. It looks like that's probably going to be something on the FirstListing column, but I could easily be wrong about that detail.
Can't get more specific without table definitions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2011 at 12:43 pm
Table definitions are as follows:
StartDate Int
StartTime Int
Firstlisting nvarchar
duration decimal
adddate int
addtime int
Do you need more than that?
August 18, 2011 at 8:51 pm
Are you saying that both the "add" and "delete" tables have exactly the same columns? If so, what in the tables relates "Adds" to "Deletes"?
--Jeff Moden
August 19, 2011 at 9:03 am
Jeff,
here's the original query this was built off of:
SELECT
mOnCallAdd.SchedName,
DATEADD(MINUTE, mOnCallAdd.AddTime,
DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899')) AS ActivityDate,
moncallAdd.Initials as [By],
'Added' AS Activity,
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
FROM
mdr.dbo.mOnCallAdd
WHERE
DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899') >= @sincedate AND
mOnCallAdd.SchedName = @schedname
UNION
SELECT
mOnCallDelete.SchedName,
DATEADD(MINUTE, mOnCallDelete.AddTime,
DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899')) AS ActivityDate,
mOnCallDelete.Initials as [By],
'Deleted' AS Activity,
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
FROM
mdr.dbo.mOnCallDelete
WHERE
DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899') >= @sincedate AND
mOnCallDelete.SchedName = @schedname
ORDER BY
ActivityDate DESC
you can see where the "added" and "deleted" were defined as Activity. When I run the query that I have now, I'm showing all of the data it seems, and I simply need to see where something was "added" and don't need to see the "deleted" data.
August 19, 2011 at 12:49 pm
If I modify my query to be this:
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,
'Added' AS Activity
FROM
mdr.dbo.mOnCallAdd
WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))
BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND 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,
'Deleted' AS Activity
FROM
mdr.dbo.mOnCallDelete
WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))
BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
mOnCallDelete.SchedName = @schedname
and then run it, here is a sample of some of the results I get:
HEART HOSP - MIDIDDODI 2011-08-19 07:00:00.0002011-08-19 13:00:00.000Added
HEART HOSP - MOORE 2011-08-19 07:00:00.0002011-08-19 18:00:00.000Deleted
NAMC - LYSON 2011-08-19 07:00:00.0002011-08-19 12:00:00.000Deleted
NAMC - LYSON 2011-08-19 12:00:00.0002011-08-19 18:00:00.000Added
NAMC - LYSON 2011-08-19 12:00:00.0002011-08-19 18:00:00.000Deleted
and the only results that I want here are the results which show up as "added" and not "deleted"
August 19, 2011 at 2:21 pm
doug 40899 (8/19/2011)
and then run it, here is a sample of some of the results I get:HEART HOSP - MIDIDDODI 2011-08-19 07:00:00.0002011-08-19 13:00:00.000Added
HEART HOSP - MOORE 2011-08-19 07:00:00.0002011-08-19 18:00:00.000Deleted
NAMC - LYSON 2011-08-19 07:00:00.0002011-08-19 12:00:00.000Deleted
NAMC - LYSON 2011-08-19 12:00:00.0002011-08-19 18:00:00.000Added
NAMC - LYSON 2011-08-19 12:00:00.0002011-08-19 18:00:00.000Deleted
and the only results that I want here are the results which show up as "added" and not "deleted"
My apologies but that served only to confuse my meager mind even more. If you don't want any "deleted", why not just get rid of the the second part of the query from the UNION on down?
--Jeff Moden
August 19, 2011 at 2:36 pm
Jeff,
I've tried that and didn't get back all of the correct results. I did a line by line comparison for running my query with everything after the union statement removed and with the union statement left in. When I ran it without .. there were a few missing lines of data, when I ran it with the union, all of it is there.
August 20, 2011 at 7:43 pm
doug 40899 (8/19/2011)
Jeff,I've tried that and didn't get back all of the correct results. I did a line by line comparison for running my query with everything after the union statement removed and with the union statement left in. When I ran it without .. there were a few missing lines of data, when I ran it with the union, all of it is there.
Then I'm lost as to how to help because I thought that the only thing returned by the second part of the query were rows that were 'deleted'.
--Jeff Moden
August 21, 2011 at 5:52 am
doug 40899 (8/19/2011)
Jeff,I've tried that and didn't get back all of the correct results. I did a line by line comparison for running my query with everything after the union statement removed and with the union statement left in. When I ran it without .. there were a few missing lines of data, when I ran it with the union, all of it is there.
It does not sound credible.
Copy your "UNION" query and paste it right below in the same window of Query Analyzer or SSMS.
Comment the key word "UNION" and add "DISTINCT" after each select.
SELECT DISTINCT
.....
-- UNION
SELECT DISTINCT
.....
Don't change anything else.
Run all 3 queries.
What are the number of records returned by each of these 3 queries?
_____________
Code for TallyGenerator
August 22, 2011 at 1:57 pm
Sergiy,
I did as you suggested, and ran all 3 queries.
Query 1 returned 23 lines of data
Query 2 returned 10 lines of data
Query 3 returned 33 lines of data
Now what I did notice is even though the OncallStartTime is within the constraints that I put on it, the OncallEndTime is sometimes before the current time. Here is an example:
NAMC - CIPLEU2011-08-22 07:00:00.000 2011-08-22 12:00:00.000Added
Also the requirements for this data has changed a bit. What I'm needing to do now is as follows:
I need to be able to search for OnCallEndTime that would end past the current time but that would also match OncallStartTime before current time and lastly only present data that the activity is listed as "Added" and not deleted.
I hope that makes sense.
Thank you
Doug
August 22, 2011 at 3:18 pm
Let's clear with this first:
doug 40899 (8/19/2011)
Jeff,... there were a few missing lines of data, when I ran it with the union, all of it is there.
Apparently, it's not quite true:
doug 40899 (8/22/2011)
Sergiy,I did as you suggested, and ran all 3 queries.
Query 1 returned 23 lines of data
Query 2 returned 10 lines of data
Query 3 returned 33 lines of data
23+10 = 33.
So. all line are there when you run it without UNION.
Is it correct?
_____________
Code for TallyGenerator
August 23, 2011 at 9:32 am
Sergiy,
Ok so I have this revised query now:
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
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 = 'capital neph'
but when I run it, I'm still getting duplicate information. Here's how I know. If I run the original query I am shown this sample data:
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 12:00:00.000 Deleted
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 18:00:00.000 Deleted
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 18:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 12:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 13:00:00.000 Added
Leaving me with the one line on the bottom as my only "true" return. (since the add's and deleted's cancel out the other entries)
and when I run the revised query, here is the data that I'm returned back:
BRACK & HEALTH S.- MAIDMENT2011-08-23 07:00:00.0002011-08-23 18:00:00.000
BRACK & HEALTH S.- MAIDMENT2011-08-23 07:00:00.0002011-08-23 12:00:00.000
so obviously something's not giving me the complete results and I'm not sure what. Any idea why I'm not shown just the single added entry?
Thank you
Doug
August 24, 2011 at 6:40 pm
doug 40899 (8/23/2011)
Leaving me with the one line on the bottom as my only "true" return. ([font="Arial Black"]since the add's and deleted's cancel out the other entries[/font])
AH! Now I finally understand the problem you're trying to solve.
Take a look at the first link in my signature line below. If you'll take the time to make some "readily consumable" data for me as instructed in that article, I'll be happy to fix you up with some code (actually, someone will probably beat me to it if readily consumable data is made available) 😉
--Jeff Moden
August 25, 2011 at 10:47 am
Jeff,
I've had to modify the query a bit, because now we're using the adddate and addtime to be able to eliminate duplicate data. So here's the query as it stands now:
SELECT a.* FROM
(SELECT
moncallAdd.FirstListing,
Dateadd(MINUTE, moncalladd.addtime,
DateAdd(Day,moncalladd.adddate,'12/31/1899')) as AddStart,
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,
'Added' AS Activity
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 = 'capital neph') a
LEFT JOIN
(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,
'Deleted' AS Activity
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 = 'capital neph') b
ON a.FirstListing = b.FirstListing
and a.oncallstart = b.oncallstart
and a.oncallend = b.oncallend
and I know that I need a max clause and a group by clause but so far I've been unable to get those to work. Also here is the data as per your instructions.
IF OBJECT_ID('TempDB..#moncalladd','U') IS NOT NULL
DROP TABLE #moncalladd
--===== Create the test table with
CREATE TABLE #moncalladd
(
FIRSTLISTING NVARCHAR,
StartOncallDate INT,
StartOncallTime INT,
Duration DECIMAL,
Schedname NVARCHAR,
AddTime INT,
AddDate INT
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #moncalladd ON
--===== Insert the test data into the test table
INSERT INTO #moncalladd
(FIRSLISTING, STARTONCALLDATE, STARTONCALLTIME, DURATION, ADDTIME, ADDDATE)
Select'YIUM','38490','1020','840','BITTAR&ESKEW','1293','38490',UNION ALL
Select'CARTER, KIMBERLY','38494','1020','900','ZZOB02','247','38491',UNION ALL
Select'CARTER, KIMBERLY','38502','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38505','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38507','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38509','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38516','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38523','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38530','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38537','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38540','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38546','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38551','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38558','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38561','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38565','1020','900','ZZOB02','249','38491',UNION ALL
Select'CARTER, KIMBERLY','38574','1020','900','ZZOB02','249','38491',UNION ALL
Select'CHOUTEAU, MICHELLE','38495','1020','900','ZZOB02','249','38491',UNION ALL
Select'CHOUTEAU, MICHELLE','38498','1020','900','ZZOB02','250','38491',UNION ALL
Select'CHOUTEAU, MICHELLE','38503','1020','900','ZZOB02','250','38491'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #moncalladd OFF
I have LOTS more data if that isn't enough.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply