I think first of all you need a
dates table in SQL . You can find codes how to create a SQL dates table in SQL Server at given reference.
The following sample uses this date table. Please note that you can change the date period by adjusting the given parameters
declare @date date = '20121201'
;with dates as (
SELECT
number+1 No,
dateadd(dd,number,@date) [date]
FROM master..spt_values
WHERE
Type = 'P'
AND dateadd(dd,number,@date) < dateadd(yy,1,@date)
AND dateadd(dd,number,@date) <= '20121203'
), t as (
select *
from dates, TableB
)
select t.Name, t.date
from t
left join tableA on tableA.UserID = t.UserID and t.date = TableA.CreationDate
where tableA.UserId is null
declare @A table(USERID int, CreationDate date)
insert into @A
select 1, '2012-12-01' union all
select 1, '2012-12-02' union all
select 2, '2012-12-03'
declare @B table(USERID int, Name varchar(10)
)
insert into @B
select 1, 'David' union all
select 2, 'Dan' union all
select 3, 'Oliver'
Select t2.Name, t1.CreationDate as EmptyValueDate
from @A t1
left outer join @B t2 on t1.USERID <> t2.USERID
Order By t2.Name, t1.CreationDate
/*
OUTPUT
Name EmptyValueDate
---------- --------------
Dan 2012-12-01
Dan 2012-12-02
David 2012-12-03
Oliver 2012-12-01
Oliver 2012-12-02
Oliver 2012-12-03
*/
Declare @Table1 Table
(
UserId INT,
CreationDate Date
)
Declare @Table2 Table
(
UserId INT,
UserName Varchar(20)
)
INSERT INTO @Table1
Select 1,'2012-12-01'UNION ALL
Select 1,'2012-12-02'UNION ALL
Select 2,'2012-12-03'
INSERT INTO @Table2
Select 1 ,'David' UNION ALL
Select 2,'Dan'UNION ALL
Select 3, 'Oliver'
;WITH CTE AS
(
Select t.UserId,t1.CreationDate From @Table1 as t1
Cross Apply (Select*From @Table2) as t
Where t.UserId NOT IN (t1.UserId)
)
Select*From CTE
vahid.ch
Member
294 Points
331 Posts
Select table A where not in table B order by date
Dec 27, 2012 02:31 PM|LINK
Hi there,Sorry for posting my problem in here,cause SQL SERVER thread is not working now.
I have two table as below:
A
USERID CreationDate
1 2012-12-01
1 2012-12-02
2 2012-12-03
B
USERID Name
1 David
2 Dan
3 Oliver
Now i want to select the date(s) which is not in table A.
for instance for David 2012-12-01 and 2012-12-02 are in table A and 2012-12-03 is not in Table A,
so I want something like this:
Name EmptyValueDate
DAVID 2012-12-03
DAN 2012-12-01
DAN 2012-12-02
OLIVER 2012-12-01
OLIVER 2012-12-02
OLIVER 2012-12-03
how can I do this?
tnx
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Select table A where not in table B order by date
Dec 27, 2012 02:41 PM|LINK
Something like this might work. You'll have to try it.
select name, creationdate
from tablea cross join tableb
where cast(userid as varchar) + cast(creationdate as varchar)
not in
(select cast(userid as varchar) + cast(creationdate as varchar)
from tablea)
anantDD2007
Member
122 Points
21 Posts
Re: Select table A where not in table B order by date
Dec 27, 2012 03:28 PM|LINK
Hi,
You can use left outer join here to get the required result.
Select B.name,A.creationDate from A left outer join B on A.UserID != B.UserID
Please mark this as answer if this solves your problem.
Thanks,
Anant
eralper
Contributor
6048 Points
971 Posts
Re: Select table A where not in table B order by date
Dec 28, 2012 06:47 AM|LINK
Hi Vahid,
I think first of all you need a dates table in SQL . You can find codes how to create a SQL dates table in SQL Server at given reference.
The following sample uses this date table. Please note that you can change the date period by adjusting the given parameters
If you do not know how to use SQL CTE SQL Server Common Table Expression please have a look at the given tutorial
I hope it helps,
SQL Server 2012
sangeetonly
Member
174 Points
43 Posts
Re: Select table A where not in table B order by date
Dec 28, 2012 10:58 AM|LINK
Declare @TableA table
(FKUSERID int,CreationDate varchar(15) )
Insert into @TableA
Select 1,'2012-12-01'
Union
Select 1,'2012-12-02'
Union
Select 2,'2012-12-03'
Declare @TableB table (PKUSERID int,Name varchar(15))
Insert Into @TableB
Select 1, 'David'
Union Select 2, 'Dan'
Union
Select 3, 'Oliver'
Select name,creationDate from @TableA left outer join @TableB on PKUSERID != FKUSERID Order byPKUSERID
("I would love to change the world, but they won’t give me the source code")
Please: Don't forget to click "Mark as Answer" on the post that helped you. That way future readers will know which post solved your issue.
anantDD2007
Member
122 Points
21 Posts
Re: Select table A where not in table B order by date
Dec 28, 2012 03:01 PM|LINK
Hi,
Please see below query with result. I guess this is what is expected to you. :)
-- Declare two temp table variables (You may not need this)
declare @A as table (userid int, creationDate date)
declare @B as table (userid int, name varchar(100))
-- Insert data
insert into @A values (1,'2012-12-01')
insert into @A values (1,'2012-12-02')
insert into @A values (2,'2012-12-03')
insert into @B values (1,'David')
insert into @B values (2,'Dan')
insert into @B values (3,'Oliver')
-- Actual query to get the required result
Select B.name,A.creationDate
from @A as A left outer join @B as B on A.UserID != B.UserID
Order By B.userid
Result of above query:
name creationDate
David 2012-12-03
Dan 2012-12-01
Dan 2012-12-02
Oliver 2012-12-02
Oliver 2012-12-01
Oliver 2012-12-03
Please mark this as answer if it solves your problem.
Thanks
Anant
sandeepmitta...
Contributor
6801 Points
1059 Posts
Re: Select table A where not in table B order by date
Dec 29, 2012 04:26 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone
sourabh_mish...
Member
6 Points
3 Posts
Re: Select table A where not in table B order by date
Dec 30, 2012 02:30 AM|LINK
Hi Friend,
Just Simple Asmwer.
SELECT B.Name ,A.CreationDate
FROM A LEFT OUTER JOIN B ON
B.UserID <>A.UserID
Please mark this answer.
sourabh_mish...
Member
6 Points
3 Posts
Re: Select table A where not in table B order by date
Dec 30, 2012 02:34 AM|LINK
Hi Friend,
Just Simple Answer.
SELECT b.Name ,A.CreationDate
FROM A
LEFT OUTER JOIN b ON
B.UserID <>A.UserID
Please Mark this answer
adityashah
Member
18 Points
9 Posts
Re: Select table A where not in table B order by date
Dec 31, 2012 12:03 PM|LINK