Complex case statement within select statement

Transact-SQL
1

I have a table that contains thousands of records but only some of those records match a certain criteria. I then need to extract these records that match the criteria and update an external system with a ‘Y’ flag by way of a user category.

My temporary table contains only the records that match the required criteria and all of those records in the temporary table then update in the external system with a ‘Y’ flag.

My problem is that these records change on a daily basis and I have a situation where old records, that were flagged as ‘Y’ previously, do not meet the criteria anymore and should now update to ‘N’. The old records do not update in the external system to ‘N’ as the last record received was a ‘Y’ some time ago.

Currently I use the following SQL query to identify the applicable records and these records then feeds into the temporary table:

declare @Dummyfundsonly table (iss_id varchar(20))
insert into
  @Dummyfundsonly
select
  iss_id AS iss_id
from
  infoportal..position_dg
where
  as_of_tms >= cast(getdate() - 1 as date)
group by
  iss_id
having
  count(*) = count(
    case when acct_id in ('FUTUR', 'ZOTCDUM') then 1 end
  )

Once I have the records from above mentioned sql these records (iss_id’s) update the sValue with a ‘Y’ flag for a certain user category as follows:

select
  distinct iss_id as sSecurityID,
  '654' as icategory,
  'Y' as sValue,
  cast(
    dateadd(
      hh,
      01,(
        select
          convert(char(10), getdate(), 120)
      )
    ) as datetime
  ) as dtPoint
from
  @Dummyfundsonly

One suggestion was to rather insert all the records from the main table into the temporary table and not just the ones matching the criteria and then to make use of a case statement to identify the applicable records in the temporary table that needs to update as sValue to ‘Y’ and the rest must be ‘N’.

I am struggling to incorporate my initial SQL query (to identify the unique records only) into the case statement for the sValue. Any suggestions to get this right?

CASE when ? ? ? then 'Y' else 'N' end as sValue
2

Thousands of records should not be a problem for SQL Server. How long does it take to populate the @Dummyfundsonly table? If performance is acceptable, you could replace the temp table for a view. The results from the view will never go stale.

DROP VIEW IF EXISTS dbo.V_Dummyfundsonly;
GO

CREATE VIEW dbo.V_Dummyfundsonly
AS
WITH Dummyfundsonly
AS (
	SELECT iss_id AS iss_id
	FROM infoportal..position_dg
	WHERE as_of_tms >= cast(getdate() - 1 AS DATE)
	GROUP BY iss_id
	HAVING count(*) = count(CASE WHEN acct_id IN ('FUTUR', 'ZOTCDUM') THEN 1 ELSE 0 END)
	)
SELECT iss_id AS sSecurityID
	,'654' AS icategory
	,'Y' AS sValue
	,dateadd(hh, 01, getdate()) AS dtPoint
FROM Dummyfundsonly
3

Here is an alternative, where the view will return a 'Y' or 'N' weather it is a Dummyfundsonly or not.

DROP VIEW IF EXISTS dbo.V_Dummyfundsonly;
GO

CREATE VIEW dbo.V_Dummyfundsonly
AS
WITH Dummyfundsonly
AS (
	SELECT iss_id, CASE WHEN count(*) = count(CASE WHEN acct_id IN ('FUTUR', 'ZOTCDUM') THEN 1 ELSE 0 END) THEN 'Y' ELSE 'N' END AS sValue
	FROM infoportal..position_dg
	WHERE as_of_tms >= cast(getdate() - 1 AS DATE)
	GROUP BY iss_id
	)
SELECT iss_id AS sSecurityID
	, '654' AS icategory
	, sValue
	, dateadd(hh, 01, getdate()) AS dtPoint
FROM Dummyfundsonly
4

Hi

One idea is
Trigger on source table

Whenever change update destination table

5

After what change would you update the destination table?

6

Hi Wim

What I meant

Was any changes ..
Like change in column values

7

Hi Harish,

If you have a look at the SQL script, you'll notice a GROUP BY clause.

  • The trigger could put a lock on the position_dg table.
  • The result has to be written to another table. This could result in a deadlock.

Both reasons are a red flag to me to not go with a trigger solution.

8

sounds good Wim

thanks for pointing it out !!!

guess we will have to look for other OPTIONS !!!
:slight_smile: