Sql-server – how to select first 9 numbers from other table from database using sql query

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

• please always use the schema prefix when creating and referencing objects

• please always use a length when converting to varchar etc.

• stay away from ambiguous, regional date formats like mm-dd-yyyy

• consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

  SELECT m.Member_ID
    ,t.Date_Start
    ,t.Hours_Actual
    ,t.Hours_Bill
  FROM dbo.Members AS m
  LEFT OUTER JOIN dbo.Time_Entry AS t
  ON m.Member_ID = t.Member_ID
  AND t.Date_Start = '20131110';
  

  ... a lot tidier, IMHO, as long as you use sensible aliases.

By adding the additional SELECT it pushes the compute scalar evaluation deeper into the plan and gives the join predicate, the compute scalar at the top then references the earlier one.

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT ( SELECT 1 + ABS(CHECKSUM(NEWID())) % (4)) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id

|--Compute Scalar(DEFINE:([Expr1071]=[Expr1070]))

|--Compute Scalar(DEFINE:([Expr1070]=(1)+abs(checksum(newid()))%(4)))

Still digging into just why it waits so late to do it, but currently reading this post by Paul White (https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html). Perhaps it has something to do with the fact that NEWID is not deterministic?