SQL Server Performance Forum – Threads Archive
Cross Join on a single table
I have the following table<br />log( occuredTime datetime, term varchar(50), termImportance float); <br /><br />Data Characteristics:<br />For each occuredTime, there will certain term will appear and have associated termImportance values..<br /><br /><br />Now, i am looking for a query that returns the following:<br /><br />Between datetime T1 and T2,<br /> For all distinct time values,<br /> For each term appeared during this time period,<br /> if the term is present, return its importance<br /> otherwise return 0.<br /><br />The closest query i got to is: <br /> select distinct l1.occuredTime,l2.term,(case when l2.occuredTime = l1.occuredTime then l2.importanceValue else 0 end) as val from log l1, log l2 where l1.occuredTime between @Time1 and @Time2 and l2.occuredTime between @Time1 and @Time2)<br /><br /><br />This results in more than one value for (timeval1,term2) combination when i am expecting only 6<br /><br />timeval1,term1,0<br />timeval1,term2,0<br />timeval1,term2,6<br />……<br /><br /><br />Novice sql programmer here [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br /><br /><br /><br /><br /><br /><br /><br /><br />
Thanks people! Got my solution!!
select l1.occuredTime,l2.term,sum(case when l2.occuredTime = l1.occuredTime and l1.term=l2.term then l2.importanceValue else 0 end) as val from log l1, log l2 where l1.occuredTime between @Time1 and @Time2 and l2.occuredTime between @Time1 and @Time2 group by l1.occuredTime,l2.term order by l1.occuredTime
]]>