Inactive Cards

[jwiggleston]

I am having trouble with this one. I use it for displaying multiple cards I have in our system, but I need it to show for "total Inactive Cards" we have. So how can I edit this from showing all multiple active cards to just showing all inactive cards? I appreciate anyone's help.


SELECT DISTINCT t3.*, t4.cardNum
FROM (SELECT id, lastname, firstname, empnum, count(cardnumber)
FROM (SELECT DISTINCT t1.id, lastname, firstname, empnum, cardnumber
FROM (SELECT t1.id, t1.lastname, t1.firstname, t1.empnum, t1.cardnumber, t1.location, iif(t1.cardnumber in (select id from tempbadgenumbers), 'Temp - ' + conemp, conemp)
AS type, company, t1.sponsor, t1.activedatetime, t1.expirationdatetime, t2.readergroupname, iif(t1.readername is null, t2.readername, t1.readername)
AS access
FROM (SELECT d.personaldataflatid
AS id, f.lastname, f.firstname, e.cardnumber, d.personaldata1
AS company, d.personaldata2
AS empNum, d.personaldata3
AS conemp, d.personaldata4
AS sponsor, d.personaldata5
AS location, b.readergroupname, b.readername, e.activedatetime, e.expirationdatetime
FROM (((dbo_cardholderaccessxrtable AS a
RIGHT JOIN dbo_viewaccessrights AS b
ON a.cardholderaccessxrid = b.cardholderaccessxrid)
LEFT JOIN dbo_personaldataflattable AS d
ON a.cardid = d.cardid)
LEFT JOIN dbo_viewcardinfotable AS e
ON e.cardid = a.cardid)
LEFT JOIN dbo_cardholdertable AS f
ON f.cardid = a.cardid WHERE e.inactive = false) AS t1
LEFT JOIN (SELECT i.readergroupname, g.readername
FROM (dbo_readertable AS g
LEFT JOIN dbo_readergroupxrtable AS h
ON h.readerid = g.readerid)
LEFT JOIN dbo_readergrouptable AS I
ON i.readergroupid = h.readergroupid) AS t2
ON t1.readergroupname = t2.readergroupname) AS [%$##@_Alias]) AS [%$##@_Alias]
GROUP BY id, lastname, firstname, empnum
HAVING count(cardnumber) > 1) AS t3
LEFT JOIN (SELECT d.personaldataflatid AS id, e.cardnumber AS cardnum
FROM (dbo_cardholderaccessxrtable AS a
LEFT JOIN dbo_personaldataflattable AS d ON a.cardid = d.cardid)
LEFT JOIN dbo_viewcardinfotable AS e ON e.cardid = a.cardid
WHERE e.inactive = false) AS t4 ON t3.id = t4.id;

 

[MarkK]

I'm sure there's a savant somewhere who can look at that SQL and tell you exactly what's wrong in 10 seconds or less, but until that person posts a tip, I'd suggest working with something that complicated in a design grid, and not directly in text.

And if it was me, I would never let a query get that out of control. Rather than continuously subquerying new tables in one enormous SQL statement, rather, I would write a series of shorter, clearer queries, each one serving as an input table into the next one, and build up the complexity that way. Then each query can be more aptly named, and the purpose of each incremental step can be more clearly understood more quickly.

Code, be it SQL or VBA or whatever, is more valuable if it's more serviceable, and to achieve that I recommend you build stuff in smaller simpler chunks.

Hope this helps,