Pivot Query
-
Hello:
I am trying to get a count of columns for values within a given date range. I am using this example, but unable to get results.
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017
There are three transaction types, Receipt (value of 1), Issue (Value of 2) and Adjustment (Value of 3). How many are valued at 1, 2 and 3 in respective columns is the goal, at least for now. Ultimately, I am looking to divide this out by Month as well, so that within the given date range, how many of each type are in each month?
This is what I have so far. Any help would be greatly appreciated.
SELECT TOP (1000000)
[PartTransactions].[imtPartID]
,[1] AS Receipt
,[2] AS Issue
,[3] AS Adjustment
FROM
(SELECT [M1_SU].[dbo].[PartTransactions].[imtTransactionDate], [M1_SU].[dbo].[PartTransactions].[imtTransactionType], [M1_SU].[dbo].[PartTransactions].[imtPartID]
FROM [M1_SU].[dbo].[PartTransactions]) P
PIVOT
COUNT ([M1_SU].[dbo].[PartTransactions].[imtTransactionDate])
FOR impPartID IN
( [1], [2], [3] )
) AS pvt
WHERE [imtPartID] = '7000917' AND imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
ORDER BY pvt.impTransactionDate DESCSteve Anderson
-
What results do you get when you run that?
-
Steve Anderson
-
I added a pic. Unfortunately, lots of red underlines means I cannot get results.
Steve Anderson
-
I can't see that your query will work?
You are missing a bracket after PIVOT.
If you could supply your input data and the output expected it would be easier to understand.
-
I think this might be what you want:
SELECT pt.[imtPartID],
COUNT(CASE WHEN pt.[imtPartID] = 1 THEN 'x' ELSE NULL END) AS Receipt,
COUNT(CASE WHEN pt.[imtPartID] = 2 THEN 'x' ELSE NULL END) AS Issue,
COUNT(CASE WHEN pt.[imtPartID] = 3 THEN 'x' ELSE NULL 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]; -
This is good, except that all the values are zero. I know for certain there are nine records with valid data here. Am I supposed to do something with the 'x'?
Thank you!
Steve Anderson
-
the 'x' was just there so it's included in the count. You could put anything that's not null.
This is also the equivalent:
SELECT pt.[imtPartID],
SUM(CASE WHEN pt.[imtPartID] = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.[imtPartID] = 2 THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pt.[imtPartID] = 3 THEN 1 ELSE 0 END) AS Adjustment
FROM [M1_SU].[dbo].[PartTransactions] pt
WHERE pt.[imtPartID] = '7000917'
AND pt.imtTransactionDate BETWEEN '20190501 15:13:52' AND '20212910 15:13:52'
GROUP BY pt.[imtPartID];Unless you supply some data there is no way for me to see why it is not working.
-
Jonathan, Thanks again!
SELECT TOP (1000000) [PartTransactions].[imtTransactionDate]
,[PartTransactions].[imtTransactionType]
,[PartTransactions].[imtPartID]
FROM [M1_SU].[dbo].[PartTransactions]
WHERE [imtPartID] = '7000917' AND imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
ORDER BY imtTransactionDate DESCOk, here's some data for the above query:
Steve Anderson
-
I think this does it.
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] = '7000917'
AND pt.imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
GROUP BY pt.[imtPartID];Steve Anderson
-
You've been here long enough to know about
a) Putting T-SQL code in a code block and
b) Providing data in a consumable format (which can be pasted into SSMS)
-
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?
Steve Anderson
-
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.
Steve Anderson
- stephen.aa wrote:
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.
Here is a random post which does both things:
https://www.sqlservercentral.com/forums/topic/datediff-with-getweek-worth-of-data#post-3942774
Click on Insert/edit code sample to format your code.
-
a) Putting T-SQL code in a code block
b) Providing data in a consumable format (which can be pasted into SSMS)
This just means (again using code sample control) copying & pasting SQL statements that insert data into the tables in the DDL statements -- e.g.,
INSERT INTO [M1_SU].[dbo].[PartTransactions]
(imtTransactionDate, imtTransactionType, imtPartType)
VALUES ('2020-12,31 19:00:09',3,7000917),
('2020-09-29 12:05:25',1,700917),
('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),
('2019-09-07 17:16:44',3,7000917);
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply