Solved

SQL Server 2008

Posted on 2010-11-18
10
904 Views
Last Modified: 2012-05-10
Hello,

I have attached a document that contains the query I've written using several CTE's. I have attempted to use a variable for the first CTE (the Agency variable) which works fine. But then I've tried to insert another variable of date range in the second CTE. Instead of pulling just records for that date range, I am getting everything.

Could someone please take a look and let me know what the problem is? I have highlighted in yellow where I want my date range to be taken from.

Thanks.
GHS-Query.doc
0
Comment
Question by:erp1022
  • 5
  • 3
  • 2
10 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 34168014
I think you must include the same AND (h.DOCDATE between @StartDate and @EndDate)  to the where clause of each subquery where you use SOP30200 h in a JOIN or FROM clause.
0
 

Author Comment

by:erp1022
ID: 34168070
Hey, thanks for your response.

Unfortunately, that won't work. Each CTE is pulling a different transaction type from my table. I want this query (which is being used for an SRS report) to pull the date for the 'Gov' transaction type, my second CTE in the query. This works fine when I put my date variables just in the Acquisition section (the first CTE) but my end users would like the option to also look at the data by 'Gov' date range.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34168197
Well in that case don't you need another set of parameters if the date ranges are different?
One pair for the  Acquisition section and one for the GOV section?
0
 

Author Comment

by:erp1022
ID: 34168483
Yes, I do need another set of paramaters.

My report will look like this: (I'm not typing in all of my report fields below, just the relevant ones)

Acquisition | Acquisition Date | Acquisition Amount | Invoice | Invoice Date | Invoice Amount | Resale | Resale Date | Resale Amount

(Invoice corresponds to the CTE called 'Gov')

Acquisition, Invoice and Resale are my different transactions types, which also correspond to the CTEs I created. All of the above transactions will take place on different dates, so the end users would like the option to pull data not only for an acquisition date range, but the date range of when it was Invoiced (Gov), or when it was resold.

Again, if I use the date parameters on the Acquisition part of my CTE, it works fine. Now I just need to get this working for the Gov part. Any ideas?

0
 
LVL 4

Expert Comment

by:BostonMA
ID: 34174982
What error message are you getting?     I actually think its a syntax issue:


should be

,Gov (SopNumbe, CustNMBR,  DOCDATE, TRXSORCE, ITEMNMBR) AS

(
(select h.SOPNUMBE
,h.CUSTNMBR
,convert(varchar,h.DOCDATE,1)as DOCDATE
,d.TRXSORCE
,d.ITEMNMBR
,CASE
      WHEN ITEMDESC like '%Amend%' then 'Amended Value'
      WHEN ITEMDESC like '%Apprais%' then 'Appraised Value'
END As ItemDesc
,d.UNITPRCE
,d.XTNDPRCE
,d.XTNDPRCE/NULLIF(d.UNITPRCE,0) as FeeBilled
FROM RM00101 c
INNER JOIN SOP30200 h
ON c.CUSTNMBR = h.CUSTNMBR
INNER JOIN SOP30300 d
ON h.SOPNUMBE = d.SOPNUMBE
where d.ITEMNMBR LIKE '___1125' OR
    d.ITEMNMBR LIKE '___1120' OR
    d.ITEMNMBR LIKE '___1110' OR
    d.ITEMNMBR LIKE '___1105' OR
    d.ITEMNMBR LIKE '___1100' AND
    d.TRXSORCE not like 'SLSVT%' AND
    h.CUSTNAME <> '' AND
    h.SOPNUMBE not in (select APTODCNM from RM20201 where APFRDCNM like 'RTN%') AND
    h.SOPTYPE = 3 AND
    (h.DOCDATE between @StartDate and @EndDate)

)

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:erp1022
ID: 34175431
I'm not getting an error message. In my initial post I stated that instead of giving me the dates I selected for my parameter, it is giving me everything, all dates.

It is not a syntax issue either. As I stated in a couple of my other comments, my query works fine. It pulls all the appropriate data, dates, etc. when my variables are in the first CTE. When I try to move the date variables to my second CTE, it does not work. No error, just giving me all dates instead of the dates I am entering for the date range.
0
 
LVL 4

Expert Comment

by:BostonMA
ID: 34175458
Ok so its a query issue.  Dont you need paranthesis around your 'or' statements?  Otherwise the query is treating those statements as not part of your later 'and' requirements.
0
 

Author Comment

by:erp1022
ID: 34176539
There is no issue with the OR statements. The issue is that when I try to put date variables in my second CTE it pulls all dates, not the dates entered when prompted.
0
 
LVL 4

Accepted Solution

by:
BostonMA earned 500 total points
ID: 34176657
I use CTE's all the time and I've never experience an issue like you are describing that the variable is not being set for the second CTE.  It makes me think something else is the problem.  In order to troubleshoot can you try this, remove all the other logic from the CTE ad just run it with the date parameter?  



Declare @Agency nvarchar(50),
@StartDate datetime,
@EndDate datetime;

Set @Agency = 'COE'
Set @StartDate = '08/01/10'
Set @EndDate = '08/30/10';


with Acquisitions as
(select c.USERDEF2
,h.SOPNUMBE
,h.CUSTNMBR
,h.CUSTNAME
,convert(varchar,h.DOCDATE,1) as DOCDATE
,d.TRXSORCE
,d.ITEMNMBR
,CASE
      WHEN ITEMDESC like '%Purch%' then 'Home Acquisition'
      ELSE ITEMDESC
END As ItemDesc
,d.XTNDPRCE
FROM RM00101 c
INNER JOIN SOP30200 h
ON c.CUSTNMBR = h.CUSTNMBR
INNER JOIN SOP30300 d
ON h.SOPNUMBE = d.SOPNUMBE
where d.ITEMNMBR LIKE '___2057' AND
      d.TRXSORCE not like 'SLSVT%' AND
      h.CUSTNAME <> '' AND
      h.SOPNUMBE not in (select APTODCNM from RM20201 where APFRDCNM like 'RTN%') AND
      h.SOPTYPE = 3 AND
      c.USERDEF2 =(@Agency))

,Gov as
(select h.SOPNUMBE
,h.CUSTNMBR
,convert(varchar,h.DOCDATE,1)as DOCDATE
,d.TRXSORCE
,d.ITEMNMBR
,CASE
      WHEN ITEMDESC like '%Amend%' then 'Amended Value'
      WHEN ITEMDESC like '%Apprais%' then 'Appraised Value'
END As ItemDesc
,d.UNITPRCE
,d.XTNDPRCE
,d.XTNDPRCE/NULLIF(d.UNITPRCE,0) as FeeBilled
FROM RM00101 c
INNER JOIN SOP30200 h
ON c.CUSTNMBR = h.CUSTNMBR
INNER JOIN SOP30300 d
ON h.SOPNUMBE = d.SOPNUMBE
where
h.DOCDATE between @StartDate and @EndDate

)

select * from Gov
0
 

Author Comment

by:erp1022
ID: 34188537
Yes, I will give that a shot.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

13 Experts available now in Live!

Get 1:1 Help Now