Dates, tblDates tla_Dates and tla_Months
Previous Topic  Next Topic 

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]

AS

-- ********************* 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'')')

GO


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]

AS

SELECT * FROM tblDates

GO



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]

AS

SELECT Fiscalyear

     , FiscalPeriod

     , YR

     , MTH

     , [Month]

     , DaysInMonth

FROM tblDates

GROUP BY Fiscalyear

       , FiscalPeriod

       , YR

       , MTH

       , [Month]

       , DaysInMonth

GO


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]

AS

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]

AS

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]

AS

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'

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, '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'

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, '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'

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, '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'

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, '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'

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, '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'

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, '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'

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, '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'

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))

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'

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, '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'

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, '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