July 22, 2016 at 5:46 pm
Hi All,
I need help with my report query.I have 2 tables. Table1 has ID and Date.Table2 has Date only.I would like to get all Date from Table2 and ID's from Table1 for corresponding Date.If there is no Id in Table1 then it should say null. Expl:
declare @TABLE1table
(IDint,
XDATEdate );
declare @TABLE2table
(XDATEdate );
insert into @TABLE1 (ID, XDATE) values
(100,'20150505'),
(100,'20150506'),
(100,'20150507'),
(222,'20150505'),
(222,'20150506');
insert into @TABLE2 (XDATE) values
('20150505'),
('20150506'),
('20150507'),
('20150508');
results that i want to see:
IDXDATE
100 2015-05-05
222 2015-05-05
100 2015-05-06
222 2015-05-06
100 2015-05-07
NULL 2015-05-07
NULL 2015-05-08
NULL 2015-05-08
since there is no ID=222 for 2015-05-07 then NULL.Same thing for 2015-05-08.
This is what i came up with,which is not what i want
select
g.ID
,DT.XDATE
from @TABLE2 DT
OUTER APPLY
(
SELECT IDS.XDATE,IDS.ID FROM @TABLE1 IDSWHERE DT.XDATE = IDS.XDATE
)g
ORDER BY 2 ,1
IDXDATE
1002015-05-05
2222015-05-05
1002015-05-06
2222015-05-06
1002015-05-07
NULL2015-05-08
thank you
July 22, 2016 at 9:29 pm
Like this?
CREATE TABLE TABLE1
(IDint,
XDATEdate );
CREATE TABLE TABLE2
(XDATEdate );
GO
insert into TABLE1 (ID, XDATE) values
(100,'20150505'),
(100,'20150506'),
(100,'20150507'),
(222,'20150505'),
(222,'20150506');
insert into TABLE2 (XDATE) values
('20150505'),
('20150506'),
('20150507'),
('20150508');
SELECT t1.id, t2.xdate as t2date
FROM table2 t2
LEFT JOIN table1 t1 on t1.xdate=t2.xdate;
Why not just write this as a standard stored procedure and then use it as the base for your report? No need to use OUTER APPLY at all...
July 22, 2016 at 10:15 pm
Or the other way around
😎
SELECT
T1.ID
,T2.XDATE
FROM dbo.TABLE1 T1
RIGHT OUTER JOIN dbo.TABLE2 T2
ON T1.XDATE = T2.XDATE;
Output
ID XDATE
----------- ----------
100 2015-05-05
222 2015-05-05
100 2015-05-06
222 2015-05-06
100 2015-05-07
NULL 2015-05-08
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply