July 8, 2008 at 4:05 am
Hi i am trying to write a query that will give me a result set like this
Mon Tues Wed Thu
1 2 3 4
but the data is stored in the opposite format like this
Date Result
1/1/08 1
2/1/08 2
3/1/08 3
4/1/08 4
i have written the following query but each date is retrieved on a seperate line which includes nulls for the other days in the week
Select
Cast (Case when DATENAME(WEEKDAY, se.Date) = 'Monday' then se.PlannedIntensity end as nvarchar) as 'Monday',
Cast (Case when DATENAME(WEEKDAY, se.Date) = 'Tuesday' then se.PlannedIntensity end as nvarchar) as 'Tuesday'
From SessionIntensity se
Join RedZoneWeeklyMapping rzwm on rzwm.ID = 146
Where Date Between rzwm.StartDate And rzwm.EndDate
So i am just wondering if anyone has any tips or advice,
Thanks in advance
Tim
July 8, 2008 at 4:16 am
The above is quite a common solution on 2000. For the null values you can use ISNULL with a 0 parameter.
- Andras
July 8, 2008 at 5:46 am
thanks for your reply but do u know if there is some way i can group the columns because the results from my query are like this
Mon Tue Wed
null null null
null null null
null null null
null null null
1 null null
null 2 null
null null 3
Tim
July 8, 2008 at 6:54 am
What you can do is to encapsulate your query into another select statement, and do a max like:
SELECT SUM(COALESCE(x2.Monday, 0))
, SUM(COALESCE(x2.Tuesday, 0))
FROM (
-- Original select
) AS x2
If you have multiple weeks, then you could pass the week and year to the outside select, and include it in a group by like
SELECT SUM(COALESCE(x2.Monday, 0))
, SUM(COALESCE(x2.Tuesday, 0))
FROM ( SELECT DATEPART(YEAR, se.Date) AS wy
, DATEPART(WEEK, se.Date) AS ww
, CAST(CASE WHEN DATENAME(WEEKDAY, se.Date) = 'Monday'
THEN se.Result
END AS NVARCHAR) AS 'Monday'
, CAST(CASE WHEN DATENAME(WEEKDAY, se.Date) = 'Tuesday'
THEN se.Result
END AS NVARCHAR) AS 'Tuesday'
FROM x AS se
) AS x2
GROUP BY x2.wy
, x2.ww
The inner query returns two extra pieces of information: the year and the week number (wy and ww). The outer query does a group by on these two pieces of information, the COALESCE gets rid of the nulls.
Regards,
Andras
July 8, 2008 at 8:23 am
[font="Verdana"]http://qa.sqlservercentral.com/Forums/Topic529741-149-1.aspx[/font]
MH-09-AM-8694
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply