Hi Thomas,
Could you try with two parameters? I nearly always use two to select a range of dates and defime the procedure along these lines.
USE [CLARITY];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/*
=====================================
Author: Gethyn Owen
Create date: 07/09/2015
Description: Susceptibility Tests
For AmSurv Report
=====================================
*/
CREATE PROCEDURE [dbo].[SP_GO_AmSurve_SUSCEPTIBILITY] (@Start VARCHAR(50), @End VARCHAR(50))
AS
DECLARE
@StartDate DATETIME,
@EndDate DATETIME
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @StartDate = EPIC_UTIL.EFN_DIN (@Start)
SET @endDate = EPIC_UTIL.EFN_DIN (@End)
SELECT DISTINCT
(V_LAB_RESULTS.SPECIMEN_ID + '|') + (CAST (CLARITY_ORGANISM.ORGANISM_ID AS VARCHAR (18))) AS SORT,
Cast(CLARITY_COMPONENT.ABBREVIATION as VarChar(9)) AS [Antibiotic Code],
CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) AS LastVerifiedDate,
CASE
WHEN ZC_SUSCEPT.ABBR IS NOT NULL
THEN ZC_SUSCEPT.ABBR
ELSE
CASE
WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL
THEN 'U'
WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL
THEN ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'
WHEN ZC_SUSCEPT.ABBR IS NULL
THEN ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
END
END AS RES_MICRO_SUSC_group
FROM CLARITY.dbo.RES_MICRO_CULTURE RES_MICRO_CULTURE
LEFT OUTER JOIN CLARITY.dbo.CLARITY_ORGANISM CLARITY_ORGANISM
ON (RES_MICRO_CULTURE.CULT_ORG_ID = CLARITY_ORGANISM.ORGANISM_ID)
LEFT OUTER JOIN CLARITY.dbo.RES_DB_MAIN RES_DB_MAIN
ON (RES_DB_MAIN.RESULT_ID = RES_MICRO_CULTURE.RESULT_ID)
FULL OUTER JOIN CLARITY.dbo.RES_MICRO_SUSC RES_MICRO_SUSC
ON (RES_MICRO_SUSC.CULTURE_ID = RES_MICRO_CULTURE.RESULT_ID)
AND (RES_MICRO_SUSC.UNIQUE_ORGANISM_ID = RES_MICRO_CULTURE.LINE)
LEFT OUTER JOIN CLARITY.dbo.V_LAB_RESULTS V_LAB_RESULTS
ON (RES_DB_MAIN.RESULT_ID = V_LAB_RESULTS.RESULT_ID)
LEFT OUTER JOIN CLARITY.dbo.CLARITY_COMPONENT CLARITY_COMPONENT
ON (RES_MICRO_SUSC.SUSC_ANTIBIOTIC_ID = CLARITY_COMPONENT.COMPONENT_ID)
LEFT OUTER JOIN CLARITY.dbo.METHOD_INFO METHOD_INFO
ON (RES_MICRO_SUSC.SUSC_TEST_METH_ID = METHOD_INFO.METHOD_ID)
RIGHT OUTER JOIN CLARITY.dbo.SPEC_DB_MAIN SPEC_DB_MAIN
ON (RES_MICRO_SUSC.SPECIMEN_ID = SPEC_DB_MAIN.SPECIMEN_ID)
LEFT OUTER JOIN CLARITY.dbo.TEST_MSTR_DB_MAIN TEST_MSTR_DB_MAIN
ON (RES_MICRO_SUSC.SUSC_TEST_ID = TEST_MSTR_DB_MAIN.TEST_ID)
LEFT OUTER JOIN CLARITY.dbo.ZC_SUSCEPT ZC_SUSCEPT
ON (RES_MICRO_SUSC.SUSC_INTERPRETATN_C = ZC_SUSCEPT.SUSCEPT_C)
LEFT OUTER JOIN CLARITY.dbo.RTYPE_DB_MAIN RTYPE_DB_MAIN
ON (RES_MICRO_SUSC.RESULT_TYPE_ID = RTYPE_DB_MAIN.RESULT_TYPE_ID)
WHERE (RES_DB_MAIN.RES_VAL_STATUS_C = 9
AND RES_MICRO_SUSC.CULTURE_ID IS NOT NULL
and CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) Between @StartDate and @EndDate
AND RES_MICRO_SUSC.RESULT_ID Is NOT NULL)
AND CLARITY_COMPONENT.ABBREVIATION <> 'ADNR'
AND CASE
WHEN ZC_SUSCEPT.ABBR IS NOT NULL
THEN ZC_SUSCEPT.ABBR
ELSE
CASE
WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL
THEN 'U'
WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL
THEN ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'
WHEN ZC_SUSCEPT.ABBR IS NULL
THEN ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
END
END <> 'U'
END
GO
My two parameters, @Start and @End can be entered as a date, timestamp or a relative period, ie T-5 for a 5 days ago, WB-1 for first day of last week. I have a function (EPIC_UTIL.EFN_DIN)which converts them to dd/mm/yyyy hh:mm:ss and saves them as @StartDate and @EndDate and then they get applied in the where clause of the sql code.
The code for the function is like this:
USE [CLARITY];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION [EPIC_UTIL].[EFN_DIN]
(
@i_vDateString VARCHAR(255)
)
RETURNS datetime
AS
BEGIN
-- Variables
DECLARE @vDate AS varchar(255)
DECLARE @dDate AS datetime
DECLARE @vDay as varchar(255)
DECLARE @vMonth as varchar(255)
DECLARE @vYear as varchar(255)
DECLARE @iSecs as int
DECLARE @iDays as int
DECLARE @iInstant as bigint
IF @i_vDateString is NULL RETURN NULL
-- Set to Upper case and deleting spaces
-- Be careful when entering numbers separated like "t + 2 4"
SET @vDate = upper(replace(@i_vDateString,' ',''))
-- Format Checking
-- Format DTE.xxxxx
IF @vDate LIKE 'DTE.%'
return DATEADD(dd,FLOOR(CONVERT(numeric(18,2),SUBSTRING(@vDate,5,LEN(@vDate)-4))),'18401231')
-- Format DAT.xxxxx
IF @vDate LIKE 'DAT.%'
return DATEADD(dd,121531 - CEILING(CONVERT(numeric(18,2),SUBSTRING(@vDate,5,LEN(@vDate)-4))),'18401231')
-- Format INST.xxxxx
IF @vDate LIKE 'INST.%'
BEGIN
SET @iInstant=CONVERT(bigint,SUBSTRING(@vDate,6,LEN(@vDate)-5))
SET @iDays=@iInstant/86400 --Get the days out of the instant
SET @iSecs=@iInstant % 86400 --Get the seconds out of the instant that are less than a day
return DATEADD(ss,@iSecs,DATEADD(dd,@iDays,'18401231'))
END
-- Format mmddyyyy
IF @vDate LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
return CONVERT(datetime,SUBSTRING(@vDate,1,2)+'/'+SUBSTRING(@vDate,3,2)+'/'+SUBSTRING(@vDate,5,4))
-- Date accepted by T-SQL
IF CHARINDEX('-',@i_vDateString)=5
RETURN DATEADD(dd,0, DATEDIFF(dd,0,CONVERT(datetime,@i_vDateString,120)))
else
IF ISDATE(@i_vDateString)<>0
RETURN DATEADD(dd,0, DATEDIFF(dd,0,CONVERT(datetime,@i_vDateString)))
-- Relative to day interval
IF PATINDEX('T%',@vDate) = 1 --Today
BEGIN
SET @vDate=SUBSTRING(@vDate,2,LEN(@vDate))
IF @vDate LIKE '[+-]_%' -- Add or substract days
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(d,CAST(@vDate as int),getdate())))
ELSE IF @vDate='' RETURN DATEADD(dd,0, DATEDIFF(dd,0,getdate())) -- Today
ELSE RETURN NULL
END
-- Relative to time
IF PATINDEX ('N%',@vDate)= 1 -- Now
BEGIN
SET @vDate=SUBSTRING(@vDate,2,LEN(@vDate))
IF @vDate LIKE '[+-]_%' --Add or substract minutes
RETURN DATEADD(mi,CAST(@vDate as int),getdate())
ELSE IF @vDate='' RETURN getdate() --NOW
ELSE RETURN NULL
END
-- Month interval
-- Month BEGINNING
IF PATINDEX('MB%',@vDate) = 1
BEGIN
SET @dDate=DATEADD(d,-day(getdate())+1,getdate()) -- Set the beginning of the month
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(m,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate)))
END
-- Month Ending
IF PATINDEX('ME%',@vDate) = 1
BEGIN
SET @dDate=DATEADD(d,-day(getdate())+1,getdate()) -- Set the beginning of the month
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(d,-1,DATEADD(m,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)+1,@dDate))))
END
IF PATINDEX('M_%',@vDate) = 1 --Month
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(m,CAST(SUBSTRING(@vDate,2,len(@vDate)-1) as int),getdate())))
-- Relative to week interval
IF PATINDEX('WB%',@vDate) = 1 --Week beginning
BEGIN
SET @dDate = getdate() + 1 - DATEPART(dw, getdate()) + CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)*7 -- Set the beginning of the week
RETURN DATEDIFF(dd,0,@dDate)
END
IF PATINDEX('WE%',@vDate) = 1 --Week ending
BEGIN
SET @dDate = getdate() + 7 - DATEPART(dw, getdate()) + CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)*7 -- Set the ending of the week
RETURN DATEDIFF(dd,0,@dDate)
END
IF PATINDEX('W_%',@vDate) = 1 --Week
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(ww,CAST(SUBSTRING(@vDate,2,LEN(@vDate)-1) as int),getdate())))
-- Relative to year interval
IF PATINDEX('YB%',@vDate) = 1 --Year beginning
BEGIN
SET @dDate=CAST('01/01/' + CAST(year(GETDATE()) as varchar(4)) as datetime) -- Set the beginning of the year
RETURN DATEADD(yy,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate)
END
IF PATINDEX('YE%',@vDate) = 1 --Year ending
BEGIN
SET @dDate=CAST('01/01/' + CAST(year(GETDATE()) as varchar(4)) as datetime) -- Set the beginning of the year
RETURN DATEADD(d,-1,DATEADD(yy,1+CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate))
END
IF PATINDEX('Y_%',@vDate) = 1 --Year
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(yy,CAST(SUBSTRING(@vDate,2,LEN(@vDate)-1) as int),getdate())))
-- M,D,YYYY
IF PATINDEX('_%,_%,_%',@vDate) = 1
BEGIN
SET @vMonth = LEFT(@vDate,CHARINDEX(',',@vDate)-1) -- get the month
SET @vDate = SUBSTRING(@vDate,LEN(@vMonth)+2,LEN(@vDate)) -- Advance the date to read
SET @vDay = LEFT(@vDate,CHARINDEX(',',@vDate)-1) -- get the day
SET @vYear = SUBSTRING(@vDate,CHARINDEX(',',@vDate)+1,LEN(@vDate))
RETURN CONVERT(datetime,@vMonth +'/'+@vDay+'/'+@vYear)
END
-- Omit year
IF PATINDEX('_%/_%',@vDate) = 1 and (isdate(@vdate)=1 or isdate(@vdate+'/'+datename(yyyy,getdate()))=1)
RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX('/',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX('/',@vDate)+1,len(@vDate))+'/'+datename(yyyy,getdate()))--Add the year
IF PATINDEX('_%._%',@vDate) = 1 and (isdate(@vdate)=1 or isdate(@vdate+'/'+datename(yyyy,getdate()))=1)
RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX('.',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX('.',@vDate)+1,len(@vDate))+'/'+ datename(yyyy,getdate()))--Add the year
IF PATINDEX('_%,_%',@vDate) = 1 and (isdate(replace(@vdate,',','/'))=1 or isdate(replace(@vdate,',','/')+'/'+datename(yyyy,getdate()))=1)
RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX(',',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX(',',@vDate)+1,len(@vDate))+'/'+ datename(yyyy,getdate()))--Add the year
-- Default
ELSE
RETURN NULL
RETURN @dDate
END
GO
I would then have this in QlikView:
SQL EXEC CLARITY.dbo.SP_GO_AmSurv_SUSCEPTIBILITY 'MB-1', 'ME-1';
This would then run the procedure and bring in the data for the whole of the last calendar month.
Could you do similar and have your @Start parameter as the earliest time that you want to start from perhaps YB-5 for the year start five years ago and have the @End as T-1 for yesterday?
Gethyn.
