Solved

Help With Sql View Query

Posted on 2014-03-28
4
443 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
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now