April 12, 2011 at 11:41 am
I have the following query:
SELECT mOnCallAdd.OpName,mOnCallAdd.SchedName,mOnCallAdd.FirstListing, moncallAdd.Initials,
DATEADD(MINUTE, mOnCallAdd.AddTime, DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899')) as Added,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) as OnCallDate,
DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) as OnCallEnd,
convert(smalldatetime,convert(float,T2.Timestamp)/1440-1) as Deleted
FROM mdr.dbo.mOnCallAdd
INNER JOIN (SELECT mOnCallDelete.Timestamp, mOnCallDelete.SchedName FROM mOncallDelete WHERE mOnCallDelete.TimeStamp >= datediff(mi, '1899-12-31 00:00:00.000', @datesince) AND mOnCallDelete.SchedName = @schedname) T2
ON mOnCallAdd.SchedName = T2.SchedName
WHERE mOnCallAdd.TimeStamp >= datediff(mi, '1899-12-31 00:00:00.000', @datesince) AND mOnCallAdd.SchedName = @schedname
ORDER BY OnCallDate
and what I'm trying to do is instead of showing the "added" field and "deleted" field is just to simply have a field called "activity" which will show anything that "activity" (either that something has been added or deleted, and the time and date when this was done) and it was suggested to me to use Union.
here is the ddl:
CREATE TABLE [dbo].[mOnCallAdd] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RecID] [decimal](18, 0) NOT NULL ,
[Timestamp] [int] NULL ,
[SchedName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rank] [int] NULL ,
[StartOnCallDate] [int] NULL ,
[StartOnCallTime] [int] NULL ,
[Override] [int] NULL ,
[FirstListing] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Duration] [decimal](18, 0) NULL ,
[TimeDifference] [decimal](18, 0) NULL ,
[AddDate] [int] NULL ,
[AddTime] [int] NULL ,
[Initials] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Opname] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comment] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field0] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field1] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field2] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field3] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field4] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field5] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field6] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field7] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field8] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field9] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field10] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field11] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MainSchedName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Both the moncalladd and moncalldelete tables are the same as far as structure goes.
Here is a sample line of data:
17055055426893Schedname03849010200Dr Doctor8400384901293TG8199
The output I want would be this:
Op Name SchedName FirstListing Activity OncallStart OncallEnd
___________________________________________________________________________________________________________________________________________
8467BUTTERCUPData, Sample2011-03-20 03:57:00 Added 2011-03-17 17:00:00 2011-03-1808:00:00
8467 BUTTERCUPData, Sample 2011-03-20 03:57:00 Deleted 2011-03-17 17:00:00 2011-03-18 08:00:00
April 12, 2011 at 3:38 pm
You could use
CROSS APPLY
(SELECT 'Added' AS Activity UNION ALL
SELECT 'Deleted') x
Together with
CONVERT(CHAR(19),CASE WHEN x.Activity THEN Added ELSE Deleted END,120) + ' ' + x.Activity
April 13, 2011 at 9:49 am
Lutz,
Will that work for SQL 2000? I see that cross apply will work with SQL 2005 but don't see that command being used in 2000.
April 13, 2011 at 10:06 am
APPLY was added in 2005 so that wouldn't work in 2000.
If SchedName is unique then I see no reason why a simple UNION wouldn't work. Like this:
SELECT
mOnCallAdd.OpName,
mOnCallAdd.SchedName,
mOnCallAdd.FirstListing,
moncallAdd.Initials,
'Added' AS Activity,
DATEADD(MINUTE, mOnCallAdd.AddTime,
DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899')) AS AcivityDate,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallDate,
DATEADD(MINUTE, mOnCallAdd.duration,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
FROM
mdr.dbo.mOnCallAdd
WHERE
mOnCallAdd.TimeStamp >= DATEDIFF(mi, '1899-12-31 00:00:00.000', @datesince) AND
mOnCallAdd.SchedName = @schedname
UNION
SELECT
mOnCallDelete.OpName,
mOnCallDelete.SchedName,
mOnCallDelete.FirstListing,
mOnCallDelete.Initials,
'Deleted' AS Activity,
DATEADD(MINUTE, mOnCallDelete.AddTime,
DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899')) AS AcivityDate,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallDate,
DATEADD(MINUTE, mOnCallDelete.duration,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
FROM
mdr.dbo.mOnCallDelete
WHERE
mOnCallDelete.TimeStamp >= DATEDIFF(mi, '1899-12-31 00:00:00.000', @datesince) AND
mOnCallDelete.SchedName = @schedname
ORDER BY
OnCallDate
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2011 at 10:42 am
Jack,
Thank you. That was absolutely it.
April 15, 2011 at 9:01 am
Jack,
One other thing I'm noticing. When I run my query I'm getting some results that don't make any sense. I used the date of 3/21/2011 and produced the following results in my query:
CTKA3/9/2011 4:00:00 PMWEBDeletedDATA - DATA3/30/2011 7:00:00 AM3/31/2011 5:00:00 PM
CTKA3/9/2011 3:59:00 PMWEBDeletedDATA - DATA3/30/2011 7:00:00 AM3/30/2011 5:00:00 PM
CTKA3/9/2011 9:53:00 AMWEBDeletedDATA - DATA3/25/2011 7:00:00 AM3/25/2011 5:00:00 PM
CTKA3/8/2011 8:53:00 AMWEBDeletedDATA - DATA3/25/2011 7:00:00 AM3/25/2011 5:00:00 PM
CTKA3/8/2011 8:52:00 AMWEBDeletedDATA - DATA3/25/2011 7:00:00 AM3/25/2011 5:00:00 PM
CTKA3/8/2011 8:52:00 AMWEBDeletedDATA - DATA3/25/2011 7:00:00 AM3/25/2011 5:00:00 PM
CTKA2/1/2011 1:52:00 PMWEBDeletedDATA - DATA3/31/2011 7:00:00 AM3/31/2011 5:00:00 PM
CTKA2/1/2011 1:18:00 PMWEBDeletedDATA - DATA3/28/2011 7:00:00 AM3/28/2011 5:00:00 PM
CTKA2/1/2011 12:47:00 PMAGDeletedDATA - DATA4/1/2011 5:00:00 PM4/2/2011 7:00:00 AM
and I thought I was pulling data where the activity was my "sincedate" and not the oncall start.
April 15, 2011 at 9:21 am
I'm not sure what you mean. I need the column names so I know what columns you are showing in your post as they don't appear to match the column order in the query I posted.
Also, I have no idea what the timestamp column really represents, I'm just using the where clause you provided. Are you sure that the timestamp contains the # of minutes since 12/31/1899?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 9:55 am
Jack,
Sorry I did have to modify the query a bit for the user. Here is the query as it is now:
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
mOnCallAdd.TimeStamp >= DATEDIFF(mi, '1899-12-31 00:00:00.000', @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
mOnCallDelete.TimeStamp >= DATEDIFF(mi, '1899-12-31 00:00:00.000', @sincedate) AND
mOnCallDelete.SchedName = @schedname
ORDER BY
ActivityDate DESC
The column names are
SchednameActivityDateByActivityFirstListingOncallStartOnCallEnd
and the vendor who's application uses that database tells me that that it does use that date as the starting point.
April 18, 2011 at 9:08 am
I just ran this query and this is the results that I got:
sp_getoncallresults '4/17/2011', 'ctka'
REATE procedure dbo.sp_getoncallresults
-- Add the parameters for the stored procedure here
@sincedate nvarchar(25),
@schedname nvarchar (100)
AS
BEGIN
SET NOCOUNT ON;
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
mOnCallAdd.TimeStamp >= DATEDIFF(mi, '1899-12-31 00:00:00.000', @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
mOnCallDelete.TimeStamp >= DATEDIFF(mi, '1899-12-31 00:00:00.000', @sincedate) AND
mOnCallDelete.SchedName = @schedname
ORDER BY
ActivityDate DESC
End
GO
CTKA2011-04-17 12:08:00.000KHAddedtestdata - sample2011-04-17 07:00:00.0002011-04-17 17:00:00.000
CTKA2011-04-16 09:38:00.000KHDeletedtestdata - sample2011-04-17 07:00:00.0002011-04-17 17:00:00.000
and the second result should not have been included in that query because the date is outside of the daterange that I had asked for. Can someone tell me why I get the second result?
Thank you,
Doug
April 18, 2011 at 9:45 am
There are a couple of potential things here.
1. You aren't displaying the TimeStamp column so it is likely working just as expected. The TimeStamp may not be the same as the calculation you have to get ActivityDate.
2. @sincedate is a varchar parameter and should be smalldatetime or datetime, you are getting an implicit conversion, and that could cause precision issues. Then you are doing a conversion to get an integer. There are a lot of things that could be going wrong here.
My suggestions are to display the TimeStamp column, preferably converting to a Date type so you can see what is really there. Change the datatype on @sincedate to one of the date datatypes.
Without the data I can't tell what's going on. SQL Server isn't changing the data so I'd have to assume that TimeStamp and your ActivityDate calculation are not the same.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 18, 2011 at 12:53 pm
Here's two lines of data from the moncalladd table:
17055055426893BITTAR&ESKEW03849010200YIUM 8400384901293TG 8199
27055155427287ZZOB02 03849410200CARTER, KIMBERLY900038491247LS
and here are two lines of data from the moncalldelete table
11853755426893BITTAR&ESKEW03849010200BUTLER 840 0 384811150AB8199
21853855427292ZZOB02 03849810200CHOUTEAU, MICHELLE900038491250LS
I also changed the sincedate to datetime.
April 18, 2011 at 1:00 pm
doug 40899 (4/18/2011)
Here's two lines of data from the moncalladd table:
17055055426893BITTAR&ESKEW03849010200YIUM 8400384901293TG 8199
27055155427287ZZOB02 03849410200CARTER, KIMBERLY900038491247LS
and here are two lines of data from the moncalldelete table
11853755426893BITTAR&ESKEW03849010200BUTLER 840 0 384811150AB8199
21853855427292ZZOB02 03849810200CHOUTEAU, MICHELLE900038491250LS
I also changed the sincedate to datetime.
Can you post this with the column headers? I don't know what the data means without the headers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 19, 2011 at 9:24 am
the headers for both are the same but they are
ID Rec Id TimeStamp Schedname Rank StartOncallDate StartOncallTime Overide
1 70550 55426893 BITTAR&ESKEW 0 38490 1020 0
FirstListing Duration TimeDifference AddDate AddTime Initials
YIUM 840 0 384901293TG
April 19, 2011 at 10:16 am
Thanks. A couple of things:
1. You keep using different data in the presentation so I have nothing to compare it to. If you could setup a consistent set of test data that would be great. The latest data is from May of 2005.
2. Return the timestamp column both as an integer and converted to a date, with your data to check it. DATEADD(MINUTE, timestamp, '1899-12-31 00:00:00.000').
3. Include @sincedate in the output both as a date and converted to int. DATEDIFF(mi, '1899-12-31 00:00:00.000', @sincedate)
I have to believe that SQL Server is returning exactly what you are asking for.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 19, 2011 at 1:43 pm
Jack,
I'll get you more exact data.
2. Return the timestamp column both as an integer and converted to a date, with your data to check it. DATEADD(MINUTE, timestamp, '1899-12-31 00:00:00.000').
I thought I was doing this in my where clause:
mOnCallAdd.TimeStamp >= DATEDIFF(mi, '1899-12-31 00:00:00.000', @sincedate)
3. Include @sincedate in the output both as a date and converted to int. DATEDIFF(mi, '1899-12-31 00:00:00.000', @sincedate)
or maybe I'm just confused. Do I need to cast to show both date and int?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply