For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters.
select a.myVal,
b.myVal
from MyTable a
join MyTable b on b.id = a.id
where a.OtherVal = 100
and b.Otherval = 200
For performance, ensure you have proper indexes. In this limited example, ideally you would have a clustered index on ID and a non-clustered index on OtherVal.
After looking at your query, however, I cannot tell just what it is you are trying to accomplish.
Managed to get it to work, below is the answer. Thanks to everyone that replied.
;with cte as(
select [CAB Part No.]
,[RFT/Scrap/Rework]
,[Process No.]
,[Process]
,[Date & Time]
,[Quantity]
,cast([Date & Time] as date) UtcDay
,DATEPART(hour, [Date & Time]) UtcHour
from [SFDLOG]
where [Date & Time] between '2015-02-1' and '2015-02-2'
)
select [CAB Part No.]
,[RFT/Scrap/Rework]
,[Process No.]
,[Process]
,[Quantity]
,dateadd(hour, utchour, cast(utcday as datetime)) as [SFD Hour]
into #TempDates
from cte
select *
from
(
select [Quantity]
,[SFD Hour]
,[CAB Part No.]
,[Process No.]
,[Process]
,[RFT/Scrap/Rework]
from #tempDates
) x
pivot
(
count([Quantity])
for [SFD Hour]
In ([2015-02-01 00:00:00:000],[2015-02-01 01:00:00:000],[2015-02-01 02:00:00:000],[2015-02-01 03:00:00:000],[2015-02-01 04:00:00:000],[2015-02-01 05:00:00:000],[2015-02-01 06:00:00:000]
,[2015-02-01 07:00:00:000],[2015-02-01 08:00:00:000],[2015-02-01 09:00:00:000],[2015-02-01 10:00:00:000],[2015-02-01 11:00:00:000],[2015-02-01 12:00:00:000],[2015-02-01 13:00:00:000]
,[2015-02-01 14:00:00:000],[2015-02-01 15:00:00:000],[2015-02-01 16:00:00:000],[2015-02-01 17:00:00:000],[2015-02-01 18:00:00:000],[2015-02-01 19:00:00:000],[2015-02-01 20:00:00:000]
,[2015-02-01 21:00:00:000],[2015-02-01 22:00:00:000],[2015-02-01 23:00:00:000])
)p
Order By [CAB Part No.],[Process No.]
drop table #tempdates
Best Answer
If I correctly understand your question, you can use two (ore more) ways but both are related with DATEPART function.
Using DayOfYear
Using same week & same day of week
This depends on first day of week:
You can use that function to add another combination, for example: First Thursday of November.
db<>fiddle here