CityInfoSys
asked on
Help With Sql View Query
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
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
ASKER
That is what I was looking for. How would I plug that in? This is what I have so far.
Thanks
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
Thanks
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in that case, all you need is to compute the 2 dates as by "today", which is simple:
Open in new window
so, in your sql, you put:Open in new window