Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Sql Query to pull data by dates

I am creating an application to pull delinquent data, based on dates in 30,60,90, 120 day increments.  I wrote a sql query which seems to compile correctly, but errors out when I create it as a view.  I get the following error:

Incorrect syntax near the keyword 'WHERE'.  how is my query incorrect?  

Thanks in advance.  
select a.Title, a.PrdNumber, 
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 1 and 30) 
AS '30 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 31 and 61) as '60 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 62 and 91) as '90 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 92 and 121) as '120 Days'
FROM tmp_Logs_RapidCue r
INNER JOIN tmp_Logs_VCIAsrun a 
ON r.Media = a.PrdNumber
--WHERE a.PrdNumber <> ''
Order by a.Title ASC

Open in new window

0
ayoZen
Asked:
ayoZen
  • 5
  • 3
  • 2
  • +3
2 Solutions
 
Rajesh DalmiaCommented:
what's that -- near WHERE clause?
0
 
Rajkumar GsSoftware EngineerCommented:
When I tested your query, it seems to be working!
Can you check this attached query, that is just realligned your query
Raj
select a.Title, a.PrdNumber, 
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 1 and 30) AS '30 Days',
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 31 and 61) as '60 Days',
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 62 and 91) as '90 Days',
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 92 and 121) as '120 Days'
FROM tmp_Logs_RapidCue r
INNER JOIN tmp_Logs_VCIAsrun a 
ON r.Media = a.PrdNumber
--WHERE a.PrdNumber <> ''
Order by a.Title ASC

Open in new window

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Mohit VijayCommented:
You missed the from cluase.
0
 
Mohit VijayCommented:
you are facing problem in such type of inner queries

(select Datediff(day, a.Schedule_Date, getDate())
WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 1 and 30)

0
 
vdr1620Commented:
just read your question again

Oops.. You get that error because you are trying to create a view with Order by Clause...

***Views cannot be created be created with Order by clause unless its being with TOP in select
0
 
vdr1620Commented:
check this article on views

http://www.devx.com/dbzone/Article/8048/1954
0
 
vdr1620Commented:
below query should work as a view..
select TOP 100 PERCENT WITH TIES a.Title, a.PrdNumber, 
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 1 and 30) AS '30 Days',
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 31 and 61) as '60 Days',
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 62 and 91) as '90 Days',
(select Datediff(day, a.Schedule_Date, getDate()) WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 92 and 121) as '120 Days'
FROM tmp_Logs_RapidCue r
INNER JOIN tmp_Logs_VCIAsrun a 
ON r.Media = a.PrdNumber
--WHERE a.PrdNumber <> ''
Order by a.Title ASC

Open in new window

0
 
ayoZenAuthor Commented:
Thanks so much for your responses.

In my view,  I removed the order by clause and still received the same error.  
vdr1620,  I tried your suggestion with the ties and the error persists.

vjsoft, you mentioned that the problem lies with my inner queries.  I cleaned up my data and I can't see the problem.

please advise.  
select a.Title, a.PrdNumber, 
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 1 and 30) 
AS '30 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 31 and 61) as '60 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 62 and 91) as '90 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 92 and 121) as '120 Days'
FROM tmp_Logs_RapidCue r
INNER JOIN tmp_Logs_VCIAsrun a 
ON r.Media = a.PrdNumber

Open in new window

0
 
vdr1620Commented:
i don't see any error..it works perfectly.. Do you mean that you don't see expected results

Can you post your error message
CREATE TABLE #tmp_Logs_RapidCue
(
media Int
)

CREATE  TABLE #tmp_Logs_VCIAsrun
(
Title Varchar(10),
Schedule_Date Date,
PrdNumber Int
)

INSERT INTO #tmp_Logs_VCIAsrun VALUES ('A','08/20/2010',1),('B','06/20/2010',2)
INSERT INTO #tmp_Logs_RapidCue VALUES (1),(2)

select a.Title, a.PrdNumber, 
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) BETWEEN 1 and 30) 
AS '30 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 31 and 61) as '60 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 62 and 91) as '90 Days',
(select Datediff(day, a.Schedule_Date, getDate()) 
WHERE Datediff(day, a.Schedule_Date, getDate()) 
BETWEEN 92 and 121) as '120 Days'
FROM #tmp_Logs_RapidCue r
INNER JOIN #tmp_Logs_VCIAsrun a 
ON r.Media = a.PrdNumber

DROP TABLE  #tmp_Logs_VCIAsrun
DROP TABLE #tmp_Logs_RapidCue

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
I agree. It executes for me also, but note you really don't need to be using subquery for this.
create view logsView as
select 
   a.Title, a.PrdNumber
 , case 
     when Datediff(day, a.Schedule_Date, getDate()) between 1 and 30
     then Datediff(day, a.Schedule_Date, getDate())
   end AS '30 Days'
 , case 
     when Datediff(day, a.Schedule_Date, getDate()) between 31 and 60
     then Datediff(day, a.Schedule_Date, getDate())
   end AS '60 Days'
 , case 
     when Datediff(day, a.Schedule_Date, getDate()) between 61 and 90
     then Datediff(day, a.Schedule_Date, getDate())
   end AS '90 Days'
 , case 
     when Datediff(day, a.Schedule_Date, getDate()) between 91 and 120
     then Datediff(day, a.Schedule_Date, getDate())
   end AS '120 Days'
FROM tmp_Logs_RapidCue r
INNER JOIN tmp_Logs_VCIAsrun a 
ON r.Media = a.PrdNumber
;

Open in new window

0
 
ayoZenAuthor Commented:
I mispoke.  I retested.  Thank you so much!!!!!!!!!!!
0
 
ayoZenAuthor Commented:
Thank you so much!!!!!!!  The solution was perfect.
0
 
Rajkumar GsSoftware EngineerCommented:
That was me too commented initially  :)

Raj
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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