title: sql server cross join
body: <p>I have the following query:</p>
  
  <pre><code>select 
      tt.ItemOrder, tt.DisplayVal as Task, tt.Responsible as ResParty, 
      tt.DaysDue,  
      ActualDate = (select convert(varchar(10), cnfmdate, 101) from ProSer where PId = @PID), PDate = @PDate 
  from 
      tblTimeline tt
  where
      tt.ID = 1
  </code></pre>
  
  <p>What I need to do is to put it in a view such that I can call the view simply using the PID.
  I came up with the following and used the cross join:</p>
  
  <pre><code>create view view1 as
      select 
          ps.PID, tt.ID, tt.ItemOrder, tt.DisplayVal as Task, 
          tt.Responsible as ResParty, tt.DaysDue,  
          ps.cnfmdate As ActualDate, ProgStartDate as ProgramDate  
      from 
          tblTimeline tt 
     cross join 
          ProSer ps
     where 
          tt.ID = 1 and ps.cancelled = 0 
  </code></pre>
  
  <p>Notice now, I can do the following</p>
  
  <pre><code>select * 
  from view1 
  where PID = '34343'
  </code></pre>
  
  <p>and then I can retrieve it from the view.</p>
  
  <p>Now, I am not sure how to do similiarly with the following in which case I need to put it in a cross join similarly to how I did above.</p>
  
  <p>Notice how actual date is somehat more involved. I need to use the cross table similarly to how I did it above but not as you can see, it is somewhat more involved.</p>
  
  <p>(notice for this part, I will simly join to the view1 that I have above with UNION</p>
  
  <pre><code>select 
      tt.ItemOrder, tt.DisplayVal as Task, tt.Responsible as ResParty, 
      ActualDate = (
            CASE
            WHEN
               NOT EXISTS(SELECT * FROM Spls WHERE RequestRcvd = 1 AND PID = @PID)
            THEN 
               'N/A'       
            WHEN EXISTS (SELECT * FROM spls WHERE RequestRcvd = 1 AND RequestRcvdDate IS NOT NULL)
            THEN
               (SELECT CONVERT(VARCHAR(10),MAX(RequestRcvdDate),101) from spls WHERE RequestRcvd = 1 AND PID = @PID)   
            END
            )   
  from 
      tblTimeline tt
  where 
      tt.ID = 9
  </code></pre>
  
  <p>I need to know how I can create this in a cross join (which will be inside of a view) such that I can do the following similarly to how I did the above one</p>
  
  <pre><code>select * 
  from view1 
  where PID = '34343'
  </code></pre>
  
  <p>and then I can retrieve it from the view.</p>