Dates, tblDates tla_Dates and tla

A utility table tblDates has been created for your enjoyment, with required views

While experienced crystal users can do all of this with functions, this tables and its views create some simple but still thought requiring elements for reporting

The table has a single column of actual data, the date of every day between >=1 july 1990 and < 30 jun 2040 as well as 1st Jan 2100 (for undischarged patients) and a series of computed columns

If joining a date to this table note that a datetime column in a table (and all talons SQL tables use DateTime data types) includes time and the time in tblDates is 00:00:00.000, so you need to use crystal to convert both your datetime column to "just date" and join it to the [DateOnly] column in tblDates

Note that the discharge date time for Undischarged patients is 1 January 2100 23:59:59

Here is how you can create your own..

CREATE PROCEDURE [dbo].[sp_Create_Dates]

-- ********************* ver 0.9.903.9081 ********************

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblDates]

CREATE TABLE [dbo].[tblDates]([Date] [datetime] NOT NULL

,CONSTRAINT [PK_tblDates] PRIMARY KEY CLUSTERED ([Date] ASC) ON [PRIMARY])

CREATE INDEX IX_tblDates_Date ON dbo.tblDates ([Date] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE [dbo].[tblDates] ADD [FiscalPeriod] AS CONVERT(VARCHAR(9), REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,[Date])),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,[Date])),2,0),' ','0'))

,YR AS CONVERT(INT,DATEPART(YEAR,[Date]))

,MTH AS CONVERT(INT,DATEPART(MONTH,[Date]))

,[DAY] AS CONVERT(INT,DATEPART(DAY,[Date]))

,[DOW] AS CONVERT(INT,DATEPART(DW,[Date]))

,[Month] AS CONVERT(VARCHAR(12),DATENAME(MONTH,[Date]))

,[DayName] AS CONVERT(VARCHAR(12),DATENAME(DW,[Date]))

,[FiscalYear] AS CONVERT(varchar(9), STR(DATEPART(YEAR,DATEADD(MONTH,-6,[Date])),4,0) + '/' + STR(DATEPART(YEAR,DATEADD(MONTH,6,[Date])),4,0))

,[DateOnly] AS CONVERT(Date, [Date])

,[DaysInMonth] AS DATEDIFF(DAY, CONVERT(DATETIME,'1 ' + DATENAME(MONTH,[Date]) +' ' +STR(DATEPART(YEAR,[Date]),4,0)), DATEADD(MONTH,1,CONVERT(DATETIME,'1 ' + DATENAME(MONTH,[Date]) +' ' +STR(DATEPART(YEAR,[Date]),4,0))))

,Calendar_BOM AS CONVERT(DATETIME,'1 ' + DATENAME(MONTH,[Date]) +' ' +STR(DATEPART(YEAR,[Date]),4,0))

,Calendar_EOM AS DATEADD(SECOND,-1,DATEADD(MONTH,1,CONVERT(DATETIME,'1 ' + DATENAME(MONTH,[Date]) +' ' +STR(DATEPART(YEAR,[Date]),4,0))))

DECLARE @MYDATE datetime

SET @MYDATE = CONVERT(DATETIME,'1 JUL 1990')

WHILE @MYDATE < CONVERT(DATETIME,'1 JUL 2040')

BEGIN

EXEC ('INSERT INTO [dbo].[tblDates] (Date) VALUES(''' + @MYDATE + ''')')

SET @MYDATE = DATEADD(DAY,1,@MYDATE)

END

EXEC ('INSERT INTO [dbo].[tblDates] (Date) VALUES(''1 JAN 2100'')')

How is tblDates Used

These 4 views desmonstate how the site specific views are created and how they are used

CREATE VIEW [dbo].[<tla>_Dates]

SELECT * FROM tblDates

In this view we get a table of Months (note [Month] in brackets, it is an "SQL reserved word" and needs to be in square brackets or it shows as an error (but will still work), AS WELL column names with spaces in them must be used inside [..]

CREATE VIEW [dbo].[<tla>_Months]

SELECT Fiscalyear

, FiscalPeriod

, YR

, MTH

, [Month]

, DaysInMonth

FROM tblDates

GROUP BY Fiscalyear

, FiscalPeriod

, YR

, MTH

, [Month]

, DaysInMonth

This view creates a table of all Feeders in use at the site from tblSiteModules and CROSS JOINS with tblDates values where the day of the Month is 1, i.e. the 1st of the month as we only want one value, we could have used <tla>_Months!! perhaps.. This creates a full template for us to Add Utilisation Record Counts to.. in the next view

CREATE VIEW [dbo].[<tla>_UTIL_Feeders_Periods]

select distinct CC.Feeder_System_Code as Feeder_Code

, CC.Module_Code

, BB.MTH

, BB.YR

, BB.FiscalPeriod

, CC.Company_TLA

, CC.Campus_TLA

, BB.Calendar_BOM

, BB.Calendar_EOM

, BB.DaysInMonth

, BB.[Month]

, BB.DateOnly

from tblSiteModules CC cross join tblDates bb

where BB.DAY = 1 AND CC.Company_TLA = 'tla'

Here we join the utilisation we actually find in the tla_UTIL_record_counts_simple and the template we have created, this then shows where utilisation is MISSING as record counts will be zero!!!

CREATE ALTER VIEW [dbo].[iph_UTIL_record_counts_complex]

SELECT DISTINCT

ISNULL(feeder_periods.Company_TLA,util.Company_TLA) as Company_TLA

,ISNULL(feeder_periods.Campus_TLA,util.Campus_TLA) as Campus_TLA

,ISNULL(feeder_periods.Feeder_Code,util.Feeder_Code) as Feeder_Code

,COALESCE(util.SYSTEM_TYPE,feeder_periods.Module_Code) AS SYSTEM_TYPE

,ISNULL(feeder_periods.YR,util.YR) as YR

,ISNULL(feeder_periods.MTH,util.MTH) as MTH

,ISNULL(feeder_periods.[Month],DATENAME(MONTH,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01'))) AS [Month]

,ISNULL(feeder_periods.FiscalPeriod,CONVERT(VARCHAR(9), REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01'))),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01'))),2,0),' ','0'))) AS FiscalPeriod

,ISNULL(feeder_periods.Calendar_BOM,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01')) AS Calendar_BOM

,ISNULL(feeder_periods.Calendar_EOM,DATEADD(SECOND,-1,DATEADD(MONTH,1,CONVERT(DATETIME,'1 ' + DATENAME(MONTH,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01')) +' ' +STR(DATEPART(YEAR,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01')),4,0))))) AS Calendar_EOM

,ISNULL(feeder_periods.DaysInMonth,DATEDIFF(DAY, CONVERT(DATETIME,'1 ' + DATENAME(MONTH,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01')) +' ' +STR(DATEPART(YEAR,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01')),4,0)), DATEADD(MONTH,1,CONVERT(DATETIME,'1 ' + DATENAME(MONTH,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01')) +' ' +STR(DATEPART(YEAR,CONVERT(DATETIME,STR(UTIL.YR,4) + '-' +REPLACE(STR(util.MTH,2,0),' ','0') + '-' + '01')),4,0))))) AS DaysInMonth

,ISNULL(util.[Records Counted],0) AS [Records Counted]

FROM iph_UTIL_Feeders_Periods feeder_periods full outer join iph_UTIL_record_counts_simple util ON

feeder_periods.Company_TLA = util.COMPANY_TLA AND

feeder_periods.Campus_TLA = util.CAMPUS_TLA AND

feeder_periods.FiscalPeriod = util.FiscalPeriod AND

feeder_periods.Feeder_Code = util.FEEDER_CODE

And this is the source of the data

CREATE VIEW [dbo].[iph_UTIL_record_counts_simple]

SELECT COMPANY_TLA, CAMPUS_TLA, '001 I Episodes..' as SYSTEM_TYPE, AA.Creating_Feeder AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix, DATEPART(YEAR,AA.Disch_date) YR ,DATEPART(MONTH, AA.Disch_date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Disch_date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Disch_date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblEpisodeMaster AA

where AA.Episode_Type = 'I' AND COMPANY_TLA = 'IPH'

GROUP BY COMPANY_TLA, CAMPUS_TLA, AA.Creating_Feeder,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END,DATEPART(YEAR,AA.Disch_date) ,DATEPART(MONTH, AA.Disch_date),DATEPART(YEAR,DATEADD(MONTH,6,AA.Disch_date)),DATEPART(MONTH,DATEADD(MONTH,6,AA.Disch_date))

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '002 E Episodes' as SYSTEM_TYPE, AA.Creating_Feeder AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Disch_date) YR ,DATEPART(MONTH, AA.Disch_date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Disch_date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Disch_date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblEpisodeMaster AA

where AA.Episode_Type = 'E' AND COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '003 O Episodes' as SYSTEM_TYPE, AA.Creating_Feeder AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Disch_date) YR ,DATEPART(MONTH, AA.Disch_date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Disch_date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Disch_date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblEpisodeMaster AA

where AA.Episode_Type = 'O' AND COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '01 Pathology System' as SYSTEM_TYPE, AA.Creating_Feeder AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblAuslabData AA

WHERE COMPANY_TLA = 'IPH'

GROUP BY COMPANY_TLA, CAMPUS_TLA, AA.Creating_Feeder,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END,DATEPART(YEAR,AA.Service_Date) ,DATEPART(MONTH, AA.Service_Date),DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date))

UNION

SELECt COMPANY_TLA, CAMPUS_TLA, '02 Outpatient System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM TBLOPUTIL AA

WHERE COMPANY_TLA = 'IPH'

GROUP BY COMPANY_TLA, CAMPUS_TLA, AA.Feeder_Code,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END,DATEPART(YEAR,AA.Service_Date) ,DATEPART(MONTH, AA.Service_Date),DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date))

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '03 Dependency System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblDPUtil AA

WHERE COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '11 Other System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblOtherUtil AA

WHERE COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '12 AH System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblAHIISUtil AA

WHERE COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '04 Theatre System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblTHUtil AA

WHERE COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '05 Emergency System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblEMUtil AA

WHERE COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '06 Pharmacy System' as SYSTEM_TYPE, AA.Creating_Feeder AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblStoccaData AA

WHERE COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '07 ADM System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblADMUtil AA

WHERE Product_Code LIKE '%DISCH%' AND COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '08 Radiology System' as SYSTEM_TYPE, AA.Feeder_Code AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Service_Date) YR ,DATEPART(MONTH, AA.Service_Date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Service_Date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Service_Date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblRADUtil AA

WHERE COMPANY_TLA = 'IPH'

UNION

SELECT COMPANY_TLA, CAMPUS_TLA, '09 Deliveries System' as SYSTEM_TYPE, CASE WHEN AA.Campus_TLA = AA.Company_TLA THEN 'DEL' ELSE AA.Campus_TLA + 'DL' END AS Feeder_Code,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix,DATEPART(YEAR,AA.Birth_date) YR ,DATEPART(MONTH, AA.Birth_date) AS MTH,REPLACE(STR(DATEPART(YEAR,DATEADD(MONTH,6,AA.Birth_date)),4,0) + STR(DATEPART(MONTH,DATEADD(MONTH,6,AA.Birth_date)),2,0),' ','0') AS FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblADMDeliveries AA

WHERE COMPANY_TLA = 'IPH'

GROUP BY COMPANY_TLA, CAMPUS_TLA, CASE WHEN AA.Campus_TLA = AA.Company_TLA THEN 'DEL' ELSE AA.Campus_TLA + 'DL' END,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END,DATEPART(YEAR,AA.Birth_date) ,DATEPART(MONTH, AA.Birth_date),DATEPART(YEAR,DATEADD(MONTH,6,AA.Birth_date)),DATEPART(MONTH,DATEADD(MONTH,6,AA.Birth_date))

UNION

SELECT AA.Company_TLA, AA.Campus_TLA, '000 Patient System (PMI)' as SYSTEM_TYPE, 'PAT DETAIL' AS FEEDER_CODE,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END AS Prefix, NULL as yr, NULL as mth, NULL as FiscalPeriod, COUNT(*) as [Records Counted]

FROM tblPatientDetails AA

WHERE COMPANY_TLA = 'IPH'

GROUP BY COMPANY_TLA, CAMPUS_TLA,CASE WHEN CAMPUS_TLA = 'IPH' THEN '' ELSE LEFT(UR,1) END