Database

SELECT 
	DATEDIFF(HOUR, '1:00', '23:00')

SELECT 
	DATEDIFF(HOUR, '1:00', '23:00') / 4

SELECT 
	(1 + DATEDIFF(HOUR, '1:00', '23:00')) / 4

SELECT
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N  
FROM 
	sys.all_columns

SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4) 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As N 
FROM 
    sys.all_columns

SELECT 
	DATEADD(HOUR, 5 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 4 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 3 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 2 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 1 * 4, '1:00') As ScheduleTime

SELECT
    ',' + CAST(T.ScheduleTime As char(5))
FROM
    /* Tally table: */
    (
        SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4) 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
        FROM 
            sys.all_columns
    ) As N
    /* Generated schedule: */
    CROSS APPLY 
    (
        SELECT DateAdd(hour, N.n * 4, '1:00') As ScheduleTime
    ) As T
FOR XML PATH('')

WITH cteSource As
(
	SELECT CAST('13:00' As time) As StartTime, 4 As Hours
	UNION SELECT CAST('01:00' As time) As StartTime, 7 As Hours
)
SELECT
    StartTime,
    Hours
    ,STUFF(T.ScheduleTime, 1, 1, '') As ScheduleTime
FROM
    cteSource As S
    CROSS APPLY
    (
        SELECT
			
            ',' + CAST(T.ScheduleTime As char(5))
        FROM
            /* Tally table: */
            (

                SELECT TOP (24 / S.Hours) 
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
                FROM 
                    sys.all_columns
			
            ) As N
            /* Generated schedule: */
            CROSS APPLY 
            (
                
				SELECT DateAdd(hour, N.n * S.Hours, S.StartTime) As ScheduleTime

            ) As T
        FOR XML PATH('')
    ) As T (ScheduleTime)

jkirkerx wrote:

Original SQL Statement:
I don't understand the FLOOR statement in the cast. Does that make the date at midnight or 0:01

Quote:

Returns the largest integer less than or equal to the specified numeric expression.

Quote:

the date at midnight

CAST(b.startup_check_date As date) Between '20210401' And '20210431'

(b.startup_check_date >= '20210401' And b.startup_check_date < '20210501')

DECLARE @date datetime2 = '2000-01-01';
SELECT FORMAT(@date, 'MMM') AS 'FORMAT';

SELECT 
     a.project_no, 
     a.status, 
     a.Sales_no, 
     a.swan_job, 
     b.tc, 
     b.EC, 
     convert(char(10), b.startup_check_date, 120), 
     convert(char(10), b.finished_check_date, 120), 
     e.Employee_ID, 
     e.fname, 
     e.lname, 
     c.customer_no, 
     c.lname, 
     c.fname 
 FROM project AS a, commission_summary AS b, employee AS e, customer AS c 
 WHERE a.project_no = b.project_no AND a.customer_no = c.customer_no 
 AND a.sales_no = e.Employee_ID 
 AND (a.status ='construction' 
 OR a.status ='finished') 
 AND ((CONVERT(char(10), b.startup_check_date, 120) >= '$firstDate' AND CONVERT(char(10), b.startup_check_date, 120) <= '$lastDate') 
 OR (CONVERT(char(10), b.finished_check_date, 120)  >= '$firstDate' AND CONVERT(char(10), b.finished_check_date, 120) <= '$lastDate') 
 OR (b.startup_check_date IS NULL OR CONVERT(char(10), b.startup_check_date, 120) = '1900-01-01') 
 OR ((CONVERT(char(10), b.startup_check_date, 120) <= '$firstDate') 
 AND (b.finished_check_date IS NULL OR CONVERT(char(10), b.finished_check_date, 120) = '1900-01-01')))

David Crow wrote:

String sQuery2 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed) AND vehicle_id="+id;

k5054 wrote:

This appears to be missing a + ")".

k5054 wrote:

Also, what's the value of id.

k5054 wrote:

Just doing a string concatenation could result in SQL injection, so that seems like a bad idea.

SELECT * FROM service_schedules As s WHERE Exists(SELECT 1 FROM services_performed As p WHERE p.vehicle_id = s.vehicle_id And p.service_id = s.service_id) And s.vehicle_id = ?

Richard Deeming wrote:

I can't find any documentation to suggest that Sqlite supports multi-value IN queries; that syntax only seems to apply to MySQL.

Richard Deeming wrote:

Would an Exists query work instead?