June 13, 2011 at 8:37 pm
Hi Team,
I am very new to Databases concepts and I have an requirement in my hand which is far away to my technical knowledge. Could, some one please help with this.
The requirements is
I need an the following Columns for the customerNo that is i/p via the comma sepearated file:
CustomerNo
Date
Details
Date = The minimum date between the following:
The first occurance of a BT incident and the first appointment date where the appontment is with any one of (BT Centre,Test House, any provider where there is the word BT anywhere in the name)
Details = if the date above comes from incidents then the details column will provide the incident details. If the date comes from an appointment then the details will provide a concatenation of the provider the appointment is with and the status of the appointment (e.g. Foundation House - Attended).
Could, you some one be able to shed some light on this request.
There are Two tables this query is talking about
Incident and Appoinemt table and the connection between these two should be on the customerno.
Incident table has Customerno, Incident Date, Incident details
Appointment Table has Customerno, Appoint with, Appoitn status, App Date
Thanks.
June 13, 2011 at 11:01 pm
I am sure that what you have posted is very clear to yourself. But for those who wish to help you, with a tested solution, please post the table definitions, some sample representative data, and the required results from that sample data.
To to this please click on the first link in my signature block, to read how to post the requested information, and the T-SQL code to assist you in doing so quickly and easily.
June 13, 2011 at 11:53 pm
Hi Ron,
Thanks for guiding me to make sure my posting are readable and understandable.
I need the o/p to be CustomerNo, Date, Detail ans the specs are as follws
Date Column:
A customer can have multiple BT Incidents and we can identify using the [iSuspectedBT] column in the Incident table and we have take the minimum of the BT incident for a given customers and then compare it with the Apptdate ( A customer has an appointment relation to BT, even there is an incident or not. So, the way we can find it is with keyword search in the APPT with column with the "%BT%','%Test House%' etc).
In short the date feild should get populated with the Minimum of the ( Min(Appdate), Min(IncidentDate)) meeting the above criteria.
Details Column:
If the minimum date for that BT is from the Incident table, then the detail section should get populated with the Incident Details, if not it should get populated with the Appointment details.
So, at the end of the day we need to have only one record for each patient ( irrespective, whether he has the multiple appointment or multiple incidents records), whichever occurs first.
Appointment Table
CREATE TABLE [dbo].[Appointment]
(
[CustomerID] [nvarchar](10) PK,
[iApptID] [int] NOT NULL,
[dtApptDate] [datetime] NULL,
[nvchApptWith] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nvchApptDetails] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)
Incident Table
CREATE TABLE [dbo].[Incidents]
(
[iIncidentID] [int] NOT NULL,
[CustomerID] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[iIncidentNo] [int] NULL,
[dtIncidentDate] [datetime] NULL,
[nvchIncidentDetails] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[iSuspectedBT] [int] NULL -- This is a flag 1 represents the BT incident
)
Appointment Table sample date
CustomerNoApptDate App Detail Appt With
XYZ 09-Jun-10 Test1 Delhi Medical centre
ABC14-May-08 Test2 BT medical centre
DFG04-Dec-07 Test3 Term Test House
Incident Table Sample Data
CustomerNoIncident DateIncident DetialSuspectBT
XYZ 12-Jan-08 Detail1 1
DGF 01-Jan-05Detail2 1
ABC 12-May-08Detail3 1
sdfg 14-May-08Detail4 0
Say for instance, if we are looking for the customer ABC, he has entry in both Incident and Appointment table meeting the criteria, as the SUspectBT =1 in incident tale and the Apptwith in the appointment table has a keyword "BT%'
So, the output would be
CustomerNo Date Detail
ABC 12-May-08 Detail3.
Thanks,
June 14, 2011 at 9:27 am
kish1234
I have extracted a portion of your posting and present it below in a readily consumable format. However the table definition and supplied data appears to be somewhat puzzling, as I have noted.
CREATE TABLE [dbo].[Appointment]
([CustomerID] [nvarchar](10) PK,
[iApptID] [int] NOT NULL,
[dtApptDate] [datetime] NULL,
[nvchApptWith] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nvchApptDetails] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,)
--Appointment Table sample date
/*Assuming "date" is an incorrect spelling and should be "data"
CustomerNo ApptDate App Detail Appt With
| | | | |--is this [nvchApptWith]?
| | | |--is this [nvchApptDetails]?
| | |--is this [dtApptDate]?
| |--missing value for [iApptID] defined as NOT NULL
|--this could be assumed to be values for [CustomerID]
XYZ 09-Jun-10 Test1 Delhi Medical centre
ABC 14-May-08 Test2 BT medical centre
DFG 04-Dec-07 Test3 Term Test House
Then again I could be miss reading your input, and if that is the case, I apologize.
June 15, 2011 at 11:08 pm
Hi Ron,
Thanks for your time. I found the solution.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply