March 5, 2015 at 7:29 am
Hi I have a query:
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion
FROM dbo.tblGP_Practices AS gp INNER JOIN
dbo.tblGP_PatientLink AS pl ON gp.GPPracticeID = pl.GPPracticeID INNER JOIN
dbo.tblPatient AS p ON pl.PatientID = p.PatientID INNER JOIN
dbo.tblAppointments AS a ON p.PatientID = a.PatientID INNER JOIN
dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID
WHERE (a.AttendanceStatus IN (1, 2, 3, 4, 6, 7, 9))
GROUP BY gp.CCGRegion, YEAR(dbo.tblCourses.CourseDate), CONVERT(char(3), dbo.tblCourses.CourseDate, 0)
I wanted to add anohter column where it counts the results of the followign query:
SELECT dbo.tblPatient.NHSnumber, dbo.tblPatient.DateOfBirth, dbo.tblPatient.DateReferralReceived, dbo.tblGP_Practices.OrganisationCode
FROM dbo.tblPatient INNER JOIN
dbo.tblGP_PatientLink ON dbo.tblPatient.PatientID = dbo.tblGP_PatientLink.PatientID INNER JOIN
dbo.tblGP_Practices ON dbo.tblGP_PatientLink.GPPracticeID = dbo.tblGP_Practices.GPPracticeID LEFT OUTER JOIN
dbo.tblAppointments ON dbo.tblPatient.PatientID = dbo.tblAppointments.PatientID
WHERE (dbo.tblAppointments.PatientID IS NULL)
is this possible?
March 5, 2015 at 7:43 am
Short answer, yes its possible.
But to actually do it we'd probably need a detailed example. Ideally DDL for the affected tables, some sample data and expected output.
Your outer query is grouped by CCGregion and year, so is the count column you want to add correlated to that (counting the patients within the region with appointments for that year?) , or would the value of the "inner query" column be the same for every row? As in every row gets the overall count?
March 5, 2015 at 8:09 am
the first query returns:
MonthYearCity CCG AttendedCity CCG DNACancelled
Oct2014010
Feb2015000
Jan2015100
Mar2015210
May2015010
The second query returns
NHSNumber DateOfBirth DateReferralReceived OrganisationCode
121421421 1981-03-14 2015-03-14 C43653
I want the query to count the number return in query 2 and return this as a column in query 1. Is there any more infomation I can provide to make it easier
March 5, 2015 at 3:34 pm
I am still having trouble understanding the table structure. Please send us CREATE TABLE statements for the tables you want.
March 10, 2015 at 4:25 am
tblPatient:
CREATE TABLE [dbo].[tblPatient](
[PatientID] [int] IDENTITY(1,1) NOT NULL,
[CentreName] [nvarchar](max) NULL,
[StudyID] [nchar](7) NULL,
[RandomNumber] [int] NULL,
[Title] [int] NULL,
[Surname] [nvarchar](max) NULL,
[Forename] [nvarchar](max) NULL,
[Gender] [nchar](10) NULL,
[DateOfBirth] [datetime] NULL,
[Note] [nvarchar](max) NULL,
[PAddressID] [int] NULL,
[Telephone1] [nvarchar](max) NULL,
[Telephone2] [nvarchar](max) NULL,
[Mobile] [nvarchar](max) NULL,
[nvarchar](max) NULL,
[ContactTime] [nvarchar](max) NULL,
[CreatedBy] [nvarchar](10) NULL,
[CreatedDate] [datetime] NULL,
[LastModifiedBy] [nvarchar](10) NULL,
[LastModifiedDate] [datetime] NULL,
[stopLetters] [bit] NULL,
[apptGenerated] [bit] NULL,
[DateReferralReceived] [datetime] NULL,
[ReferredBy] [nvarchar](max) NULL,
[NHSnumber] [nchar](10) NULL,
[InterpreterReqired] [nchar](10) NULL,
[Language] [nchar](10) NULL,
[YearOfDiagnosis] [int] NULL,
[HbA1c] [real] NULL,
[TotalCholesterol] [real] NULL,
[HDLOnReferral] [real] NULL,
[ldlOnReferral] [real] NULL,
[BPSystolicOnReferral] [real] NULL,
[BPDiasotlicOnReferral] [real] NULL,
[HbA1c6months] [real] NULL,
[HbA1c12months] [real] NULL,
[Ethnicity] [nvarchar](max) NULL,
[AttendedStatus] [int] NULL,
CONSTRAINT [PK_tblPatient] PRIMARY KEY CLUSTERED
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPatient] WITH CHECK ADD CONSTRAINT [FK_tblPatient_tblPatient] FOREIGN KEY([PatientID])
REFERENCES [dbo].[tblPatient] ([PatientID])
GO
ALTER TABLE [dbo].[tblPatient] CHECK CONSTRAINT [FK_tblPatient_tblPatient]
GO
tblAppointments:
CREATE TABLE [dbo].[tblAppointments](
[AppointmentID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [int] NULL,
[AppType] [int] NULL,
[AppDate] [datetime] NULL,
[Comments] [nvarchar](max) NULL,
[DateSent] [datetime] NULL,
[CourseID] [int] NULL,
[LocationID] [int] NULL,
[AttendanceStatus] [int] NULL,
CONSTRAINT [PK_tblAppointments] PRIMARY KEY CLUSTERED
(
[AppointmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblAppointments] WITH CHECK ADD CONSTRAINT [FK_tblAppointments_tblAppointments] FOREIGN KEY([PatientID])
REFERENCES [dbo].[tblPatient] ([PatientID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblAppointments] CHECK CONSTRAINT [FK_tblAppointments_tblAppointments]
GO
tblCourses:
CREATE TABLE [dbo].[tblCourses](
[Course_ID] [int] IDENTITY(1,1) NOT NULL,
[Location_ID] [int] NOT NULL,
[CourseDate] [datetime] NOT NULL,
[CourseType] [int] NOT NULL,
[Instructor1] [nvarchar](max) NULL,
[Instructor2] [nvarchar](max) NULL,
[Instructor3] [nvarchar](max) NULL,
[Observer] [nvarchar](max) NULL,
CONSTRAINT [PK_tblCourses] PRIMARY KEY CLUSTERED
(
[Course_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCourses] WITH CHECK ADD CONSTRAINT [FK_tblCourses_tblLkup_Location] FOREIGN KEY([Location_ID])
REFERENCES [dbo].[tblLkup_Location] ([LocationID])
GO
ALTER TABLE [dbo].[tblCourses] CHECK CONSTRAINT [FK_tblCourses_tblLkup_Location]
GO
tblGP_PatientLink:
CREATE TABLE [dbo].[tblGP_PatientLink](
[GPPatientID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [int] NULL,
[GPPracticeID] [int] NULL,
[GPPractitionersID] [int] NULL,
[GPBranchID] [int] NULL,
[CreatedBy] [nvarchar](10) NULL,
[CreatedDate] [datetime] NULL,
[LastModifiedBy] [nvarchar](10) NULL,
[LastModifiedDate] [datetime] NULL,
CONSTRAINT [PK_tblGP_PatientLink] PRIMARY KEY CLUSTERED
(
[GPPatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
tblGP_Practices:
CREATE TABLE [dbo].[tblGP_Practices](
[GPPracticeID] [int] IDENTITY(1,1) NOT NULL,
[CustomGPID] [nvarchar](max) NULL,
[OrganisationCode] [nchar](6) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[NationalGrouping] [nchar](3) NULL,
[HL_Authority] [nchar](3) NULL,
[Address1] [nvarchar](35) NULL,
[Address2] [nvarchar](35) NULL,
[Address3] [nvarchar](35) NULL,
[Address4] [nvarchar](35) NULL,
[Address5] [nvarchar](35) NULL,
[Postcode] [nvarchar](8) NULL,
[Open_Date] [nvarchar](8) NULL,
[Close_Date] [nvarchar](8) NULL,
[Status_Code] [nvarchar](1) NULL,
[OrgSubCode] [nchar](1) NULL,
[ParentOrgCode] [nchar](3) NULL,
[JoinParentDate] [nvarchar](8) NULL,
[LeftParentDate] [nvarchar](8) NULL,
[Telephone] [nvarchar](12) NULL,
[Notused1] [nchar](10) NULL,
[Notused2] [nchar](10) NULL,
[Notused3] [nchar](10) NULL,
[AmendedRecIndi] [nchar](1) NULL,
[Notused4] [nchar](10) NULL,
[Notused5] [nchar](10) NULL,
[Notused6] [nchar](10) NULL,
[PracticeType] [nchar](1) NULL,
[Notused7] [nchar](10) NULL,
[CreatedBy] [nvarchar](10) NULL,
[CreatedDate] [datetime] NULL,
[LastModifiedBy] [nvarchar](10) NULL,
[LastModifiedDate] [datetime] NULL,
[CCGRegion] [nvarchar](max) NULL,
CONSTRAINT [PK_GP_Practices1] PRIMARY KEY CLUSTERED
(
[GPPracticeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply