November 3, 2021 at 6:53 pm
Got it, thanks. I think part of my dilemma has been those controls are so small, and when I hover over them the descriptions are covered up by Windows icons.
Steve Anderson
November 3, 2021 at 7:05 pm
Phil:
Happy to comply, but I'm not sure I know how to do those things. I will try to figure it out.
Other sites have controls for these things, which I do not see here.
Please see the article at the first link in my signature line below for one way to do it. See the post at the link that Phil Provided for another and the code in the post right below Phil's post for yet another.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2021 at 7:08 pm
Just one more thing... The example above spans 29 months. Is there a good way to separate it out to place three columns for each month within the date range?
Yes but can you post an example output just so we're sure? What I'm concerned about is that it sounds like you want 29 sets of 3 columns all spread out horizontally for a total of more than 29*3 or 87 columns wide. That CAN be done auto-magically but I want to make sure that's what you actually want.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2021 at 7:41 pm
IF OBJECT_ID('tempdb..#PartTransactions','U') IS NOT NULL
DROP TABLE #PartTransactions
GO
SELECT *
INTO #PartTransactions
FROM (VALUES ('2020-12-31 19:00:09',3,7000917),
('2020-09-29 12:05:25',1,7000917),
('2020-09-28 06:56:42',2,7000917),
('2019-12-31 23:59:30',3,7000917),
('2019-09-07 17:38:34',3,7000917),
('2019-09-07 17:38:34',3,7000917),
('2020-09-29 12:05:25',1,7000918),
('2020-09-28 06:56:42',2,7000918),
('2019-12-31 23:59:30',3,7000918),
('2019-09-07 17:38:34',3,7000918),
('2019-09-07 17:38:34',3,7000918),
('2019-09-07 17:16:44',3,7000917))T(imtTransactionDate, imtTransactionType, [imtPartID])
GO
SELECT pt.[imtPartID],
SUM(CASE WHEN pt.imtTransactionType = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.imtTransactionType = 2 THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pt.imtTransactionType = 3 THEN 1 ELSE 0 END) AS Adjustment
FROM #PartTransactions pt
WHERE pt.imtTransactionDate BETWEEN '2019-01-01' AND '2021-01-01'
GROUP BY pt.[imtPartID]; November 3, 2021 at 8:16 pm
Just one more thing... The example above spans 29 months. Is there a good way to separate it out to place three columns for each month within the date range?
Include the month in the GROUP BY:
SELECT pt.[imtPartID],
CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, pt.[imtTransactionDate]), 0) AS date) AS TransMonth,
SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
FROM [M1_SU].[dbo].[PartTransactions] pt
WHERE pt.[imtPartID] = '7000917'
AND pt.[imtTransactionDate] BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
GROUP BY pt.[imtPartID], CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, pt.[imtTransactionDate]), 0) AS date)
ORDER BY [imtPartID], TransMonth
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 3, 2021 at 8:37 pm
stephen.aa wrote:Just one more thing... The example above spans 29 months. Is there a good way to separate it out to place three columns for each month within the date range?
Yes but can you post an example output just so we're sure? What I'm concerned about is that it sounds like you want 29 sets of 3 columns all spread out horizontally for a total of more than 29*3 or 87 columns wide. That CAN be done auto-magically but I want to make sure that's what you actually want.
Just bumping my own response to see if you saw it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2021 at 6:41 pm
Just one more question...
If there are no values, thus no sums in the Pivot Query below, is there a way to have it return zero's?
SELECT pt.[imtPartID],
SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
FROM [M1_SU].[dbo].[PartTransactions] pt
WHERE pt.[imtPartID] = 'M900156'
AND pt.imtTransactionDate BETWEEN '8/1/2021' AND '8/31/2021'
GROUP BY pt.[imtPartID]
Steve Anderson
November 10, 2021 at 7:09 pm
Just one more question...
If there are no values, thus no sums in the Pivot Query below, is there a way to have it return zero's?
SELECT pt.[imtPartID],
SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
FROM [M1_SU].[dbo].[PartTransactions] pt
WHERE pt.[imtPartID] = 'M900156'
AND pt.imtTransactionDate BETWEEN '8/1/2021' AND '8/31/2021'
GROUP BY pt.[imtPartID]
Do you mean if no rows are returned from the query?
November 16, 2021 at 5:40 pm
Below query will give you the desired output.
SELECT
PVT.[imtPartID],
PVT.[1] AS Receipt,
PVT.[2] AS Issue,
PVT.[3] AS Adjustment
FROM
(
SELECT
[imtPartID],
imtTransactionType
FROM #PartTransactions
) AS Z
PIVOT (COUNT(imtTransactionType) FOR imtTransactionType IN ([1],[2],[3]))AS PVT
November 24, 2021 at 10:42 pm
This is great.
So now I need to add another fiend into the mix. There must be some rules to make this happen. Basically, I need to add the other field, imtPurchaseQuantityReceived for each transaction type. I will study up on this further, but somehow to sum that field for each transaction type.
Thanks
--SELECT COUNT(*) AS COUNT FROM(
SELECT pt.[imtPartID], pt.[imtPurchaseQuantityReceived]
SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
FROM [M1_SU].[dbo].[PartTransactions] pt
WHERE pt.[imtPartID] = '3001617'
--AND pt.imtTransactionDate BETWEEN '5/12/2021' AND '5/31/2021'
--AND pt.imtTransactionDate BETWEEN '6/1/2021' AND '6/30/2021'
AND pt.imtTransactionDate BETWEEN '7/1/2021' AND '7/31/2021'
--AND pt.imtTransactionDate BETWEEN '8/1/2021' AND '8/31/2021'
--AND pt.imtTransactionDate BETWEEN '9/1/2021' AND '9/30/2021'
--AND pt.imtTransactionDate BETWEEN '10/1/2021' AND '10/31/2021'
--AND pt.imtTransactionDate BETWEEN '11/1/2021' AND '11/10/2021'
GROUP BY pt.[imtPartID], pt.[imtPurchaseQuantityReceived] --) AS COUNT
Steve Anderson
November 29, 2021 at 7:46 pm
Here is my answer!
SELECT pt.[imtPartID], pt.[imtTransactionType],
SUM (CASE WHEN pt.[imtTransactionType] = 1 THEN pt.[imtPurchaseQuantityReceived] ELSE 0.00000 END) AS Receipt,
SUM (CASE WHEN pt.[imtTransactionType] = 2 THEN pt.[imtPurchaseQuantityReceived] ELSE 0.00000 END) AS Issue,
SUM (CASE WHEN pt.[imtTransactionType] = 3 THEN pt.[imtPurchaseQuantityReceived] ELSE 0.00000 END) AS Adjustment
FROM [M1_SU].[dbo].[PartTransactions] pt
WHERE pt.[imtPartID] = '3001617'
AND pt.imtTransactionDate BETWEEN '5/1/2021' AND '11/30/2021'
GROUP BY pt.[imtPartID], pt.[imtTransactionType]
Steve Anderson
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply