?
Solved

Combine two sql queries

Posted on 2011-09-22
5
Medium Priority
?
271 Views
Last Modified: 2012-06-27
Hi,
I have 2 queries ,any help on how to combine them will be appreciated
Specification for the query :

Where  [Status] at %Input_Date%  = “Active” or “Preserved Pensioner” or “Pensioner” or  “Widow(er)”  or “Child” ---is it the latest entry
OR where  [Status] at %Input_Date%  = “No Liability”
and  [Dateleft], [Dbdate], [Pdpencomm] or [Topaid]  later than (%Input_Date% less 1 Year)

Query1:

Select *  From Personal_Details where PERSONAL_DETAILS.CURRSTATUS = 'PRESERVED PENSIONER' and DateJS > datejc

Query2:

 select * from Personal_Details a inner join DEATH_BENEFIT b on a.REFNO=b.REFNO inner join PENSIONER_DETAIL c on c.REFNO=a.REFNO inner join TRANSFER_OUT_HISTORY d on d.REFNO=a.REFNO where (a.CURRStatus in ('Active','PreservedPensioner','Pensioner','Widow','Child','No Liability')) and (a.Dateleft > dateadd(y,17/06/2011,-1)) or  b.dbdate > dateadd(y,17/06/2011,-1)   or c.pdpencomm  > dateadd(y,17/06/2011,-1) or d.topaid > dateadd(y,17/06/2011,-1)
0
Comment
Question by:RIAS
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:lisa_mc
ID: 36579624
Try this I think its what you need


select * from Personal_Details a
inner join DEATH_BENEFIT b on
a.REFNO=b.REFNO 

inner join PENSIONER_DETAIL c on
c.REFNO=a.REFNO 

inner join TRANSFER_OUT_HISTORY d on 
d.REFNO=a.REFNO 

where (a.CURRStatus in ('Active','PreservedPensioner','Pensioner','Widow','Child')
or (a.CURRStatus = 'No Liability'
and ((a.Dateleft > dateadd(y,17/06/2011,-1)) or  (b.dbdate > dateadd(y,17/06/2011,-1))   
or (c.pdpencomm  > dateadd(y,17/06/2011,-1)) or (d.topaid > dateadd(y,17/06/2011,-1)))  )

Open in new window

0
 

Author Comment

by:RIAS
ID: 36579651
Hi,
Got error :
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.


Thanks
0
 
LVL 3

Accepted Solution

by:
lisa_mc earned 2000 total points
ID: 36579661
my apologies forgot one bracket

 select * from Personal_Details a
inner join DEATH_BENEFIT b on
a.REFNO=b.REFNO

inner join PENSIONER_DETAIL c on
c.REFNO=a.REFNO

inner join TRANSFER_OUT_HISTORY d on
d.REFNO=a.REFNO

where (a.CURRStatus in ('Active','PreservedPensioner','Pensioner','Widow','Child'))
or (a.CURRStatus = 'No Liability'
and ((a.Dateleft > dateadd(y,17/06/2011,-1)) or  (b.dbdate > dateadd(y,17/06/2011,-1))  
or (c.pdpencomm  > dateadd(y,17/06/2011,-1)) or (d.topaid > dateadd(y,17/06/2011,-1)))  )
0
 

Author Closing Comment

by:RIAS
ID: 36579689
Cheers mate
0
 
LVL 3

Expert Comment

by:lisa_mc
ID: 36579696
you are very welcome glad I could help
happy coding :-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 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