need help with show list of null result

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 2000 Forums

Transact-SQL (2000)

need help with show list of null result

Author

Topic

sun919
Starting Member

22 Posts

Posted - 2007-01-29 : 03:03:49

hi ,
I have written an sql statement but i want to display all value even thou the whole is null but at the moment my query list display nothing if non of this meet criteria
Here is my query

SELECT ISNULL(COUNT(Appointment.HNID), 0) AS Count, Treatment.TreamentType, Doctor.DoctorName, Treatment.Price, Treatment.TreatmentID AS Expr1
FROM Appointment INNER JOIN
Doctor ON Appointment.DoctorID = Doctor.DoctorID FULL OUTER JOIN
Treatment ON Appointment.TreatmentID = Treatment.TreatmentID

WHERE (Treatment.TreamentType LIKE 'LSIC %') AND
(Appointment.AppointmentDate BETWEEN @from AND @To)

GROUP BY Treatment.TreamentType, Doctor.DoctorName, Treatment.Price, Treatment.TreatmentID

ORDER BY Expr1

Basicaly I want to display the wholelist of treatment that begin with 'LSIC' and within appointment date period and return list of treatment these 'LSIC' even it is null

many thanks
sun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:31:57

[code]
WHERE Treatment.TreamentType LIKE 'LSIC %'
AND (
Appointment.AppointmentDate BETWEEN @from AND @To
OR Appointment.AppointmentDate IS NULL
)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 03:44:54

As we have pointed out before to you, some sample data to illustrate your problem is nice to have, and also your expected output based on the sample data you provide is a must.
Otherwise, all we can suggest are well educated guesses!!

SELECT		SUM(1) AS Count,
		Treatment.TreamentType,
		Doctor.DoctorName,
		Treatment.Price,
		Treatment.TreatmentID
FROM		Appointment
INNER JOIN	Doctor ON Appointment.DoctorID = Doctor.DoctorID
FULL JOIN	Treatment ON Appointment.TreatmentID = Treatment.TreatmentID
WHERE		Treatment.TreamentType LIKE 'LSIC %' AND 
		Appointment.AppointmentDate BETWEEN @from AND @To
GROUP BY	Treatment.TreamentType,
		Doctor.DoctorName,
		Treatment.Price,
		Treatment.TreatmentID
ORDER BY	Treatment.TreatmentID

Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2007-01-29 : 04:16:17

I am sorry for not providing sample data to illustrate my problem and expect output .. I ll remember it next time
Here is my data :
AppoiontmentID HNID DoctorID TreamentiD AppointmentDate
3 5 1 3 04/05/2006
4 5 2 4 06/06/2006
5 4 1 5 02/04/2006 (dd-mm-yyyyy)

TreamentID TreatmentType Price
1 Counseling 200
2 Group Discuss 230
3 IQ test 220
4 LSIC Listening 170
5 LSIC Speaking 170
6 LSIC Writing 170

query output that I wish for is ( AppointmentDate between 10/02/2006 AND 12/03/2006 )
Count TreatmenType DoctorName Price Treatment Expr
0 LSIC Listening 170 4
0 LSIC Speakiing 170 5
0 LSIC Writing 170 6

If Appointment Date is between 01/04/2006 AND 05/05/2006

Count TreatmenType DoctorName Price Treatment Expr
1 LSIC Listening John 170 4
0 LSIC Speakiing 170 5
1 LSIC Writing John 170 6

many thanks
sun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 04:33:54

[code]
declare @app table
(
AppointmentID int,
HNID int,
DoctorID int,
TreatmentID int,
AppointmentDate datetime
)

declare @treatment table
(
TreatmentID int,
TreatmentType varchar(50),
Price int
)

insert into @app
select 3, 5, 1, 3, '20060504' union all
select 4, 5, 2, 4, '20060606' union all
select 5, 4, 1, 5, '20060402'

insert into @treatment
select 1, 'Counseling', 200 union all
select 2, 'Group Discuss', 230 union all
select 3, 'IQ test', 220 union all
select 4, 'LSIC Listening', 170 union all
select 5, 'LSIC Speaking', 170 union all
select 6, 'LSIC Writing', 170

