August 17, 2010 at 5:09 am
Hi
This is my first post to this board and I'm hoping someone out there can help (please).
I have a script which makes a median time calculation of waiting times in an ER unit. It outputs the following median value under the fields 'PCT', 'Trust' and 'Median' (this is the sole output):
PCTTrustMedian
5A3RA374.500000
By the way the PCT and Trust values are codes for Insurer Name and Hospital location.
The code to produce the above output is as follows:
use Analyst_SQL_Area
go
declare @PCT nvarchar(50)
declare @Trust nvarchar(50)
declare @StartDate datetime
declare @EndDate datetime
set @PCT = '5A3' --('5QJ','5JF','5JG','5A3','5M8')
set @Trust = 'RA3' --('RVJ','RA7','RA3')
set @StartDate = '01 APR 2009'
set @EndDate = '30 APR 2009'
select
@PCT PCT,
@Trust Trust,
Median =
(
(select top 1
x.Duration
from
(
select top 50 percent
convert(decimal(5,1),datediff(mi,t.Arrival, t.Departure)) Duration
from
(
select
s.Arrival,
Case
when s.Departure < s.Arrival then dateadd(day,1,s.Departure)
else s.Departure
End Departure
from
(
select
convert(datetime,
cast(Year(ae.ArrivalDate) as char) +'-'+
cast(Month(ae.ArrivalDate) as char) +'-'+
cast(Day(ae.ArrivalDate) as char) +' '+
Case
when len(ae.ArrivalTime)= 0 then '00'
when len(ae.ArrivalTime)= 3 then '1' + Left(ae.ArrivalTime,1)
when len(ae.ArrivalTime)= 4 then Left(ae.ArrivalTime,2)
when len(ae.ArrivalTime)= 5 then Left(ae.ArrivalTime,2)
End
+':'+
Case
when len(ae.ArrivalTime)= 0 then '00'
else Right(ae.ArrivalTime,2)
End
+':00.000', 120) Arrival,
convert(datetime,
cast(Year(ae.ArrivalDate) as char) +'-'+
cast(Month(ae.ArrivalDate) as char) +'-'+
cast(Day(ae.ArrivalDate) as char) +' '+
Case
when len(ae.DepartureTime)= 0 then '00'
when len(ae.DepartureTime)= 3 then '1' + Left(ae.DepartureTime,1)
else Left(ae.DepartureTime,2)
End
+':'+
Case
when len(ae.DepartureTime)= 0 then '00'
when len(ae.DepartureTime)= 4 then Right(ae.DepartureTime,2)
when len(ae.DepartureTime)= 5 then Right(ae.DepartureTime,2)
else substring(ae.DepartureTime,4,2)
End
+':00.000', 120) Departure
from
zia.xiom.dbo.vw_AE_SUS ae
where
left(ae.PurchCode,3) = @PCT
and left(ae.Provcode,3) = @Trust
and ae.ArrivalDate between @StartDate and @EndDate
)s
)t
order by
convert(decimal(5,1),datediff(mi,t.Arrival, t.Departure))asc)x
order by
x.Duration desc)
+
(select top 1
y.Duration
from
(
select top 50 percent
convert(decimal(5,1),datediff(mi,t.Arrival, t.Departure)) Duration
from
(
select
s.Arrival,
Case
when s.Departure < s.Arrival then dateadd(day,1,s.Departure)
else s.Departure
End Departure
from
(
select
convert(datetime,
cast(Year(ae.ArrivalDate) as char) +'-'+
cast(Month(ae.ArrivalDate) as char) +'-'+
cast(Day(ae.ArrivalDate) as char) +' '+
Case
when len(ae.ArrivalTime)= 0 then '00'
when len(ae.ArrivalTime)= 3 then '1' + Left(ae.ArrivalTime,1)
when len(ae.ArrivalTime)= 4 then Left(ae.ArrivalTime,2)
when len(ae.ArrivalTime)= 5 then Left(ae.ArrivalTime,2)
End
+':'+
Case
when len(ae.ArrivalTime)= 0 then '00'
else Right(ae.ArrivalTime,2)
End
+':00.000', 120) Arrival,
convert(datetime,
cast(Year(ae.ArrivalDate) as char) +'-'+
cast(Month(ae.ArrivalDate) as char) +'-'+
cast(Day(ae.ArrivalDate) as char) +' '+
Case
when len(ae.DepartureTime)= 0 then '00'
when len(ae.DepartureTime)= 3 then '1' + Left(ae.DepartureTime,1)
else Left(ae.DepartureTime,2)
End
+':'+
Case
when len(ae.DepartureTime)= 0 then '00'
when len(ae.DepartureTime)= 4 then Right(ae.DepartureTime,2)
when len(ae.DepartureTime)= 5 then Right(ae.DepartureTime,2)
else substring(ae.DepartureTime,4,2)
End
+':00.000', 120) Departure
from
zia.xiom.dbo.vw_AE_SUS ae
where
left(ae.PurchCode,3) = @PCT
and left(ae.Provcode,3) = @Trust
and ae.ArrivalDate between @StartDate and @EndDate
)s
)t
order by
convert(decimal(5,1),datediff(mi,t.Arrival, t.Departure))desc)y
order by
y.Duration asc)
) /2
As you can see, the variables for 'PCT' and 'Trust' fields are currently set to '5A3' and 'RA3' respectively.
What I need to do however, is to output median values for all combinations of the 'PCT' and 'Trust' fields (commented out in the script) which are currently:
PCT = '5QJ' or '5JF' or '5JG' or '5A3' or '5M8'
each of which can have a 1 to many join with
Trust : 'RVJ' or 'RA7' or 'RA3'
i.e. To I need to output a separate result for
‘5QJ’ and ‘RA7’
‘5QJ’ and ‘RVJ’
‘5QJ’ and ‘RA3’
'5JF' and ‘RA7’
'5JF' and ‘RVJ’
'5JF' and ‘RA3’
'5JG' and ‘RA7’
'5JG' and ‘RVJ’
'5JG' and ‘RA3’
etc
I don't have much time to do this, but was wondering if there was some sort of method of feeding these combinations to the variables via a loop or somesuch. However, I’m not at all familiar with the methodologies involved.
Any help would be greatly appreciated.
August 17, 2010 at 5:38 am
Hi Richard
Can you give us some sample data please? This will get you started, and there are also notes in the link in my sig.
Cheers-- Create some sample data
CREATE TABLE #vw_AE_SUS (
PurchCode VARCHAR(1), -- <-- adjust datatype to match your table
Provcode VARCHAR(1), -- <-- adjust datatype to match your table
ArrivalDate DATETIME, -- <-- adjust datatype to match your table
ArrivalTime VARCHAR(1), -- <-- adjust datatype to match your table
DepartureTime VARCHAR(1)) -- <-- adjust datatype to match your table
INSERT INTO #vw_AE_SUS (PurchCode, Provcode, ArrivalDate, ArrivalTime, DepartureTime)
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', '' UNION ALL -- <-- put in some representative values
SELECT '', '', GETDATE(), '', ''
SELECT * FROM #vw_AE_SUS
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
August 17, 2010 at 6:47 am
Hi Chris
I've attached some data and I will read through the suggested articles.
Thanks for such a rapid response.
August 17, 2010 at 6:54 am
richard.kirby (8/17/2010)
Hi ChrisI've attached some data and I will read through the suggested articles.
Thanks for such a rapid response.
Hi Richard
Thanks for providing the data, however, for anybody to be able to use it, they would have to perform a fair amount of work to get it into a table in a db. Can you please provide say 20 or 30 rows in the format I've posted above? This allows any of the folks who read this thread to copy and paste, and begin work straight away. The data types are vital. If you use the CREATE TABLE script above, don't forget to edit the column types to exactly match yours.
Cheers
ChrisM
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
August 17, 2010 at 8:07 am
-- sample data
DROP TABLE #Sample
CREATE TABLE #Sample (PurchCode VARCHAR(3), Provcode VARCHAR(5), ArrivalDate DATE, ArrivalTime VARCHAR(5), DepartureTime VARCHAR(8))
INSERT INTO #Sample
(PurchCode, Provcode, ArrivalDate, ArrivalTime, DepartureTime)
SELECT '5A3', 'RD130', '01/02/2010', '18:13', '20:30:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '17:05', '19:30:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '19:22', '21:05:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '19:26', '20:35:00' UNION ALL
SELECT '5PL', 'RVJ00', '01/02/2010', '19:45', '20:12:00' UNION ALL
SELECT '5PL', 'RVJ00', '01/02/2010', '19:45', '21:12:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '19:50', '23:21:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '09:57', '10:20:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '15:09', '16:15:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '08:05', '09:45:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '14:25', '17:10:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '09:41', '12:34:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '10:12', '12:15:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '10:19', '12:00:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '18:29', '22:20:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '19:47', '20:10:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '10:46', '13:30:00' UNION ALL
SELECT '5QH', 'RVJ00', '01/02/2010', '03:16', '03:30:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '01:30', '03:27:00' UNION ALL
SELECT '5A3', 'RVJ00', '01/02/2010', '10:53', '14:45:00'
-- Solution
DECLARE @PCT VARCHAR(3), @Trust VARCHAR(3), @StartDate DATE, @EndDate DATE
SELECT @PCT = '5A3', @Trust = 'RVJ', @StartDate = '01/02/2010', @EndDate = '01/02/2010'
SELECT PurchCode, Provcode, Duration, Seq, PartitionSize
FROM (
SELECT Seq = ROW_NUMBER() OVER (PARTITION BY PurchCode, Provcode ORDER BY Duration),
PartitionSize = COUNT(*) OVER (PARTITION BY PurchCode, Provcode),
PurchCode, Provcode, Duration
FROM (
SELECT PurchCode, Provcode,
Duration = CONVERT(DECIMAL(5,1), DATEDIFF(mi, d.Arrival,
CASE WHEN Departure < Arrival THEN DATEADD(dd, 1, Departure) ELSE Departure END))
FROM (
SELECT PurchCode, Provcode,
Arrival = DATEADD(mi, CAST(RIGHT(ArrivalTime,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(ArrivalTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME))),
Departure = DATEADD(mi, CAST(SUBSTRING(DepartureTime,4,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(DepartureTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME)))
FROM #Sample ae
--WHERE LEFT(ae.PurchCode, 3) = @PCT
--AND LEFT(ae.Provcode, 3) = @Trust
--AND ae.ArrivalDate BETWEEN @StartDate AND @EndDate
) d
) q
) final
WHERE PartitionSize IN (1, Seq*2, Seq*2+1)
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
August 17, 2010 at 8:44 am
Hi Chris
Apologies, I've been away from my desk and had just finished populating the 'Insert' statement when your solution popped into my Inbox.
All I can say is I'm eternally grateful as it worked first time.
I shall spend the rest of the day trying to work out how you did it as I have lot's of other old scripts lying around that would benefit from this approach.
BEST WISHES AND THANKS AGAIN.
-Richard
August 17, 2010 at 9:41 am
Richard
You're welcome, come back if you have any questions.
Cheers
ChrisM
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
August 18, 2010 at 8:08 am
Hi Chris
I wonder if there is a way to amend the output.
I have some new data which is slightly different as follows:
CREATE TABLE VW_AE_SUS (
PurchCode VARCHAR(5), -- <-- adjust datatype to match your table
Provcode VARCHAR(5), -- <-- adjust datatype to match your table
ArrivalDate DATETIME, -- <-- adjust datatype to match your table
ArrivalTime VARCHAR(5), -- <-- adjust datatype to match your table
DepartureTime VARCHAR(8)) -- <-- adjust datatype to match your table
INSERT INTO VW_AE_SUS (PurchCode, Provcode, ArrivalDate, ArrivalTime, DepartureTime)
SELECT '5QJ00', 'RVJ00', GETDATE(), '16:58', '18:09:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ01', 'RVJ01', GETDATE(), '10:48', '11:10:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ02', 'RVJ02', GETDATE(), '15:15', '15:58:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ03', 'RVJ03', GETDATE(), '13:31', '14:18:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ04', 'RVJ04', GETDATE(), '17:05', '18:17:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ05', 'RA701', GETDATE(), '05:10', '12:32:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ06', 'RA702', GETDATE(), '01:47', '03:46:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ07', 'RA301', GETDATE(), '19:36', '21:17:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ08', 'RA302', GETDATE(), '15:10', '17:10:00' UNION ALL -- <-- put in some representative values
SELECT '5QJ09', 'RA303', GETDATE(), '19:13', '22:02:00' UNION ALL -- <-- put in some representative values
SELECT '5JF10', 'RVJ00', GETDATE(), '18:13', '23:38:00' UNION ALL -- <-- put in some representative values
SELECT '5JF00', 'RVJ01', GETDATE(), '17:05', '19:30:00' UNION ALL -- <-- put in some representative values
SELECT '5JF01', 'RVJ02', GETDATE(), '19:22', '21:05:00' UNION ALL -- <-- put in some representative values
SELECT '5JF02', 'RVJ03', GETDATE(), '19:26', '20:35:00' UNION ALL -- <-- put in some representative values
SELECT '5JF03', 'RA700', GETDATE(), '19:45', '20:12:00' UNION ALL -- <-- put in some representative values
SELECT '5JF04', 'RA701', GETDATE(), '19:50', '23:21:00' UNION ALL -- <-- put in some representative values
SELECT '5JF05', 'RA702', GETDATE(), '09:57', '10:20:00' UNION ALL -- <-- put in some representative values
SELECT '5JF06', 'RA303', GETDATE(), '15:09', '16:15:00' UNION ALL -- <-- put in some representative values
SELECT '5JF07', 'RA304', GETDATE(), '08:05', '09:45:00' UNION ALL -- <-- put in some representative values
SELECT '5JF08', 'RA305', GETDATE(), '08:05', '09:45:00' UNION ALL -- <-- put in some representative values
SELECT '5JF09', 'RA306', GETDATE(), '08:05', '09:45:00' UNION ALL -- <-- put in some representative values
SELECT '5JF10', 'RA307', GETDATE(), '08:05', '09:45:00' UNION ALL -- <-- put in some representative values
SELECT '5JG00', 'RVJ00', GETDATE(), '14:25', '17:10:00' UNION ALL -- <-- put in some representative values
SELECT '5JG01', 'RVJ01', GETDATE(), '09:41', '12:34:00' UNION ALL -- <-- put in some representative values
SELECT '5JG02', 'RVJ02', GETDATE(), '10:12', '12:15:00' UNION ALL -- <-- put in some representative values
SELECT '5JG03', 'RVJ03', GETDATE(), '10:19', '12:00:00' UNION ALL -- <-- put in some representative values
SELECT '5JG04', 'RA704', GETDATE(), '18:29', '22:20:00' UNION ALL -- <-- put in some representative values
SELECT '5JG05', 'RA704', GETDATE(), '19:47', '20:10:00' UNION ALL -- <-- put in some representative values
SELECT '5JG06', 'RA706', GETDATE(), '10:46', '13:30:00' UNION ALL -- <-- put in some representative values
SELECT '5JG07', 'RA707', GETDATE(), '03:16', '03:30:00' UNION ALL -- <-- put in some representative values
SELECT '5JG08', 'RA300', GETDATE(), '01:30', '03:27:00' UNION ALL -- <-- put in some representative values
SELECT '5JG09', 'RA301', GETDATE(), '10:53', '14:45:00' UNION ALL -- <-- put in some representative values
SELECT '5JG10', 'RA302', GETDATE(), '14:18', '16:15:00';
select * from VW_AE_SUS;
DECLARE @PCT VARCHAR(3), @Trust VARCHAR(3), @StartDate DATE, @EndDate DATE
SELECT @PCT = '5A3', @Trust = 'RVJ', @StartDate = '01/02/2010', @EndDate = '01/02/2010'
SELECT PurchCode, Provcode, Duration, Seq, PartitionSize
FROM (
SELECT Seq = ROW_NUMBER() OVER (PARTITION BY PurchCode, Provcode ORDER BY Duration),
PartitionSize = COUNT(*) OVER (PARTITION BY PurchCode, Provcode),
PurchCode, Provcode, Duration
FROM (
SELECT PurchCode, Provcode,
Duration = CONVERT(DECIMAL(5,1), DATEDIFF(mi, d.Arrival,
CASE WHEN Departure < Arrival THEN DATEADD(dd, 1, Departure) ELSE Departure END))
FROM (
SELECT PurchCode, Provcode,
Arrival = DATEADD(mi, CAST(RIGHT(ArrivalTime,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(ArrivalTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME))),
Departure = DATEADD(mi, CAST(SUBSTRING(DepartureTime,4,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(DepartureTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME)))
FROM VW_AE_SUS
--WHERE LEFT(ae.PurchCode, 3) = @PCT
-- AND LEFT(ae.Provcode, 3) = @Trust
-- AND ae.ArrivalDate BETWEEN @StartDate AND @EndDate
) d
) q
) final
WHERE PartitionSize IN (1, Seq*2, Seq*2+1)
Basically, the first 3 characters of the PurchCode field e.g. '5A3' and ProvCode field e.g. 'RVJ' field indicate that they are part of a larger parent organisation. However, they are sometimes entered in our database with some superflous extra characters such as e.g. '5A300', 'RVJ00 etc as shown above which can be said to indicate a child organisation. However, it's the two organisation's 'Parent' parts that I sometimes need to report on.
Basically, I need to aggregate the data under each parent code to give a single median output for duration each the parent organisation changes.
So the following output:
PurchCode ProvCode Duration
5A300 RVJ00 X Mins
5A301 RVJ01 Y mins
5A302 RVJ02 Z Mins
Would combine to become:
PurchCode ProvCode Duration
5A3 RVJ Overall median
The original script I submitted at the start of this thread outputs in this way, but I've been unable to successfully combine the two methods.
Again, anyhelp would be gratefully received as I've been banging my head for a couple of hours now.
Cheers.
August 18, 2010 at 8:27 am
Hi Richard
Your sample output values don't appear to exist in the sample data, however something like this should do the trick:
SELECT PurchCode, Provcode, Duration, Seq, PartitionSize
FROM ( -- final
SELECT Seq = ROW_NUMBER() OVER (PARTITION BY LEFT(PurchCode, 3), LEFT(Provcode, 3) ORDER BY Duration),
PartitionSize = COUNT(*) OVER (PARTITION BY LEFT(PurchCode, 3), LEFT(Provcode, 3)),
PurchCode, Provcode, Duration
FROM ( -- q
SELECT PurchCode, Provcode,
Duration = CONVERT(DECIMAL(5,1), DATEDIFF(mi, d.Arrival,
CASE WHEN Departure < Arrival THEN DATEADD(dd, 1, Departure) ELSE Departure END))
FROM ( -- d
SELECT PurchCode,
Provcode,
Arrival = DATEADD(mi, CAST(RIGHT(ArrivalTime,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(ArrivalTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME))),
Departure = DATEADD(mi, CAST(SUBSTRING(DepartureTime,4,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(DepartureTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME)))
FROM #VW_AE_SUS
--WHERE LEFT(ae.PurchCode, 3) = @PCT
-- AND LEFT(ae.Provcode, 3) = @Trust
-- AND ae.ArrivalDate BETWEEN @StartDate AND @EndDate
) d
) q
) final
WHERE PartitionSize IN (1, Seq*2, Seq*2-1) -- < changed 3rd param here
Cheers
ChrisM
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
August 18, 2010 at 10:18 am
Hi Chris
Thanks for the updated code. I'm not sure if I've done something wrong, but when I run it against our database I get the same results as before
Apologies for the previous sample. It does appear in our database, but not in the extract I used - sorry to confuse the issue.
I should have used something like this as an example so that the sample does apply:
Existing output:
PurchCodeProvCode Duration
'5QJ00', 'RVJ00' A
'5QJ01', ' 'RVJ01' B
'5QJ02', 'RVJ02' C
'5QJ03', 'RVJ03' D
'5QJ04', 'RVJ04' E
While what would be very useful is:
PurchCodeProvCode Duration
'5QJ' 'RVJ' Median Value for all ‘RVJ%’ Provider
codes against all '5QJ%'
PurchCodes
The output from the most recent code is:
PurchCodeProvcodeDurationSeqPartitionSize
5JF08 RA305100.035
5JF03 RA70027.023
5JF01 RVJ02103.024
5JG10 RA302117.023
5JG05 RA70423.024
5JG02 RVJ02123.024
5QJ08 RA302120.023
5QJ06 RA702119.012
5QJ03 RVJ0347.035
Any additional help would be great.
Cheers
August 18, 2010 at 10:23 am
Hi Richard
Try changing the innermost select
SELECT PurchCode = LEFT(PurchCode, 3),
Provcode = LEFT(Provcode, 3),
Arrival = DATEADD(mi, CAST(RIGHT(ArrivalTime,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(ArrivalTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME))),
Departure = DATEADD(mi, CAST(SUBSTRING(DepartureTime,4,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(DepartureTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME)))
FROM #VW_AE_SUS
Cheers
ChrisM
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
August 19, 2010 at 4:39 am
Hi Chris
This is brilliant, but I'm now trying to understand exactly how the following pieces of code retrieve a median value:
SELECT Seq = ROW_NUMBER() OVER (PARTITION BY PurchCode, Provcode ORDER BY Duration),
PartitionSize = COUNT(*) OVER (PARTITION BY PurchCode, Provcode),
PurchCode, Provcode, Duration
I understand the rownumber and partitioning, but not how the latter combines with the following to produce a median:
WHERE PartitionSize IN (1, Seq*2, Seq*2+1)
If you could give me a brief explanation that would be great and enable me to share your syntax with my colleagues.
Thanks for your help so far - you've been very helpful and the SQL lesson very useful -I can envisage many circumstances where we will be reusing this code in our organisation.
Cheers
-Richard
August 19, 2010 at 5:58 am
Sure. If you remove the filter and instead mark the selected rows in the SELECT, you can see exactly what's happening...
DROP TABLE #VW_AE_SUS
CREATE TABLE #VW_AE_SUS (
PurchCode VARCHAR(5),
Provcode VARCHAR(5),
ArrivalDate DATETIME,
ArrivalTime VARCHAR(5),
DepartureTime VARCHAR(8))
INSERT INTO #VW_AE_SUS (PurchCode, Provcode, ArrivalDate, ArrivalTime, DepartureTime)
SELECT '5QJ00', 'RVJ00', GETDATE(), '16:58', '18:09:00' UNION ALL
SELECT '5QJ01', 'RVJ01', GETDATE(), '10:48', '11:10:00' UNION ALL
SELECT '5QJ02', 'RVJ02', GETDATE(), '15:15', '15:58:00' UNION ALL
SELECT '5QJ03', 'RVJ03', GETDATE(), '13:31', '14:18:00' UNION ALL
SELECT '5QJ04', 'RVJ04', GETDATE(), '17:05', '18:17:00' UNION ALL
SELECT '5QJ05', 'RA701', GETDATE(), '05:10', '12:32:00' UNION ALL
SELECT '5QJ06', 'RA702', GETDATE(), '01:47', '03:46:00' UNION ALL
SELECT '5QJ07', 'RA301', GETDATE(), '19:36', '21:17:00' UNION ALL
SELECT '5QJ08', 'RA302', GETDATE(), '15:10', '17:10:00' UNION ALL
SELECT '5QJ09', 'RA303', GETDATE(), '19:13', '22:02:00' UNION ALL
SELECT '5JF10', 'RVJ00', GETDATE(), '18:13', '23:38:00' UNION ALL
SELECT '5JF00', 'RVJ01', GETDATE(), '17:05', '19:30:00' UNION ALL
SELECT '5JF01', 'RVJ02', GETDATE(), '19:22', '21:05:00' UNION ALL
SELECT '5JF02', 'RVJ03', GETDATE(), '19:26', '20:35:00' UNION ALL
SELECT '5JF03', 'RA700', GETDATE(), '19:45', '20:12:00' UNION ALL
SELECT '5JF04', 'RA701', GETDATE(), '19:50', '23:21:00' UNION ALL
SELECT '5JF05', 'RA702', GETDATE(), '09:57', '10:20:00' UNION ALL
SELECT '5JF06', 'RA303', GETDATE(), '15:09', '16:15:00' UNION ALL
SELECT '5JF07', 'RA304', GETDATE(), '08:05', '09:45:00' UNION ALL
SELECT '5JF08', 'RA305', GETDATE(), '08:05', '09:45:00' UNION ALL
SELECT '5JF09', 'RA306', GETDATE(), '08:05', '09:45:00' UNION ALL
SELECT '5JF10', 'RA307', GETDATE(), '08:05', '09:45:00' UNION ALL
SELECT '5JG00', 'RVJ00', GETDATE(), '14:25', '17:10:00' UNION ALL
SELECT '5JG01', 'RVJ01', GETDATE(), '09:41', '12:34:00' UNION ALL
SELECT '5JG02', 'RVJ02', GETDATE(), '10:12', '12:15:00' UNION ALL
SELECT '5JG03', 'RVJ03', GETDATE(), '10:19', '12:00:00' UNION ALL
SELECT '5JG04', 'RA704', GETDATE(), '18:29', '22:20:00' UNION ALL
SELECT '5JG05', 'RA704', GETDATE(), '19:47', '20:10:00' UNION ALL
SELECT '5JG06', 'RA706', GETDATE(), '10:46', '13:30:00' UNION ALL
SELECT '5JG07', 'RA707', GETDATE(), '03:16', '03:30:00' UNION ALL
SELECT '5JG08', 'RA300', GETDATE(), '01:30', '03:27:00' UNION ALL
SELECT '5JG09', 'RA301', GETDATE(), '10:53', '14:45:00' UNION ALL
SELECT '5JG10', 'RA302', GETDATE(), '14:18', '16:15:00'
SELECT
PurchCode,
Provcode,
Duration,
Seq, -- row number within partition (ordered by duration)
PartitionSize, -- partition row count
ChooseMeAsMedian = CASE
WHEN PartitionSize = 1 THEN 'Y' -- only one row in the partition
WHEN PartitionSize = Seq*2 THEN 'Y' -- even number of rows in partition
WHEN PartitionSize = Seq*2-1 THEN 'Y' -- odd number of rows in partition
ELSE 'N' END
FROM (
SELECT Seq = ROW_NUMBER() OVER (PARTITION BY PurchCode, Provcode ORDER BY Duration),
PartitionSize = COUNT(*) OVER (PARTITION BY PurchCode, Provcode),
PurchCode,
Provcode,
Duration
FROM (
SELECT PurchCode,
Provcode,
Duration = CONVERT(DECIMAL(5,1), DATEDIFF(mi, d.Arrival,
CASE WHEN Departure < Arrival THEN DATEADD(dd, 1, Departure) ELSE Departure END))
FROM (
SELECT
PurchCode = LEFT(PurchCode, 3),
Provcode = LEFT(Provcode, 3),
Arrival = DATEADD(mi, CAST(RIGHT(ArrivalTime,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(ArrivalTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME))),
Departure = DATEADD(mi, CAST(SUBSTRING(DepartureTime,4,2) AS SMALLINT),
DATEADD(hh, CAST(LEFT(DepartureTime,2) AS SMALLINT),
CAST(ArrivalDate AS DATETIME)))
FROM #VW_AE_SUS
) d
) q
) final
--WHERE PartitionSize IN (1, Seq*2, Seq*2-1) -- remove the filter to return all rows
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
August 19, 2010 at 9:01 am
Hi Chris
That is brilliantly succinct.
Many thanks for all your assistance. I've run the query against a live database (test database earlier) and it also runs about 10 times faster than the original script.
Cheers and have a good day.
August 19, 2010 at 3:48 pm
Chris Morris-439714 (8/18/2010)
WHERE PartitionSize IN (1, Seq*2, Seq*2-1) -- < changed 3rd param hereCheers
ChrisM
This makes it seem like 1 is a special case when it's actually an exemplar of Seq*2-1. Including unnecessary elements makes your code harder to understand. Admittedly, it doesn't make that much difference in this case.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply