I recently saw this syntax used in an example - SELECT <col_name> INTO <new_table> FROM (SELECT 'Literal') AS EX(<col_name>);      Now I have figured out what it accomplishes...

It's a shame you're on SQL Server 2012 because the graph database functionality implemented in SQL Server 2017 is perfect for this.

https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-sample?view=sql-server-ver16

Might be worth installing somewhere and testing/creating...

I'd have one DimOrder row per order. Say the order total is £10,000, made on June 1st and they pay in three instalments of £5,000, £3,000 and £2,000, made on...

I'd suggest DimOrder as a normal conformed dimension.

I'd also suggest that you have negative surrogate keys that will allow you to report smarter:

It's common practice to have -1 as an...

Joining fact tables isn't a great idea - people may get confused, performance won't be great, etc.

Do payments always relate to an order or can there be another source?

I'd be...

Agreed, I was just tidying up their code. An alternative:

SELECT 
CONVERT(VARCHAR(25),Datefield,105)+' '+CONVERT(VARCHAR(25),Datefield,108)
FROM
Table1

• This reply was modified 2 years, 2 months ago by  MarkP. Reason: Code formatting

Change hh to HH

Also if you actually want your format, change your "/"s to "-"s

Add

LEFT OUTER JOIN dbo.TrainingParticipants ON dbo.TrainingParticipants.TrainingID = dbo.TrainingHeader.TrainingID

to the bottom of your query.

Your join between TrainingHeader and TrainingParticipants (on ParticipantID = TrainingID) returns 13 rows as a left outer join and only 1 row as an inner join. Are you sure your...

Hello there. What query are you running to not return the right result?

CAST(Timestamp AS DATE) will do that for you. It will strip the time part out.

2022-09-22 14:30:54.843 becomes 2022-09-22

WITH TEST
AS (SELECT CAST(CURRENT_TIMESTAMP AS DATE) JUSTDATE,
CURRENT_TIMESTAMP TSTAMP
UNION
SELECT CAST(CURRENT_TIMESTAMP AS...

Basic stuff @voldemarg.

Distinct dates

SELECT COUNT(DISTINCT CAST(timestamp AS DATE)) FROM TABLE

Rows per day

SELECT CAST(timestamp AS DATE) AS TransactionDate, COUNT(*) FROM TABLE GROUP BY...

Good to hear you've got a working solution. Communication is the key now - make sure you're told in advance if the format will change again so you've got time...

Will it always be that format:

Apple (red) (23)

Apple (23)

Grape (green) (12)

Grape (12)

And so on?

If so, you could use a CASE expression to count the number of opening brackets:

CASE WHEN LEN(Fields!Field.Value)...

This thread on MSDN should help:

https://social.msdn.microsoft.com/Forums/en-US/782b92bc-d1d4-403b-8a3b-30c168160b2f/client-connectivity-tools?forum=sqlsetupandupgrade