help for building a SQL statement

[zathrone]

I have attached a small ms-word file which contains a database structure from which a sql statement has to be prepared to generate a web report in ASP on the basis of date range.

Thanks in advance...

[MAK]

use northwind


create table games
(ID int,Date datetime, RegistrationNumber varchar(10),Game varchar(10))

insert into games select 1,'2006-02-15 12:00 PM','AB-123','Won'
insert into games select 2,'2006-02-15 12:16 PM','AB-123','Won'
insert into games select 3,'2006-02-15 12:30 PM','AB-123','Lost'
insert into games select 4,'2006-02-15 12:40 PM','AB-123','Lost'
insert into games select 5,'2006-02-15 12:50 PM','AB-123','Won'
insert into games select 6,'2006-02-15 2:50 PM','AB-546','Lost'
insert into games select 7,'2006-02-15 12:50 PM','AB-546','Won'
insert into games select 8,'2006-02-17 12:50 PM','AB-123','Lost'
insert into games select 9,'2006-02-18 12:50 PM','AB-789','Lost'
insert into games select 10,'2006-02-19 12:50 PM','AB-416','Lost'
insert into games select 11,'2006-02-20 12:50 PM','AB-222','Lost'

select Date=convert(datetime,convert(varchar(10),date,112 )),RegistrationNumber,
WON=sum(case game when 'Won' then 1 else 0 end) ,
LOST=sum(case game when 'Lost' then 1 else 0 end)
from games group by
RegistrationNumber,convert(datetime,convert(varcha r(10),date,112))

[zathrone]

Thanks heap man. I really appreciate your help and it did help me a lot...

use northwind


create table games
(ID int,Date datetime, RegistrationNumber varchar(10),Game varchar(10))

insert into games select 1,'2006-02-15 12:00 PM','AB-123','Won'
insert into games select 2,'2006-02-15 12:16 PM','AB-123','Won'
insert into games select 3,'2006-02-15 12:30 PM','AB-123','Lost'
insert into games select 4,'2006-02-15 12:40 PM','AB-123','Lost'
insert into games select 5,'2006-02-15 12:50 PM','AB-123','Won'
insert into games select 6,'2006-02-15 2:50 PM','AB-546','Lost'
insert into games select 7,'2006-02-15 12:50 PM','AB-546','Won'
insert into games select 8,'2006-02-17 12:50 PM','AB-123','Lost'
insert into games select 9,'2006-02-18 12:50 PM','AB-789','Lost'
insert into games select 10,'2006-02-19 12:50 PM','AB-416','Lost'
insert into games select 11,'2006-02-20 12:50 PM','AB-222','Lost'

select Date=convert(datetime,convert(varchar(10),date,112 )),RegistrationNumber,
WON=sum(case game when 'Won' then 1 else 0 end) ,
LOST=sum(case game when 'Lost' then 1 else 0 end)
from games group by
RegistrationNumber,convert(datetime,convert(varcha r(10),date,112))