September 29, 2009 at 1:25 pm
Hi,
I'm puzzled with what seems to be a stupid question, but I'd greatly appreciate your insight. Here is a simple query:
SELECT DISTINCT [orderName], [TB_vpmPallet].[vpmNoPallet], [TB_vpmPalletColis].[NoColis]
FROM [TB_vpmOrder]
INNER JOIN [TB_vpmPallet] ON [TB_vpmOrder].[orderStore] = [TB_vpmPallet].[vpmStore]
INNER JOIN [TB_vpmPalletColis] ON [TB_vpmPallet].[vpmNoPallet] = [TB_vpmPalletColis].[NoPallet]
WHERE [orderTreated] BETWEEN @Date1 AND @Date1 + 1 AND [TB_vpmPallet].[palDateClosed] IS NOT NULL
the result is:
CA0001 - Centre RocklandP090929001C3087600112
CA0001 - Centre RocklandP090929001C3087600412
CA0001 - Centre RocklandP090929002S3087600212
CA0001 - Centre RocklandP090929002S3087600512
CA0001 - Centre RocklandP090929002S3087600712
what I want is to have a result like:
CA0001 - Centre Rockland2 (pallet count) 5 (box count)
is there any easy way I can do that without using temp table?
thanks a lot for your help!
September 29, 2009 at 1:36 pm
If you could post the table definitions and some sample data (using insert statements) that would help. But the below code should work. I can't test it since I don't have the definitions but let me know.
SELECT [orderName], count(distinct [TB_vpmPallet].[vpmNoPallet]), count( distinct [TB_vpmPalletColis].[NoColis])
FROM [TB_vpmOrder]
INNER JOIN [TB_vpmPallet] ON [TB_vpmOrder].[orderStore] = [TB_vpmPallet].[vpmStore]
INNER JOIN [TB_vpmPalletColis] ON [TB_vpmPallet].[vpmNoPallet] = [TB_vpmPalletColis].[NoPallet]
WHERE [orderTreated] BETWEEN @Date1 AND @Date1 + 1 AND [TB_vpmPallet].[palDateClosed] IS NOT NULL
group by [orderName]
September 29, 2009 at 1:41 pm
great!! it works!! I didn't knew I could put the distinct keyword inside the COUNT method!
thanks a lot!!!! 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply