Joining virtual tables in SQL server 2005

I have a simple procedure that uses two identical virtual tables:

declare @t table
(T datetime,  Berth5 int, BerthOther int)

declare @t2 table
(T datetime,  Berth5 int, BerthOther int)

Now after populating those tables I can select from any of those i.e. SELECT * FROM @t but I can't select from join

SELECT * FROM @t inner join @t2 on @t.T=@t2.T

or

SELECT * from [@t] inner join [@t2] on [@t].T= [@t2].T

I receive Must declare the scalar variable "@t"/@t2 (in second example its "Invalid object name '@t') "

EDIT: select * from @t t inner join @t2 t2 on t.T= t2.T Works fine

Using JOIN and SUM in SQL-Server Create View without "DISTINCT"

I'm using SQL Server 2005.

I'm trying to make an index view based on a query of these tables joined:

Table Name: CallRecords

TN         Carrier CallDate     Calls
----------------------------------------------
5558675309 10      2014-12-30   3
5558675309 2       2014-12-30   1
5551457868 13      2014-12-30   14

TableName: PhoneNumbers

TN            CustomerCode
----------------------------------------------
5558675309    205
5551457868    118

Here's my original query and create view:

--query
SELECT DISTINCT PN.CustomerCode,
                CR.CallDate,
                CR.Carrier,
                Sum(CR.Calls) as SumCalls
FROM CallRecords CR
INNER JOIN PhoneNumbers PN ON PN.TN = CR.TN
GROUP BY PN.Customer,
         CR.CallDate,
         CR.Carrier

--create view
CREATE VIEW CallsByCustCodeByCarrierByDay
WITH SCHEMABINDING
AS
SELECT DISTINCT PN.CustomerCode,
                CR.CallDate,
                CR.Carrier,
                Sum(CR.Calls) as SumCalls,
                COUNT_BIG(*) AS CountLines
FROM CallRecords CR
INNER JOIN PhoneNumbers PN ON PN.TN = CR.TN
GROUP BY PN.Customer,
         CR.CallDate,
         CR.Carrier

I am able to create the view but when I try to create an index on the view I get this error:

Cannot create index on view "dbo.CallsByCustCodeByCarrierByDay" because it contains the DISTINCT keyword. Consider removing DISTINCT from the view or not indexing the view. Alternatively, consider replacing DISTINCT with GROUP BY or COUNT_BIG(*) to simulate DISTINCT on grouping columns.

But if I remove "DISTINCT" then my SUM result is incorrect. I think it's because it is multiplying the true sum by the number of times a CustomerCode shows up in the PhoneNumbers table.

Is there a way I can still use this query with an indexed view or am I out of luck?

New fields added to table migrated from SQL2005 to SQL2008R2 remain NULL after INSERT

We have upgraded from SQL Server 2005 to SQL Server 2008R2. For some reason, when I add a new field to a table on the new server, an insert query will populate every field but the new one, leaving it NULL. I am using a field list along with my values list. All of the detailed column properties are the same as adjacent fields which continue to work fine.

It's interesting to note that an UPDATE query will successfully update the new field after the insert. Also interesting is that, if you create a new table from a script (after adding the new field), the new table doesn't experience this problem, so I'm wondering if it has to do with how the DBs were migrated over to the new server (a process I wasn't involved in)... if some flag or parameter did not set properly on the table after it was brought over, and insert queries are ignoring new fields somehow.

SUM Every row as Total

I was looking for before posting but I don´t find anything. I don´t know if is possible what I want.

I want the sum of every column in the same row. For a better explanation, I attach a picture. I am using SQL Server 2005

Example:

Thanks for your time.

'DateValue' is not a recognized built-in function name Sql Server 2005

What is the alternate of MS Access DateValue function in Sql Server Here is the Query

SELECT DISTINCT ss.FileName 
FROM SubmitSheets AS ss 
WHERE 1=1 AND 1=1 AND 1=1 AND 
ss.UpdateTimeA >= DateValue('2014/11/03') AND
ss.UpdateTimeA < DateAdd("d", 1, DateValue('2016/01/06')) AND
(1=1) AND 1=1 ORDER BY ss.FileName

And Here is the Error

Msg 195, Level 15, State 10, Line 4
'DateValue' is not a recognized built-in function name.

This query works fine in MS Access

Including NULL results after join

I am trying to do a report which shows all payments we have received and for the report I have to show names of patients who pay, but this table also contains checks from payers (insurance companies) and after I do a join all of the payers are excluded. I have tried every join version I know left, right, outer, inner, and combinations of the two. SQL Server 2005.

select 

pay.patient_id,
p.lname + ', ' + p.fname as 'Name',
pay.source_type,
pay.instrument,
pay.doc_reference,
pay.instrument_date,
pay.payment_amount,
pay.user_id,
pay.entry_chron,
pay.payor_id


from payment pay
join (select p.*, max(episode_id) over (partition by patient_id) as maxei from patient p) p
on p.patient_id = pay.patient_id

where episode_id = maxei and (pay.instrument_date between '2014-11-01' and '2014-11-30')
order by pay.payment_amount

This is what the results look like for patients with some fields commented out for confidentiality.

These are the fields that are being excluded

count mismatch in sql browser and java JDBC, due to high transaction

As im using sql server 2005 r2. There is more concurrent access on this table1 . when i execute the following query in sql browser im getting 2000 records, but when the same query is executed with java, no of records r varied.

EX: 1st execution --> 2000 records,
    2nd execution --> 2005 records, 
    3rd execution --> 1990 records


 select REGISTRATION_NO as TOTAL_ASSETS_COUNT from Contable1 a 
 inner join table2 b on a.REGISTRATION_NO = b.Registration_no and a.System_id=b.System_id 
 inner join table3 c on b.Registration_no=c.VehicleNo and b.System_id=c.System_id 
 where a.CLIENTID = ? and a.System_id = ? and b.User_id=?

im using isolation level as READ COMMITTED.

Due to high transaction on table1 the count is varied. please help to resolve why the count is varied in java.

thanks in advance.