SQL Statement?

Question

I have a table that contains 2 fields (Date1,Date2,), I want to write SQL statement that displays a table containing Date(Date1 and Date2 without duplications), Count the Number of Date1, Count the Number of Date2

------------------------------...
Date Count1 Count2
2/2/07 10 15
3/2/07 14 9

How can I do that???

Answer 1

What language will you use to display it?

What database software are you using?

What's the name of your database and table that stores this info?

Answer 2

-- setup

create table #t (date1 datetime, date2 datetime)

go



insert into #t values('1/1/2007', '2/2/2007')

insert into #t values('1/1/2007', '2/2/2007')

insert into #t values('3/3/2007', '4/4/2007')

insert into #t values('5/5/2007', '2/2/2007')

insert into #t values('1/1/2007', '3/3/2007')

go



-- query

select datetime, sum(date1count), sum(date2count) from

(select date1 [datetime], count(*) [date1count], 0 [date2count] from #t

group by date1

union

select date2, 0, count(*) from #t

group by date2) t1 group by [datetime]



-- results

/*

2007-01-01 00:00:00.000 3 0

2007-02-02 00:00:00.000 0 3

2007-03-03 00:00:00.000 1 1

2007-04-04 00:00:00.000 0 1

2007-05-05 00:00:00.000 1 0

*/

Answer 3

Are you using T-SQL?



If you are I would like to remind you that the only available datatype is datetime (or smalldatetime). There is no Date data type hence you should be aware of that when comparing dates.



To overcome this issue, you can define a function to get the Date part of the datetime (by setting the time part of the datetime to 0)



Your function should look like:

create function MAKE_ZERO_HOUR (@d1 smalldatetime )

RETURNS SMALLDATETIME

AS

BEGIN

declare @d0 smalldatetime

Select @d0 = convert( smalldatetime, (convert(char(20), @d1 ,102) ) )

RETURN ( @d0 )

END