Solved

SQL Server 2008

Posted on 2010-11-18
10
906 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 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
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…

820 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