|
|
You're in the
sql server reporting services
group:SQL query help
sql server reporting services:
I have a query with a number of inner joins and left outer joins. The where clause has numerous "and" clauses and one of these I need to be where a endate is greater than the current date less 6 months (ie anything in the last six months). where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid between 1 and 9) and (rpn.enddate > '01/01/2005') or (rpn.enddate is null) at present as you can see I have stipulated the date as 01/01/2005 however i need the report to run every week (schedule will be set up for running and emailing) and I need the report to have the correct "and" in place. Any help would be greatly appreciated. the rpn.enedate can either have a date or be null.
and (rpn.enddate > datediff(mm,-6,rpn.enddate))
That will subtract six months from rpn.enddate. Here is the msdn ref link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp Good luck! Peace, Dan [quoted text, click to view] "Nat Johnson" <NatJohnson@discussions.microsoft.com> wrote in message news:FC3E4789-C18E-491F-B845-A71E5696EA19@microsoft.com... > Have asked previous but not helped...so thought I would give it another > go. > I have a query with a number of inner joins and left outer joins. The > where > clause has numerous "and" clauses and one of these I need to be where a > endate is greater than the current date less 6 months (ie anything in the > last six months). > > where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid > between > 1 and 9) > and (rpn.enddate > '01/01/2005') or (rpn.enddate is null) > > at present as you can see I have stipulated the date as 01/01/2005 however > i > need the report to run every week (schedule will be set up for running and > emailing) and I need the report to have the correct "and" in place. Any > help > would be greatly appreciated. the rpn.enedate can either have a date or > be > null. > > hopefully this is a bit more information.
Thanks for the help. Not quite right tho. I need to return all enddates
that are within the last six months or that are null. RUnning this returns all enddates - 6 months. I dont want to change the value of the enddate.....just return enddates that have occurred during the last 6 months. ie. getdate(todays date) - 6 months [quoted text, click to view] "Dan Christjohn" wrote:
> and (rpn.enddate > datediff(mm,-6,rpn.enddate)) > > That will subtract six months from rpn.enddate. Here is the msdn ref link: > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp > > Good luck! > > Peace, > > Dan > > > "Nat Johnson" <NatJohnson@discussions.microsoft.com> wrote in message > news:FC3E4789-C18E-491F-B845-A71E5696EA19@microsoft.com... > > Have asked previous but not helped...so thought I would give it another > > go. > > I have a query with a number of inner joins and left outer joins. The > > where > > clause has numerous "and" clauses and one of these I need to be where a > > endate is greater than the current date less 6 months (ie anything in the > > last six months). > > > > where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid > > between > > 1 and 9) > > and (rpn.enddate > '01/01/2005') or (rpn.enddate is null) > > > > at present as you can see I have stipulated the date as 01/01/2005 however > > i > > need the report to run every week (schedule will be set up for running and > > emailing) and I need the report to have the correct "and" in place. Any > > help > > would be greatly appreciated. the rpn.enedate can either have a date or > > be > > null. > > > > hopefully this is a bit more information. > >
rpn.enddate > datediff(mm,-6,sysdate())
GeoSynch [quoted text, click to view] "Nat Johnson" <NatJohnson@discussions.microsoft.com> wrote in message news:EC35E742-025D-4532-9BC9-DE4C42292816@microsoft.com... > Thanks for the help. Not quite right tho. I need to return all enddates > that are within the last six months or that are null. > > RUnning this returns all enddates - 6 months. > I dont want to change the value of the enddate.....just return enddates that > have occurred during the last 6 months. > > ie. getdate(todays date) - 6 months > > > "Dan Christjohn" wrote: > >> and (rpn.enddate > datediff(mm,-6,rpn.enddate)) >> >> That will subtract six months from rpn.enddate. Here is the msdn ref link: >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp >> >> Good luck! >> >> Peace, >> >> Dan >> >> >> "Nat Johnson" <NatJohnson@discussions.microsoft.com> wrote in message >> news:FC3E4789-C18E-491F-B845-A71E5696EA19@microsoft.com... >> > Have asked previous but not helped...so thought I would give it another >> > go. >> > I have a query with a number of inner joins and left outer joins. The >> > where >> > clause has numerous "and" clauses and one of these I need to be where a >> > endate is greater than the current date less 6 months (ie anything in the >> > last six months). >> > >> > where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid >> > between >> > 1 and 9) >> > and (rpn.enddate > '01/01/2005') or (rpn.enddate is null) >> > >> > at present as you can see I have stipulated the date as 01/01/2005 however >> > i >> > need the report to run every week (schedule will be set up for running and >> > emailing) and I need the report to have the correct "and" in place. Any >> > help >> > would be greatly appreciated. the rpn.enedate can either have a date or >> > be >> > null. >> > >> > hopefully this is a bit more information. >> >> >>
Nat,
The error appears to be in the where clause make-up. The responses are correct insasmuch for getdate()-180 (that's the equivilent to getdate()-6 months, but your problem exists with bringing back nulls also. Your clause reads:: where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid between 1 and 9) and (rpn.enddate > '01/01/2005') or (rpn.enddate is null) In this way you are counting 4 clauses OR one other. Try adding the parems to separate the OR clause part thus (I have separated the 4 AND clauses on different lines for clarity: where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid between 1 and 9) and ((rpn.enddate > getdate()-180) or (rpn.enddate is null)) This will keep the clauses down to 4 (DeletedDate, id, regionareaid, enddate) but will house the OR clause solely for the last AND clause. I often write all AND clauses on different lines, especially when I wish to use and OR clause inside of an AND clause. Hope this assists. [quoted text, click to view] "Nat Johnson" wrote:
> Have asked previous but not helped...so thought I would give it another go. > I have a query with a number of inner joins and left outer joins. The where > clause has numerous "and" clauses and one of these I need to be where a > endate is greater than the current date less 6 months (ie anything in the > last six months). > > where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid between > 1 and 9) > and (rpn.enddate > '01/01/2005') or (rpn.enddate is null) > > at present as you can see I have stipulated the date as 01/01/2005 however i > need the report to run every week (schedule will be set up for running and > emailing) and I need the report to have the correct "and" in place. Any help > would be greatly appreciated. the rpn.enedate can either have a date or be > null. >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |