January 8, 2010 at 6:08 am
I'm trying to calculate a KPI comparing dates of planned and actual events. the reuslts are a mixture of positive and negative vlaues. I've used ABS to get the correct number but this does not return the negative.
SELECT planned.ID, planned.Source_ID, planned.Event_ID, planned.User_ID, planned.Period,
(SELECT TOP (1) ABS(DATEDIFF(dd, Period, planned.Period)) AS Expr1
FROM Event AS actual
WHERE (Source_ID = planned.Source_ID) AND (EventType_ID IN (5, 6))
ORDER BY Expr1) AS 'KPI (Days Over/Under Planned [+ / -])', Source.Name
FROM Event AS planned INNER JOIN
DataSource ON planned.DataSource_ID = DataSource.ID
WHERE (planned.Period < DATEADD(mm, - 1, GETDATE())) AND (planned.EventType_ID = 10)
any ideas?
January 8, 2010 at 6:28 am
Table(s) structure (CREATE TABLE statement(s)), sample data (a series of INSERT INTO statements for the table(s)), expected results based on the sample data.
This will help us help you.
January 8, 2010 at 6:50 am
SELECT planned.ID,
planned.Source_ID,
planned.Event_ID,
planned.User_ID,
planned.Period,
(SELECT TOP (1)
ABS(DATEDIFF(dd, Period, planned.Period)) AS Expr1
FROM Event AS actual
WHERE (Source_ID = planned.Source_ID)
AND (EventType_ID IN (5, 6))
ORDER BY Expr1) AS 'KPI (Days Over/Under Planned [+ / -])',
Source.Name
FROM Event AS planned
INNER JOIN DataSource ON planned.DataSource_ID = DataSource.ID
WHERE (planned.Period < DATEADD(mm, - 1, GETDATE()))
AND (planned.EventType_ID = 10)
Rewrite your correlated subquery as a derived table and JOIN it in the FROM list.
Make sure that all your table references actually exist - where Source.Name coming from?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2010 at 6:55 am
Example from 'event' table:
IDSource_IDEventType_ID Use_ID Period
34022 10 1 2009-03-12 11:58:36.013
34135 11 1 2009-03-12 12:00:19.827
34544 10 4 2009-03-13 00:12:17.717
34825 11 3 2009-03-14 00:12:19.133
35612 5 1 2009-03-17 00:12:23.880
36428 6 2 2009-03-18 00:12:24.257
January 8, 2010 at 6:57 am
whitlander (1/8/2010)
Example from 'event' table:IDSource_IDEventType_ID Use_ID Period
34022 10 1 2009-03-12 11:58:36.013
34135 11 1 2009-03-12 12:00:19.827
34544 10 4 2009-03-13 00:12:17.717
34825 11 3 2009-03-14 00:12:19.133
35612 5 1 2009-03-17 00:12:23.880
36428 6 2 2009-03-18 00:12:24.257
Please read Lynn's post.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2010 at 6:58 am
whitlander (1/8/2010)
Example from 'event' table:IDSource_IDEventType_ID Use_ID Period
34022 10 1 2009-03-12 11:58:36.013
34135 11 1 2009-03-12 12:00:19.827
34544 10 4 2009-03-13 00:12:17.717
34825 11 3 2009-03-14 00:12:19.133
35612 5 1 2009-03-17 00:12:23.880
36428 6 2 2009-03-18 00:12:24.257
That's nice, but it isn't what I requested.
January 8, 2010 at 7:04 am
thanks for your help guys.
I'm not sure how to re-write the sub query, what functions do I use to return the negative value?
January 8, 2010 at 7:06 am
You'd get a lot better help from us (and others) if you provide us with what I have asked you to provide. For more on this, you may want to read the first article i have referenced below in my signature block regarding asking for assistance.
January 8, 2010 at 7:09 am
Lynn Pettis (1/8/2010)
whitlander (1/8/2010)
Example from 'event' table:IDSource_IDEventType_ID Use_ID Period
34022 10 1 2009-03-12 11:58:36.013
34135 11 1 2009-03-12 12:00:19.827
34544 10 4 2009-03-13 00:12:17.717
34825 11 3 2009-03-14 00:12:19.133
35612 5 1 2009-03-17 00:12:23.880
36428 6 2 2009-03-18 00:12:24.257
That's nice, but it isn't what I requested.
Please spend a little time making up sample data scripts, as Lynn suggested. That's the time-consuming part. Since anyone who wishes to contribute to this thread will be unable to test any code without it, it's in your best interests to do so. The problem you are facing with your query appears to be trivial, or rather, with a little tweaking it will be.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2010 at 8:02 am
whitlander (1/8/2010)
thanks for your help guys.I'm not sure how to re-write the sub query, what functions do I use to return the negative value?
I've got a solution ready to test, if you have some data scripts?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2010 at 8:40 am
Is this okay
event table------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Event](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[EventType_ID] [int] NOT NULL,
[User_ID] [int] NOT NULL,
[Period] [datetime] NULL,
CONSTRAINT [PK_Event] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-------
EventType table----
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventType](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Description] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_EventType] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
------------
Source table--------
CREATE TABLE [dbo].[Source](
[ID] [smallint] NOT NULL,
[Name] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[LastUpdated] [datetime] NULL,
[FirstDate] [datetime] NULL,
[LastDate] [datetime] NULL,
[UpdateFrequencyID] [numeric](18, 0) NULL,
[IsAggregatedData] [bit] NULL,
[Info] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[SourceTableName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[SourceTableIDName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_DataSource] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----------------
January 8, 2010 at 8:41 am
Sample data? INSERT INTO statements.
January 8, 2010 at 9:26 am
Lynn Pettis (1/8/2010)
Sample data? INSERT INTO statements.
Hi Whitlander
without this essential information, I can't test and complete the code I've written and have to leave here soon because trains are hammered by the weather. Here's what I have so far - it brings both date(time) values together into a single row so you can compare them by eye - something which you should always do. Achieving your result from here should be pretty straightforward.
SELECT planned.ID,
planned.Source_ID,
planned.Event_ID,
planned.[User_ID],
planned.Period,
Actual.Period
FROM [Event] AS planned
INNER JOIN DataSource ON planned.DataSource_ID = DataSource.ID
LEFT JOIN (
SELECT Source_ID, MIN(Period) AS Period
FROM [Event]
GROUP BY Source_ID
WHERE actual.EventType_ID IN (5, 6)
) actual ON actual.Source_ID = planned.Source_ID
WHERE (planned.Period < DATEADD(mm, - 1, GETDATE()))
AND (planned.EventType_ID = 10)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply