Can someone help me with this. Time is of a essence. I tried to use ragged right but I am unable to create all the columns I need. My query is
Declare @StartDate as DateTime, @EndDate as DateTime
select @StartDate = ParameterStartDate, @EndDate = ParameterEndDate
from dbo.LkupExtractControl
where ExtractName = 'APORS'
SELECT CAST([APORSFacilityId] as varchar(4)) [APORS Facility ID]
,CAST([FacilityName] as varchar(40)) [Facility Name]
,CAST([FacilityAddress] as varchar(40)) [Facility Street Address]
,CAST([FacilityCity] as varchar(20)) [Facility City]
,CAST([FacilityZipCode] as varchar(5)) [Facility Zip Code]
,CAST([APORSContactPerson] as varchar(40)) [Contact Person]
,CAST(REPLACE([APORSContactPhone],'-','') as varchar(10)) [Contact's Telephone Number]
, (Select CAST (replace(convert(varchar(10), [ParameterStartDate], 110),'-','') as varchar(8))
from dbo.LkupExtractControl
where ExtractName = 'APORS') [Period covered first day]
,(Select CAST (replace(convert(varchar(10), [ParameterEndDate], 110),'-','') as varchar(8))
from dbo.LkupExtractControl
where ExtractName = 'APORS') [Last Day]
,CAST('' as varchar(5)) Filler1
,CAST('' as varchar(5)) Filler2
,CAST('' as varchar(5)) Filler3
,CAST('' as varchar(5)) Filler4
,CAST('' as varchar(5)) Filler5
,CAST('' as varchar(5)) Filler6
,CAST('' as varchar(5)) Filler7
from [LkupAPORSFacility]
Where [Site] = @Site
UNION ALL
SELECT Distinct CAST(replace(Convert(varchar(10),p.DateOfBirthDate,110),'-','') as varchar(8)) as [Patient Birth Date]
,CAST(p.GenderName as varchar(1)) [Patient Sex]
,CAST(p.AddressText as varchar(30)) [Patient Street Address]
,CAST(p.CityName as varchar(20)) [Patient City]
,CAST(p.StateName as varchar(2)) [Patient State]
,CAST(p.ZipCode as varchar(5)) [Patient Zip Code]
,CAST(replace(Convert(varchar(10),e.AdmitDate,110),'-','') as varchar(8)) [Admission date]
,CAST(replace(Convert(varchar(10),e.DischargeDate,110),'-','') as varchar(8)) [Discharge Date]
,CAST(case when DateDiff(month,p.DateofBirthDate,e.DischargeDate) < 1 then 1 else 2 end as varchar(1)) as [Type of Admission]
,CAST(ltrim(d.DiagnosisCode) as varchar(7)) [Diagnosis Code]
,CAST(ltrim(p.LastName) as varchar(20)) [Patient last name]
,CAST(ltrim(p.FirstName) as varchar(20)) [Patient first name]
,CAST(ltrim(e.EncounterNumber) as varchar(20)) [Patient ID number]
,CAST(ltrim(p.MedicalRecordNumber) as varchar(20)) [Patient medical record number]
,CAST(ltrim(isnull(dp.LastName,' ')) as varchar(20)) as MotherLastName
,CAST(ltrim(isnull(dp.FirstName,' ')) as varchar(20)) as MotherFirstName
from DwEncounter e
inner join DwPatient p
on e.patientkey = p.patientkey
inner join LkupAPORSFacility laf
ON (e.FacilityKey = laf.FacilityKey)
left outer join DwPatient dp
on (dp.medicalrecordnumber = right(e.MothersMedicalRecordNumber,9) and dp.facilitykey = e.facilitykey)
left outer join DwEncounterDiagnosises d
on e.encounterkey = d.encounterkey
Where DischargeDate >= @StartDate and Dischargedate <= @EndDate
and d.DiagnosisCode like 'Q%'
and d.DiagnosisTypeKey <> 171 --exclude admitdiagnosis
and (Case When (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,1,convert(varchar(10),p.DateOfBirthDate,120))) -- >= 1 year
then Case when (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,(datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) )), convert(varchar(10),p.DateOfBirthDate,120)))
then (datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) ))
else ((datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) ))-1)
end
else 0
end) < 2
and (p.StateName = 'IL')
and laf.Site = @Site
UNION ALL
select CAST([APORSFacilityId] as varchar(12)) [APORS Facility ID]
,CAST(count(1) as varchar(5)) [Number of Records]
,CAST('' as varchar(13)) Filler1
,CAST('' as varchar(13)) Filler2
,CAST('' as varchar(13)) Filler3
,CAST('' as varchar(13)) Filler4
,CAST('' as varchar(13)) Filler5
,CAST('' as varchar(13)) Filler6
,CAST('' as varchar(13)) Filler7
,CAST('' as varchar(13)) Filler8
,CAST('' as varchar(13)) Filler9
,CAST('' as varchar(13)) Filler10
,CAST('' as varchar(13)) Filler11
,CAST('' as varchar(12)) Filler12
,CAST('' as varchar(12)) Filler13
,CAST('' as varchar(13)) Filler14
from DwEncounter e
inner join DwPatient p
on e.patientkey = p.patientkey
inner join LkupAPORSFacility laf
ON (e.FacilityKey = laf.FacilityKey)
left outer join DwPatient dp
on (dp.medicalrecordnumber = right(e.MothersMedicalRecordNumber,9) and dp.facilitykey = e.facilitykey)
left outer join DwEncounterDiagnosises d
on e.encounterkey = d.encounterkey
Where DischargeDate >= @StartDate and Dischargedate <= @EndDate
and d.DiagnosisCode like 'Q%'
and d.DiagnosisTypeKey <> 171 --exclude admitdiagnosis
and (Case When (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,1,convert(varchar(10),p.DateOfBirthDate,120))) -- >= 1 year
then Case when (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,(datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) )), convert(varchar(10),p.DateOfBirthDate,120)))
then (datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) ))
else ((datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) ))-1)
end
else 0
end) < 2
and (p.StateName = 'IL')
and laf.Site = @Site
group by CAST([APORSFacilityId] as varchar(12))