• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

SQL Server 2008

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
erp1022
Asked:
erp1022
  • 5
  • 3
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
erp1022Author Commented:
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
 
lcohanDatabase AnalystCommented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
erp1022Author Commented:
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
 
BostonMACommented:
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
 
erp1022Author Commented:
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
 
BostonMACommented:
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
 
erp1022Author Commented:
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
 
BostonMACommented:
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
 
erp1022Author Commented:
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.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now