Click here to Skip to main content
15,885,032 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
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
SQL
WHERE t2.[Requested Ship Date] = t1.[Requested Ship Date]

as
SQL
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!
Posted
Updated 8-May-23 18:40pm
Comments
Maciej Los 8-May-23 15:42pm    
Can you share sample data and expected output?

1 solution

Have you tried:
SQL
WHERE DATEPART(week,t2.[Requested Ship Date]) = DATEPART(week,t1.[Requested Ship Date])
 
Share this answer
 
Comments
Cody O'Meara 9-May-23 10:49am    
Perfect, knew it was something so simple... Thank you!
OriginalGriff 9-May-23 11:23am    
You're welcome!

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