Solved

MS-SQL query question

Posted on 2012-12-21
5
241 Views
Last Modified: 2013-01-25
Hi I am doing a query using MSSQL Mgt Studio in design view ( because I no nothing). On MS SQL server 2005

SELECT     patient_name.*, patient_address.*, doctor.*
FROM         patient_name CROSS JOIN
                      patient_address CROSS JOIN
                      doctor

I need to limit the query to the past 12 months (so the server doesn't gag) and produce an output I can eventually import into Excel
0
Comment
Question by:Openallnight
5 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 38713804
SELECT     patient_name.*, patient_address.*, doctor.*
FROM         patient_name CROSS JOIN
                      patient_address CROSS JOIN
                      doctor
where <myDatecolumn> > dateadd("m",-12,getdate())
0
 

Author Comment

by:Openallnight
ID: 38714162
I tried to grab billing dates from a billing submission table called reconsideration_table and it bombs

Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '<'.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38714186
The server will gag from those CROSS JOINs, because they are inappropriate here.

Something like below will be close, although it's probably still not quite right, since there should be a "doctor_patient" table and/or a "patient_visit" table that would be more accurate to use for this.


SELECT     patient_name.*, patient_address.*, doctor.*
FROM         dbo.patient_name pn
INNER JOIN dbo.patient_address pa ON
    pa.patient_id = pn.patient_id
INNER JOIN dbo.doctor d ON
    d.doctor_id = pn.doctor_id
0
 
LVL 3

Accepted Solution

by:
expert_dharam earned 500 total points
ID: 38716034
SELECT     patient_name.*, patient_address.*, doctor.*
FROM         dbo.patient_name pn
INNER JOIN dbo.patient_address pa ON
    pa.patient_id = pn.patient_id
INNER JOIN dbo.doctor d ON
    d.doctor_id = pn.doctor_id
Where YourTableDateColumn > dateadd("m",-12,getdate())

--Note: YourTableDateColumn = Column where your date values reside in the table
--Ensure YourTableDateColumn is of Datetime data type..
0
 

Author Comment

by:Openallnight
ID: 38732966
Too many errors, I don't know enough about SQL to interpret directions
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now