I know the question isn't the best, not sure how I word it.
I am trying to make a SQL statement that has 1 column being the week number from [Requested Ship Date] column and then another column that is supposed to be a concatenated value grouped by the week number.
So far, I have the below and while this is sort of working, it is concatenating by each [Requested Ship Date] instead of the functioned week number.
SELECT DATEPART(week,[Requested Ship Date]) as dt,(SELECT concat(ltrim(rtrim([Item Number])), ' - ', sum([qty remaining])) as con
FROM [TLC].dbo.[OrdersforCutting] t2 WHERE t2.[Requested Ship Date] = t1.[Requested Ship Date] group by ltrim(rtrim([Item Number])) FOR XML PATH('') ) Concatenated FROM [TLC].dbo.[OrdersforCutting] t1
What I have tried:
I think converting
WHERE t2.[Requested Ship Date] = t1.[Requested Ship Date]
as
t2.DATEPART(week,[Requested Ship Date]) = t1.DATEPART(week,[Requested Ship Date])
is what needs to be done but not sure how to do it. Its probably something simple that I'm not thinking of. I have been working on this all morning and probably just need some more coffee...
When I add distinct to the front of my DATEPART function, I get closer. But still has multiple rows and risks losing data.
Any help is appreciated!