Hello,
I have two tables are thus and i want to query then to give me the results of date,identifier,fullname, age and total fee, from the two into one
how do i achieve this ?
thanks
MM
CREATE TABLE [dbo].[clt_referral](
[refID] [int] IDENTITY(1000000,1) NOT NULL,
[refDate] [datetime] NOT NULL CONSTRAINT [DF_ctl_referral_refDate] DEFAULT (getdate()),
[notes] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[GP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PharmacyID] [int] NULL,
[exclusionIDs] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PatientID] [int] NOT NULL CONSTRAINT [DF_clt_referral_PatientID] DEFAULT (1),
[Age] [int] NOT NULL CONSTRAINT [DF_clt_referral_Age] DEFAULT (0),
[consultation_date_time] [datetime] NOT NULL CONSTRAINT [DF_clt_referral_consultation_date_time] DEFAULT (getdate()),
[faxed] [bit] NOT NULL CONSTRAINT [DF_clt_referral_faxed] DEFAULT (0),
[service_fee] [numeric](18, 2) NOT NULL CONSTRAINT [DF_clt_referral_service_fee] DEFAULT (0),
[vat] [numeric](18, 2) NOT NULL CONSTRAINT [DF_clt_referral_vat] DEFAULT (0),
[fullname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[identifier] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
and
CREATE TABLE [dbo].[clt_record](
[id] [int] IDENTITY(1,1) NOT NULL,
[fullname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[identifier] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[patientID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[id_old] [int] NULL CONSTRAINT [DF__clt_recor__id_ol__014935CB] DEFAULT (0),
[age] [int] NULL,
[post_code] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ethnicity_id] [int] NULL,
[consultation_date_time] [datetime] NOT NULL,
[assessment_1] [bit] NOT NULL,
[assessment_2] [bit] NOT NULL,
[assessment_3] [bit] NOT NULL,
[assessment_4] [bit] NOT NULL,
[assessment_5] [bit] NULL,
[current_medication] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[current_illness] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[clt_contra_indication] [bit] NOT NULL,
[counselling_1] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_1] DEFAULT (0),
[counselling_2] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_2] DEFAULT (0),
[counselling_3] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_3] DEFAULT (0),
[counselling_4] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_4] DEFAULT (0),
[counselling_5] [bit] NULL CONSTRAINT [DF_clt_record_counselling_5] DEFAULT (0),
[counselling_6] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_6] DEFAULT (0),
[counselling_7] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_7] DEFAULT (0),
[counselling_8] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_8] DEFAULT (0),
[counselling_9] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_9] DEFAULT (0),
[counselling_10] [bit] NULL,
[supply_given] [bit] NOT NULL,
[batch_number] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[batch_expiry] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[exclusion_ids] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[referral_id] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[condoms_provided] [bit] NOT NULL,
[Chlamydia_supplied] [bit] NOT NULL,
[consultation_time] [int] NOT NULL,
[service_fee] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_service_fee] DEFAULT (0),
[products_fee] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_products_fee] DEFAULT (0),
[products_fee_vat] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_products_fee1] DEFAULT (0),
[total_fee] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_total_fee] DEFAULT (0),
[total_fee_vat] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_total_fee1] DEFAULT (0),
[pharmacy_id] [int] NOT NULL,
[user_created] [int] NOT NULL,
[date_created] [datetime] NOT NULL CONSTRAINT [DF_clt_record_date_created] DEFAULT (getdate()),
[state] [int] NOT NULL CONSTRAINT [DF_clt_record_state] DEFAULT (0),
[inclusion_ids] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[gender] [bit] NULL,
[patient_id] [int] NULL,
[Referral] [bit] NULL,