Click here to Skip to main content
15,115,146 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to display days of the week with the date like this


Fulltime FulltimeTicket_Value Contract Casual
Saturday, 2018/06/09 1 500 2 3
Thursday, 2018/06/07 2 1000 1 4
Wednesday, 2018/06/06 0 0 0 0
Friday, 2018/06/08 3 1500 2 2
Tuesday, 2018/06/05 1 500 4 1

What I have tried:

SELECT  Date_to_Display = CAST(Time AS DATE)
     ,Fulltime       = SUM(CASE WHEN jobtype = 'fulltime' THEN 1  ELSE 0 END)
     ,FulltimeTicket_Value=SUM(CASE WHEN jobtype='fulltime' THEN 1 ELSE 0 END)*
                           MAX(CASE WHEN jobtype='fulltime' THEN Vale ELSE 0 END)
     ,Contract       = SUM(CASE WHEN jobtype = 'contract' THEN 1 ELSE 0 END)
     ,Casual         = SUM(CASE WHEN jobtype = 'casual' THEN 1 ELSE 0 END) 
FROM CanLog AS clog
WHERE Time >= '2018-02-12' and time < '2018-02-15' 
GROUP BY CAST(Time AS DATE)
ORDER BY CAST(Time AS DATE);
Posted
Updated 23-Jun-18 17:17pm
Comments
Mike V Baker 23-Jun-18 22:14pm
   
So what you want for FulltimeTicket_Value is the number of fulltime jobs multiplied by the max vale? So if you have 5 records and the Vale is 2, 3, 5, 500, 6 then you want 2500, right? SUM * MAX or 5 * 500

1 solution

You can use SQL DATENAME function to get the day of the week. Here is an example.

SQL
DECLARE @temp TABLE (Id INT Identity(1,1), SomeDate Date)
INSERT INTO @temp 
	SELECT '2018/06/09'
	UNION
	SELECT '2018/06/07'

SELECT DATENAME(dw,SomeDate) +', ' + CONVERT(VARCHAR(10),SomeDate) 'Date_to_Display'
FROM @temp


Output:
Date_to_Display
Saturday, 2018-06-09
Thursday, 2018-06-07
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900