Click here to Skip to main content
14,882,317 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys, I have a requirement to get primary key of table based on condition in group by query.

And priority is based on submissionType column and it's ("Correction" then "Internal Complaint" and then submission). I have to get primary key of last submitted primary key based on sybmissionType priority.

Here is data

Id    SubmissionDate    SubmissionType      Amount     PaidAmount   Unique_Id
1      2020-01-04       Submission          1166.00    562.00       test123
2      2020-06-01       Submission         -1166.00   -562.00       test123
3      2020-06-01       Correction          1166.00    579.00       test123
4      2020-11-02       Internal Complaint   587.00      0.00       test123


Expected Answer

Count    PaidAmount    LastId
4        579.00        3


What I have tried:

SQL
Select Count(Id), Sum(PaidAmount), Max(Case When Amount <=0 then 0 else Id end) from table Group by unique_Id


Result i am getting

Count    PaidAmount    LastId
4        579.00        4
Posted
Updated 24-Mar-21 11:42am

1 solution

What I've done:
USE [cpqaAnswers]
GO

CREATE TABLE [cpqaAnswers].[cpqa].[tbl_SK_typSubmit_01](
	[Id] INT,
		[SubmissionDate] DATE,
			[SubmissionType] NVARCHAR(256),
				[Amount] DECIMAL(10,2),
					[PaidAmount] DECIMAL(10,2),
						[Unique_Id] NVARCHAR(27)
					)

USE [cpqaAnswers]
GO

BULK INSERT [cpqaAnswers].[cpqa].[tbl_sk_typSubmit_01]
	FROM 'C:\Users\ks\data.txt'
data.txt content:
/*

Id    SubmissionDate    SubmissionType      Amount     PaidAmount   Unique_Id
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1      2020-01-04       Submission          1166.00    562.00       test123
2      2020-06-01       Submission         -1166.00   -562.00       test123
3      2020-06-01       Correction          1166.00    579.00       test123
4      2020-11-02       Internal Complaint   587.00      0.00       test123

*/
Check ...

USE [cpqaAnswers]
GO

SELECT [Id]
      ,[SubmissionDate]
      ,[Amount]
      ,[PaidAmount]
      ,[Unique_Id]
  FROM [cpqa].[tbl_sk_typSubmit_01]
GO
Id	SubmissionDate	Amount	PaidAmount	Unique_Id
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1	2020-01-04	    1166.00	562.00	    test123
2	2020-06-01	   -1166.00	-562.00	    test123
3	2020-06-01	    1166.00	579.00	    test123
4	2020-11-02	    587.00	0.00	    test123

Poster's query:
USE [cpqaAnswers]
GO

Select Count(Id) As [Count], Sum(PaidAmount) As [PaidAmount], Max(Case When Amount <=0 then 0 else Id end) As [LastId]
	from [cpqa].[tbl_sk_typSubmit_01] 
		Group by unique_Id
What I get as a return:
Count	PaidAmount	LastId
~~~~~~~~~~~~~~~~~~~~~~~~~~
4	    579.00	    4 
And although you state that the desired output under [LastId] is "3", it never will be "3", it will always be the last row of data [LastId] because the conditional "<=0 THEN 0 ELSE Id END" (as you can see in the data) IS NEVER "0"; the last row "4".

So you've got youself a Type II error in you reasoning.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900