Database

declare @tblA table(recordA varchar(10))
declare @tblB table(recordB varchar(10) null)
insert into @tblA select 'recA1' union all select 'recA2'

select a.* from @tblA a full join @tblB b
on a.recordA = b.recordB 

mehrnoosh wrote:

please explain more i am a beginner

Declare @str varchar(100),@strDate datetime
Set @str='14/02/2010'--(dd/mm/yyyy)
Set @strDate=CONVERT(DATETIME, @str,103)
Print @strDate

Declare @str varchar(100)
Set @str='14/02/2010'--(dd/mm/yyyy)
select ConvertedDate = CONVERT(varchar(10),CONVERT(datetime,@str,103),101)--mm/dd/yyyy

ConvertedDate
02/14/2010

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

select 1 as bla
 where bla <> 1

I are Troll

@PageNumber  int,
@PageSize    int,
@MinutesSinceLastInActive   int,
@CurrentTimeUtc             datetime,
@LastName   nvarchar(64),
@UserName   nvarchar(256),
@CityID     int,
@UserStatus bit

AS
BEGIN

DECLARE	@FirstRow INT,
	    @LastRow INT,
	    @TotalRecords INT,
        @DateActive datetime
SELECT  @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc)

SELECT	@FirstRow = 1 + (@PageNumber * @PageSize),
	    @LastRow  = @PageSize  + @FirstRow - 1 ;


  SELECT @TotalRecords =  (SELECT COUNT(*) FROM PD_Users WHERE @LastName = PD_Users.LastName OR (@LastName = '' AND 1=1));
 
WITH Members  AS
(
	SELECT	PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID, 
	        PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
        (SELECT COUNT(*) FROM PD_Articles WHERE  (PD_Users.UserName = UserName)) AS ArticleCount,
        (SELECT COUNT(*) FROM PD_News WHERE  (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
        (SELECT COUNT(*) FROM PD_News WHERE  (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
        (SELECT COUNT(*) FROM  PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
        CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
		ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
			
	FROM	aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
                      PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
    WHERE (@LastName = PD_Users.LastName  OR (@LastName = '' AND 1=1))    
          AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
          AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
          AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))               
)

SELECT	RowNumber, FirstName, LastName, UserName, CityID, CityName, CreateDate,
         ArticleCount,NewsCount, ITNewsCount, MessageCount, IsOnline
FROM	Members
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow AND (@UserStatus = IsOnline)  
ORDER BY RowNumber ASC


RETURN @TotalRecords
END

SELECT	@FirstRow = 1 + (@PageNumber * @PageSize),
	    @LastRow  = @PageSize  + @FirstRow - 1 ;


  SELECT @TotalRecords =  (SELECT COUNT(*) FROM PD_Users WHERE @LastName = PD_Users.LastName OR (@LastName = '' AND 1=1));
 
WITH Members  AS
(

select * from
(

	SELECT	PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID, 
	        PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
        (SELECT COUNT(*) FROM PD_Articles WHERE  (PD_Users.UserName = UserName)) AS ArticleCount,
        (SELECT COUNT(*) FROM PD_News WHERE  (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
        (SELECT COUNT(*) FROM PD_News WHERE  (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
        (SELECT COUNT(*) FROM  PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
        CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
		ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
			
	FROM	aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
                      PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName

) as TempCTE

    WHERE (@LastName = PD_Users.LastName  OR (@LastName = '' AND 1=1))    
          AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
          AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
          AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))               
)

SELECT	RowNumber, FirstName, LastName, UserName, CityID, CityName, CreateDate,
         ArticleCount,NewsCount, ITNewsCount, MessageCount, IsOnline
FROM	Members
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow AND (@UserStatus = IsOnline)  
ORDER BY RowNumber ASC

I are Troll

AND (@UserStatus = (CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end) OR (@UserStatus = 0 AND 1=1))