September 13, 2005 at 2:59 am
Wondering if any TSQL gurus can lend a hand. I have the following select statement which sums the number of occurances of a date value in the columns bronze, silver and gold in the tblparticipants. The date values for the columns bronze, silver & gold must be in the range @start to @end. Tblparticipants structure looks like
PK Bronze Silver Gold
2 1/1/55 3/3/83
3 3/3/87 3/5/83
4 5/3/93
--
ie not all rows contain date values in all columns
The output im after looks something like this if @start=1/1/05 & @end=31/3/05
MDate BronzeTotal SilverTotal GoldTotal
January 2 2 3
February 3 4 4
March 3 3 3
etc
The problem i have is in the where clause, in its current form the results returned includes a date range covering all all the rows in the table, it is not limiting to @start - @end, if I remove lines 13 and 14 the date range works but I somehow need to include date restrictions for silver and gold as well. Any help would be appreciated.
SELECT
datename(m, bronze) + ' ' + CONVERT(char(4), year(bronze)) MDate,
sum(case when (bronze between convert(datetime, @start) and convert(datetime, @end)) then 1 else 0 end) MBronzeTotal,
sum(case when (silver between convert(datetime, @start) and convert(datetime, @end)) then 1 else 0 end) MSilverTotal,
sum(case when (gold between convert(datetime, @start) and convert(datetime, @end)) then 1 else 0 end) MGoldTotal
FROM
tblparticipants
WHERE
bronze between convert(datetime, @start) and convert(datetime, @end) or
silver between convert(datetime, @start) and convert(datetime, @end) or 13
gold between convert(datetime, @start) and convert(datetime, @end) 14
GROUP BY
year(bronze), month(bronze), datename(m, bronze) + ' ' + CONVERT(char(4), year(bronze))
ORDER BY
year(bronze), month(bronze)
September 13, 2005 at 3:47 am
I think you need to insert the different months into a table (could be a temp table) in order to have something you can use in a join with your table. Then I imagine you do something like the following (which may not be what you want, but I hope it can be modified to solve your problem):
create table tblparticipants(pk int, Bronze datetime null, Silver datetime null, Gold datetime null)
go
delete tblparticipants
insert into tblparticipants
select 2, '2005-1-1', '2005-1-1', null
union all
select 3, null, '2005-3-3', '2005-3-3'
union all
select 4, '2005-3-5', '2005-3-5', null
create table dates(date datetime)
go
insert into dates select '2005-1-1'
insert into dates select '2005-2-1'
insert into dates select '2005-3-1'
go
select
d.date,
sum(case when d.date<=p.bronze and p.bronze<dateadd(m, 1, d.date) then 1 else 0 end),
sum(case when d.date<=p.silver and p.silver<dateadd(m, 1, d.date) then 1 else 0 end),
sum(case when d.date<=p.gold and p.gold<dateadd(m, 1, d.date) then 1 else 0 end)
from dates d inner join tblparticipants p on 1=1
group by d.date
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply