How to get the o/p for this query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get the o/p for this query.

Author

Topic

krishna.kanigelpula
Starting Member

5 Posts

Posted - 2011-06-13 : 22:51:42

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.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-14 : 01:20:24

krishna.kanigelpula - could you post the DDL of the tables. And any query you have tried on - even if it's throwing a error.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

krishna.kanigelpula
Starting Member

5 Posts

Posted - 2011-06-14 : 01:55:20

Hi Jack,

Thanks for your time and effort on this request

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
CustomerNo ApptDate App Detail Appt With
XYZ 09-Jun-10 Test1 Delhi Medical centre
ABC 14-May-08 Test2 BT medical centre
DFG 04-Dec-07 Test3 Term Test House

Incident Table Sample Data

CustomerNo Incident Date Incident Detial SuspectBT
XYZ 12-Jan-08 Detail1 1
DGF 01-Jan-05 Detail2 1
ABC 12-May-08 Detail3 1
sdfg 14-May-08 Detail4 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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-14 : 03:21:48

[code]
select *
from Appointment a
inner join Incident i ON a.CustomerID = i.CustomerID
where i.SuspectBT = 1
and a.Detail like 'BT%'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources