NEW TO SQL - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

NEW TO SQL

Author

Topic

H
Starting Member

23 Posts

Posted - 2012-02-06 : 18:09:28

Prescription table
- Tran_id
- start_date
- active
- created_date
- Drug
- Drug_group
- Last_date
- Last_refill_date
- Next_refill (date)
- Patient_id
- Rx#
- Tran_date

Patient table
- id
- last_name
- first_name
- patient_no

Report filter by :- Next_refill and drug_group
Next_refill can be date range
And drug_group should be ‘NC’
Order by date of refill (oldest date listed 1st)

Report field
1. Patient name
2. Patient_no
3. drug
4. Next_refill
5. Note

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 18:32:51

[code]
SELECT p.first_name + ' ' + p.last_name as Patient_name,
p.patient_no,
pr.Drug,
pr.next_refill,
...
FROM Prescription pr
INNER JOIN patient p
ON p.id = pr.Patient_id
WHERE pr.drug_group = 'NC'
AND Next_refill > = @StartDate
AND Next_refill < @ENdDate + 1
ORDER BY Last_refill_date DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

H
Starting Member

23 Posts

Posted - 2012-02-07 : 10:25:38

SELECT p.first_name, p.last_name,
p.patient_no,
pr.Drug,
pr.next_refill,
pr.note
FROM Prescription pr
INNER JOIN patient_master p
ON p.id = pr.Patient_id
WHERE pr.drug_group like 'Nar%'
AND Next_refill > = '01012012'
AND Next_refill < '02062012' +1
ORDER BY Last_refill_date DESC

I ran this query and i am getting error Syntax error near'=' on line 10 sqlcode='131
line 1 column 1

uberman
Posting Yak Master

159 Posts

Posted - 2012-02-07 : 11:52:54

extra space?
AND Next_refill > = '01012012'
AND Next_refill >= '01012012'

H
Starting Member

23 Posts

Posted - 2012-02-07 : 12:15:18

there is no extra space that i can see, i am sorry i did not understood AND Next_refill > = '01012012'
AND Next_refill >= '01012012' how can we give that filter

sorry but i did not understood how that will work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:34:04

quote:
Originally posted by H

there is no extra space that i can see, i am sorry i did not understood AND Next_refill > = '01012012'
AND Next_refill >= '01012012' how can we give that filter

sorry but i did not understood how that will work

the suggestion was to remove space between > and =

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

H
Starting Member

23 Posts

Posted - 2012-02-07 : 13:13:32

thank you

Really appreciate all your help

Thanks again
H

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 13:40:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

H
Starting Member

23 Posts

Posted - 2012-02-07 : 14:08:20

One more thing if I have to add provider filed in there
Under prescription table
doctor_id

Under emp_master
Emp_id
Firstname
Lastname

Thanks
H

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 14:11:16

which field corresponds to provider? i cant see it in columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

H
Starting Member

23 Posts

Posted - 2012-02-08 : 00:02:11

prescription table has doctor_id which is link to emp_master table field emp_id (we need firstname and lastname from emp_master)
i hold this helps sorry i missed

H
Starting Member

23 Posts

Posted - 2012-02-08 : 14:23:30

sorry there was typo i meant i hope this helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 14:43:03

[code]
SELECT p.first_name, p.last_name,
p.patient_no,
pr.Drug,
pr.next_refill,
pr.note,
e.firstname + ' ' + e.lastname AS providername
FROM Prescription pr
INNER JOIN patient_master p
ON p.id = pr.Patient_id
INNER JOIN emp_master em
ON em.Emp_id = pr.doctor_id
WHERE pr.drug_group like 'Nar%'
AND Next_refill > = '01012012'
AND Next_refill < '02062012' +1
ORDER BY Last_refill_date DESC

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

H
Starting Member

23 Posts

Posted - 2012-02-10 : 18:18:05

Thank you very much

Thanks
H

H
Starting Member

23 Posts

Posted - 2012-02-10 : 18:19:22

Once again thanks for your time and your timely response

Thanks
H

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 18:32:23

no problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources