Solved

SQL Server 2008

Posted on 2010-11-18
10
907 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 40

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 40

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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

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
 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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