How to write a case statement to for a new column based on the date and username columns

Hi,

Below is the SQL Query i am currently having

SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
	   FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate
  FROM [ClaimsOperations].[Stg].[IG_FinancialTransactionSummary]
  inner join [dbo].[FinancialTransactionSummaryTest]				ON IG_FinancialTransactionSummary.ClaimNum = FinancialTransactionSummaryTest.ClaimNum
where 
  ----Username like '%data%' and
   FinancialTransactionSummaryTest.ClaimNum IN (2000074,2000257)
   order by IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionDate

And here is the result dataset

ClaimNum TransactionDate Username ClaimNum TransactionAmount UserName 2000074 20150209 jerry.witt 2000074 -10000 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 20150626 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL

ClaimNum TransactionDate Username ClaimNum TransactionAmount UserName 2000257 20150130 jevans 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 20150626 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 20150721 jevans 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 20150721 jevans 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL

So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.


September 2nd, 2015 2:02pm

Hi Naomi,

Thanks for the formatting, do you have any idea on how to solve this please?

Thanks

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 2:59pm

I only glanced over this thread. If you want help, I suggest to post the create table (or declare @t table ) statements with some data as insert statements and desired output with clear explanation of conditions. Then it will be much easier to understand your requirements.
September 2nd, 2015 3:22pm

Hi Naomi,

Below is the table structure

CREATE TABLE [dbo].[FinancialTransactionSummaryTest](
	[Sort] [nvarchar](255) NULL,
	[DATA CHECK] [bit] NULL,
	[FINANCIALTRANSACTIONID] [float] NULL,
	[CLAIMNUM] [float] NULL,
	[PAYMENTID] [nvarchar](255) NULL,
	[PAYMENTLINEID] [nvarchar](255) NULL,
	[RESERVEID] [float] NULL,
	[TRANSACTIONCODE] [float] NULL,
	[TRANSACTIONAMOUNT] [float] NULL,
	[USERNAME] [nvarchar](255) NULL,
	[TRANSACTIONDATE] [float] NULL
	
) ON [PRIMARY]


CREATE TABLE [Stg].[IG_FinancialTransactionSummary](
	[TimeStamp] [timestamp] NOT NULL,
	[TransactionID] [int] NOT NULL,
	[ClaimNum] [int] NOT NULL,
	[PaymentID] [int] NULL,
	[PaymentLineID] [int] NULL,
	[ReserveID] [int] NULL,
	[TransactionCode] [int] NULL,
	[TransactionAmount] [money] NULL,
	[TransactionDate] [int] NULL,
	[Username] [nvarchar](50) NULL
 CONSTRAINT [PK_IG_FinancialTransactionSummary] PRIMARY KEY CLUSTERED 
(
	[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

You can put any dummy values in the insert if you want, and the output i am looking for is a column which would basically be a yes or no column and the

yes or depends on:-

1)   If the IG_FinancialTransactionSummary.Username is like '%data%' and the IG_FinancialTransactionSummary.Transaction date is  max date then YES.2) If the IG_FinancialTransactionSummary.Username is like '%data%' and the IG_FinancialTransactionSummary.Transaction date is between somedates but there should be no other transactions with username like '%data%' then YES 3) else NO

  • Edited by SqlDev12 11 hours 45 minutes ago
Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 3:27pm

This is not clear:

So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.

Can you post just 4 columns you need as output to be clear:

ClaimNo, TransactionDate, UserName, NewColumn

E.g. do you need something like

case when TransactionDate = max(TransactionDate) over (partition by ClaimNo) and UserName like '%Data%' then 'Yes' else 'No' as NewColumn

September 2nd, 2015 3:35pm

ClaimNum	TransactionDate	Username	                               ClaimNum	    TransactionAmount	UserName					
ClaimNum	TransactionDate	Username					ClaimNum	TransactionAmount	UserName NewColumn
2000257		 20150130	jevans						2000257			2547.76		DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL No
2000257		 20150626	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	2000257			2547.76		DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL No
2000257		 20150721	DATAFIX INSERTED ON 20150626 AT 162152493  2000257			2547.76		DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL No

2000074 20150209 jerry.witt 2000074 -10000 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL Yes 2000074 20150626 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL Yes

If you look at the claim number 2000257 its a NO because there are more than 1 usernames with Datafix and if we look at 2000074 its a yes because there is only 1 datafix user name. So my requirement is the datafix username can be more than once for each claim but it shouldnt be on different transaction dates. if there is more than 1 datafix username on same transaction date or if there is just 1 datafix with transactiondate its fine else its a NO.

Please let me know if i am still unclear.

Thanks

  • Edited by SqlDev12 11 hours 27 minutes ago
Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 3:47pm

case when count(case when UserName like '%DataFix%' then 1 end) over(partition by ClaimNo, TransactionDate) > 1 then 'No' else 'Yes' end

---------------

However, in your sample the TransactionDate is actually different for the ClaimNum but it's the same in the text of the UserName. If you need to parse the transaction date out of the text, then it will be more complex problem.

September 2nd, 2015 4:14pm

/***** Script for SelectTopNRows command from SSMS  ******/
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
	   FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionDate,FinancialTransactionSummaryTest.TransactionAmount,FinancialTransactionSummaryTest.UserName,
	   case when count(case when IG_FinancialTransactionSummary.Username like '%DataFix%' then 1 end) over(partition by IG_FinancialTransactionSummary.ClaimNum, IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.UserName) > 1 then 'No' else 'Yes' end
	 -- , case when IG_FinancialTransactionSummary.Username like '%data%' and TransactionDate = 
  FROM [ClaimsOperations].[Stg].[IG_FinancialTransactionSummary]
  inner join [dbo].[FinancialTransactionSummaryTest]				ON IG_FinancialTransactionSummary.ClaimNum = FinancialTransactionSummaryTest.ClaimNum
where 
  ----Username like '%data%' and 2000257
   FinancialTransactionSummaryTest.ClaimNum IN (2000259)
   order by IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionDate

For the query above below is the result set, i am not sure why it has both yes and no for one claim,instead it should just be YES as the username like '%DATA%' is just for one transactionDate?

ClaimNum	TransactionDate	Username	ClaimNum	TransactionDate	TransactionAmount	UserName	(No column name)
2000259	20150121	jwise	2000259	20150626	-125	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	Yes
2000259	20150121	jwise	2000259	20150626	-125	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	Yes
2000259	20150121	jwise	2000259	20150626	-2750	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	Yes
2000259	20150121	jwise	2000259	20150626	-2750	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	Yes
2000259	20150626	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	2000259	20150626	-2750	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	No
2000259	20150626	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	2000259	20150626	-2750	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	No
2000259	20150626	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	2000259	20150626	-125	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	No
2000259	20150626	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	2000259	20150626	-125	DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL	No

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 4:28pm

Why did you add partition by UserName into your count statement? 
September 2nd, 2015 4:32pm

Before that it was the same so i thought may be paritioning by username makes a difference and it didnt.
Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 4:33pm

Your conditions are confusing. The query I suggested counts number of rows with DataFix in the UserName per Claim Num and per Transaction Date. So, if we have 2 rows with 'DataFix' for the same Claim No and same Transaction Date, is it yes or no? And if we have one more UserName with DataFix for the same claim but different transaction date, what should now be the output of that column?

In any case, it may be not the trivial problem and one count may not be enough.

September 2nd, 2015 4:45pm

Sorry for the confusion ,Let me clear it

If we have 1 or more rows with the 'DataFix' for same claimnumber and same transactiondate it should be a yes.

if we have rows with 'Datafix' for same claimnumber and different transaction date it should be a NO.

Please let me know if its still unclear.

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 4:48pm

I think you need then

CASE WHEN COALESCE(MAX(case when UserName LIKE '%DataFix%' Then TransactionDate end) over (partition by ClaimNo),'19000101')

<> COALESCE(MIN(case when UserName LIKE '%DataFix%' then TransactionDate end) over (partition by ClaimNo), '19000101') then 'No' ELSE 'Yes' end

September 2nd, 2015 4:58pm

I think this is what i am looking for, Thanks a lot for your help, i just sent it to the person who asked for it and i am sure this is it but if i need anything else, i will let you know.

Once again thanks a lot for your help.

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 5:07pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics