Solved

Help With Sql View Query

Posted on 2014-03-28
4
505 Views
Last Modified: 2014-03-28
We would like to have it first filter out the Description by CCTV then by ActualFinishDate showing from January 1, 2013 to January 1, 2015. Then when we run it next year it will automatically pull up January 1,2014 to January 1, 2016. Then have the WORKORDERID filted by number. Please see attachment for details.


End Results Example
WORKORDERID Description ACTUALFINISHDATE            ENTITYUID
3                         CCTV           2013-01-06 00:00:00.000   SM02141
3                         CCTV           2013-01-07 00:00:00.000   SM02145
79                       CCTV           2013-01-08 00:00:00.000   SM02148

Thanks
SqlViewExample.png
0
Comment
Question by:CityInfoSys
[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
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39961934
I presume the only difficult part is the 2-year sliding window?
in that case, all you need is to compute the 2 dates as by "today", which is simple:
select getdate()
  , dateadd(year, -1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120))
  , dateadd(year, +1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120))

Open in new window

so, in your sql, you put:
WHERE ActualFinishDate >= dateadd(year, -1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120))
  AND ActualFinishDate  < dateadd(year, +1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120)) 

Open in new window

0
 
LVL 1

Author Comment

by:CityInfoSys
ID: 39962028
That is what I was looking for. How would I plug that in? This is what I have so far.

SELECT   TOP (100) PERCENT azteca.WORKORDER.WORKORDERID, azteca.WORKORDER.DESCRIPTION, azteca.WORKORDER.ACTUALFINISHDATE, 
                         azteca.WORKORDERENTITY.ENTITYUID
FROM            azteca.WORKORDER INNER JOIN
                         azteca.WORKORDERENTITY ON azteca.WORKORDER.WORKORDERID = azteca.WORKORDERENTITY.WORKORDERID
select getdate()
  , dateadd(year, -1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120))
  , dateadd(year, +1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120))
WHERE ActualFinishDate >= dateadd(year, -1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120))
  AND ActualFinishDate  < dateadd(year, +1,convert(datetime, convert(varchar(6), getdate(), 120) + '01-01', 120)) 
ORDER BY azteca.WORKORDER.WORKORDERID

Open in new window


Thanks
0
 
LVL 1

Author Comment

by:CityInfoSys
ID: 39962069
Or would this work?
SELECT        TOP (100) PERCENT azteca.WORKORDER.WORKORDERID, azteca.WORKORDER.DESCRIPTION, azteca.WORKORDER.ACTUALFINISHDATE, 
                         azteca.WORKORDERENTITY.ENTITYUID
FROM            azteca.WORKORDER INNER JOIN
                         azteca.WORKORDERENTITY ON azteca.WORKORDER.WORKORDERID = azteca.WORKORDERENTITY.WORKORDERID
WHERE        (azteca.WORKORDER.DESCRIPTION = 'HYDRO CLEAN') AND (YEAR(azteca.WORKORDER.ACTUALFINISHDATE) >= YEAR(DATEADD(year, - 1, GETDATE())))
ORDER BY azteca.WORKORDER.WORKORDERID

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39962285
try like this.
SELECT        TOP (100) PERCENT azteca.WORKORDER.WORKORDERID, azteca.WORKORDER.DESCRIPTION, azteca.WORKORDER.ACTUALFINISHDATE, 
                         azteca.WORKORDERENTITY.ENTITYUID
FROM            azteca.WORKORDER INNER JOIN
                         azteca.WORKORDERENTITY ON azteca.WORKORDER.WORKORDERID = azteca.WORKORDERENTITY.WORKORDERID
WHERE        (azteca.WORKORDER.DESCRIPTION = 'HYDRO CLEAN') -- Change the Description to CCTV if you want to filter on CCTV 
AND azteca.WORKORDER.ACTUALFINISHDATE >= DATEADD(YY,-1,DATEADD(YY, DATEDIFF(YY,0,getdate()), 0))
AND  azteca.WORKORDER.ACTUALFINISHDATE < DATEADD(YY,1,DATEADD(YY, DATEDIFF(YY,0,getdate()), 0))
ORDER BY azteca.WORKORDER.WORKORDERID

Open in new window

0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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