select Cnt = count(a.TreatmentID),
t.TreatmentType, t.Price, t.TreatmentID
from @treatment t
left join @app a
on t.TreatmentID = a.TreatmentID
and a.AppointmentDate >= '20060210'
and a.AppointmentDate <= '20060312'
where t.TreatmentType like 'LSIC%'
group by t.TreatmentType, t.Price, t.TreatmentID

select Cnt = count(a.TreatmentID),
t.TreatmentType, a.DoctorID, t.Price, t.TreatmentID
from @treatment t
left join @app a
on t.TreatmentID = a.TreatmentID
and a.AppointmentDate >= '20060401'
and a.AppointmentDate <= '20060505'
where t.TreatmentType like 'LSIC%'
group by t.TreatmentType, a.DoctorID, t.Price, t.TreatmentID
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 04:49:55

[code]-- prepare sample data
set dateformat dmy

DECLARE @Appointment TABLE (AppointmentID INT, HNID INT, DoctorID INT, TreatmentID INT, AppointmentDate DATETIME)

INSERT @Appointment
SELECT 3, 5, 1, 3, '04/05/2006' union all
SELECT 4, 5, 2, 4, '06/06/2006' union all
SELECT 5, 4, 1, 5, '02/04/2006'

DECLARE @Treatment TABLE (TreatmentID INT, TreatmentType VARCHAR(30), Price MONEY)

INSERT @Treatment
SELECT 1, 'Counseling', 200 union all
SELECT 2, 'Group Discuss', 230 union all
SELECT 3, 'IQ test', 220 union all
SELECT 4, 'LSIC Listening', 170 union all
SELECT 5, 'LSIC Speaking', 170 union all
SELECT 6, 'LSIC Writing', 170

DECLARE @Doctor TABLE (DoctorID INT, DoctorName VARCHAR(30))

INSERT @Doctor
SELECT 1, 'John' union all
SELECT 2, 'Noname'

-- show the data
SELECT SUM(CASE WHEN a.AppointmentDate BETWEEN '01/04/2006' AND '05/05/2006' THEN 1 ELSE 0 END) AS Count,
t.TreatmentType,
d.DoctorName,
t.Price,
t.TreatmentID
FROM @Treatment AS t
LEFT JOIN @Appointment AS a ON a.TreatmentID = t.TreatmentID
LEFT JOIN @Doctor AS d ON d.DoctorID = a.DoctorID
WHERE t.TreatmentID IN (4, 5, 6)
GROUP BY t.TreatmentType,
d.DoctorName,
t.Price,
t.TreatmentID[/code]

Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2007-01-29 : 05:27:16

Thanks you both khtan and peter I got the solution working now by combining both a little bit

This is my final one

SELECT COUNT(a.TreatmentID) AS count, t.TreamentType, t.Price, t.TreatmentID, Doctor.DoctorName
FROM Treatment AS t LEFT OUTER JOIN
Appointment AS a ON t.TreatmentID = a.TreatmentID AND a.AppointmentDate BETWEEN @from AND @to LEFT OUTER JOIN
Doctor ON Doctor.DoctorID = a.DoctorID
WHERE (t.TreamentType LIKE 'LSIC%')
GROUP BY t.TreamentType, t.Price, t.TreatmentID, Doctor.DoctorName

One thing i want to ask,it is just curiousity (want to understand more about these query)
peter when I have chance the appointmentdate to-----> SUM(CASE WHEN a.appointmentDAte BETWEEN @From AND @TO Then 1 else 0 END) As count it give me parameter incorrect ... dont know why ... would you be kind enough to explain this to me ?
many thanks
sun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 05:29:03

count() is counting NOT NULL record value
sum() is perform a sum

for this case
SUM(CASE WHEN a.appointmentDAte BETWEEN @From AND @TO Then 1 else 0 END)
there will not be NULL at all the value will be either 1 or 0.

sun919
Starting Member

22 Posts

Posted - 2007-01-29 : 06:01:58

Thanks u ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources