You could do something like this

SELECT src.AssessmentCode, src.AssessmentDescriptio, m.Mark
FROM YourSchema.YourTable AS src
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS m(Mark)

Based on your provided sample data, this query will produce the correct result.

WITH cteCrossTab AS (
  /* Use a Cross-Tab query to get the data in...

Based on your provided sample data, this query will produce the correct result.

WITH cteBase AS (
  SELECT t0.*, rn = ROW_NUMBER() OVER (PARTITION BY t0.uniqueid, t0.[level]...

• This reply was modified 11 months, 1 weeks ago by DesNorton.

Without test data we are just guessing.

Maybe this will do the trick

DECLARE @SearchDate date = CONVERT(date, '12/29/2024', 101);

SELECT *
FROM DueDates
WHERE CONVERT(date, textDate , 1) > @SearchDate
ORDER BY...

Maybe your "date" needs quotes

CONVERT(date, textDate , 1) > CONVERT(date, 'mm/dd/yy', 1)

You can convert it to a date/datetime with

CONVERT(date, '12/31/24', 1)

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

Correct.  However (IT DEPENDS), there are times when a variable will not be updated using ISNULL.  That is why it is sometimes necessary to use the other syntax.  In the...

• This reply was modified 11 months, 3 weeks ago by DesNorton.

I generally use the 2nd version of...

Duplicate post

I would look at "forcing" the overlap.  This can be achieved by applying an extra CTE at the start of the above code

 WITH ForcedOverlap AS (
...

• This reply was modified 1 years ago by DesNorton.

You dont show your expected output.

Maybe this will point you in the right direction

WITH cteLag AS (
    SELECT  lt.Number
    ...

This code illustrates how AT TIME ZONE automatically caters for DST

DECLARE @Data table (
  MyTimeUTC datetime
);

INSERT INTO @Data ( MyTimeUTC )
VALUES ( '2024-05-01 00:31:15' )
 ...

I am having difficulty understanding what you are trying to achieve.  Maybe some sample data and expected results will help.

That said, I suspect that your design is going to fall...

Always store your datetimes as UTC

The DB creates the timestamp in UTC

The when selecting,

SELECT LocalTime = TimeFromtable AT TIME ZONE 'UTC' AT TIME ZONE 'your clients timezone'
FROM...

This is an alternate piece of code that will match your 2nd attempt

SELECT [IN_1_NOT_IN_2] = s1.SYMBOL
     , [IN_2_NOT_IN_1] = s2.SYMBOL
FROM (
  ...