I have two tables in a SQL Server database: table1
and table2
t开发者_高级运维able1:
DATE NAME CREDIT
1/1/2011 BALU 100
1/3/2011 BALU 200
table2:
DATE NAME DEBIT
1/2/2011 BALU 100
1/3/2011 BALU 50
I want to display the report in following format:
DATE NAME CREDIT DEBIT
1/1/2011 BALU 100 0
1/2/2011 BALU 0 100
1/3/2011 BALU 200 50
My problem is that both tables have a date
column.
How can I write the SELECT
statement with a WHERE
clause given that both tables have the same column named date
?
I am still not positive about what you want, several details are missing. But based on experience working in similar queries I think this is what you might need (or close enough).
SELECT ISNULL(C.DATE, D.DATE) DATE, ISNULL(C.NAME, D.NAME) NAME, SUM(C.CREDIT) CREDIT, SUM(D.DEBIT) DEBIT
FROM TABLE_1 C
FULL JOIN TABLE2 D ON D.DATE=C.DATE AND D.NAME=C.NAME
WHERE ISNULL(C.DATE, D.DATE) BETWEEN @DATE1 AND @DATE2
GROUP BY C.DATE, D.DATE, C.NAME, D.NAME
If I understand your question correctly, thi is what you want:
SELECT ISNULL(T1.Date,T2.Date) Date, ISNULL(T1.Name,T2.Name) Name,
ISNULL(T1.Credit,0) Credit, ISNULL(T2.Debit,0) Debit
FROM table1 T1
FULL JOIN table2 T2
ON T1.Date = T2.Date AND T1.Name = T2.Name
SELECT COALESCE(t1.[DATE], t2.[DATE]) [DATE],
COALESCE(t1.[NAME], t2.[NAME]) [NAME],
COALESCE(t1.[CREDIT], 0) [CREDIT],
COALESCE(t2.[DEBIT], 0) [DEBIT]
FROM Table1 t1
FULL JOIN Table2 t2
ON (t1.[DATE] = t2.[DATE]) AND (t1.[NAME] = t2.[NAME])
I guess you are looking for the datediff function in sql. http://msdn.microsoft.com/fr-fr/library/ms189794.aspx Still i might be wrong since your question is not clearly stated.
Since you don't specify which date is first in the between and the FK linking both table, i will assume..
select ta.date, ta.name, ta.credit, tb.debit from table1 ta
join table2 tb as (ta.name = tb.name)
where ta.date between tb.date
精彩评论