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 DESC

     

    • This topic was modified 3 weeks, 2 days ago by  stephen.aa.

    Steve Anderson

  • What results do you get when you run that?

    • This reply was modified 3 weeks, 2 days ago by  Phil Parkin.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Pivot Query

    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 DESC

    Ok, here's some data for the above query:

    Select Query

     

     

    • This reply was modified 3 weeks, 2 days ago by  stephen.aa.

    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];

    • This reply was modified 3 weeks, 2 days ago by  stephen.aa.

    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)

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • 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.

     

     

    • This reply was modified 3 weeks, 2 days ago by  stephen.aa.

    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.

    • This reply was modified 3 weeks, 2 days ago by  Phil Parkin.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • a) Putting T-SQL code in a code block

    InsertCodeSample

    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 25 total)

You must be logged in to reply to this topic. Login to reply