[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2008

Posted on 2010-11-18
10
Medium Priority
?
910 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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 

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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

656 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