Difficult View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Difficult View

Author

Topic

Acoustic1978
Starting Member

7 Posts

Posted - 2010-03-04 : 12:44:57

Hello forum

I have a table (tblTasks)with two columns (colTask and colDate). I'm trying to create a view with seven columns(Sun, Mon, Tue...Sat)which places the tasks under these columns depending on which day of the current week they occur. I am clueless as to how I'd even begin this.

How do I go about it?

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:50:30

[code]CREATE VIEWS yourview
AS
SELECT COUNT(CASE WHEN DATENAME(dw,colDate)='Monday' THEN colTask ELSE NULL END) AS Mon,
COUNT(CASE WHEN DATENAME(dw,colDate)='Tuesday' THEN colTask ELSE NULL END) AS Tue,
...
COUNT(CASE WHEN DATENAME(dw,colDate)='Saturday' THEN colTask ELSE NULL END) AS Sat
FROM tblTasks
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Acoustic1978
Starting Member

7 Posts

Posted - 2010-03-04 : 13:03:44

quote:
Originally posted by visakh16

CREATE VIEWS yourview
AS
SELECT COUNT(CASE WHEN DATENAME(dw,colDate)='Monday' THEN colTask ELSE NULL END) AS Mon,
COUNT(CASE WHEN DATENAME(dw,colDate)='Tuesday' THEN colTask ELSE NULL END) AS Tue,
...
COUNT(CASE WHEN DATENAME(dw,colDate)='Saturday' THEN colTask ELSE NULL END) AS Sat
FROM tblTasks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you, Visak! It worked brilliantly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 13:33:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources