Tag Info

New answers tagged

1

Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user. CREATE VIEW user_permissions_deep AS SELECT DISTINCT user_id, permission_id FROM ( SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role FROM ( WITH ...


1

Simple approach (can split sets) Chain DELETE and INSERT with a data-modifying CTE for best performance and to be safe: WITH del AS ( DELETE FROM tbl t WHERE udate < (now() - interval '1 year')::date AND EXISTS ( -- is not the greatest SELECT 1 FROM tbl WHERE compid = t.compid AND seq > t.seq RETURNING ...


3

A CTE can't be used as a subquery. One workaround would be: IF EXISTS ( SELECT 1 FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn FROM table ) AS DATA WHERE rn = 2 ) BEGIN ... END Another would be: IF EXISTS (SELECT 1 FROM dbo.table GROUP BY column HAVING COUNT(*) > 1) BEGIN ... END Even if your ...


4

add cast(CallStartTime as DATE) in your GROUP BY: ;with grp (Sdate,TransactionType,tot) as (select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type], (count ([Transaction_type])) as [Total Count] from TBL_AGENT_TRANSACTIONS where cast(CallStartTime as DATE)>='2015-04-27' and cast(CallEndTime as Date)<='2015-04-27' and ...



Top 50 recent answers are included