November 3, 2021 at 4:44 pm
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 DESC
Steve Anderson
November 3, 2021 at 4:55 pm
What results do you get when you run that?
November 3, 2021 at 5:22 pm
Steve Anderson
November 3, 2021 at 5:24 pm
I added a pic. Unfortunately, lots of red underlines means I cannot get results.
Steve Anderson
November 3, 2021 at 5:35 pm
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.
November 3, 2021 at 5:38 pm
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];
November 3, 2021 at 5:47 pm
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
November 3, 2021 at 5:52 pm
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.
November 3, 2021 at 5:59 pm
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 DESC
Ok, here's some data for the above query:
Steve Anderson
November 3, 2021 at 6:02 pm
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
November 3, 2021 at 6:05 pm
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)
November 3, 2021 at 6:17 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?
Steve Anderson
November 3, 2021 at 6:19 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.
Steve Anderson
November 3, 2021 at 6:30 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.
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.
November 3, 2021 at 6:39 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy