Post reply

How to find positive and negative numbers in a single column of data

  • October 19, 2018 at 11:56 am

    #366035

    See attached code. Sorry about the double space in the code. I don't know how to eliminate it. This data has been masked so, the identifiers don't point to anyone specifically. I need to find patients that have both a positive balance and a negative balance. PatientID identifies them.

    create table #T

    (

    VisitID varchar(30),

    PatientID varchar(30),

    PatientBalance money

    )

    insert into #T(VisitID, PatientID, PatientBalance) values('42637924','43800497',1741.94)

    insert into #T(VisitID, PatientID, PatientBalance) values('01050126','11349041',605.31)

    insert into #T(VisitID, PatientID, PatientBalance) values('82230964','54013147',19490.77)

    insert into #T(VisitID, PatientID, PatientBalance) values('91809062','85755883',127.42)

    insert into #T(VisitID, PatientID, PatientBalance) values('92813851','85755883',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('10858914','85755883',372.58)

    insert into #T(VisitID, PatientID, PatientBalance) values('92323363','04206568',125)

    insert into #T(VisitID, PatientID, PatientBalance) values('00641203','12712668',-14.45)

    insert into #T(VisitID, PatientID, PatientBalance) values('62256413','44439610',97.03)

    insert into #T(VisitID, PatientID, PatientBalance) values('52320982','44439610',302.97)

    insert into #T(VisitID, PatientID, PatientBalance) values('75145208','94504107',39.46)

    insert into #T(VisitID, PatientID, PatientBalance) values('82929719','94504107',215.9)

    insert into #T(VisitID, PatientID, PatientBalance) values('93651529','93651443',132)

    insert into #T(VisitID, PatientID, PatientBalance) values('85557470','82642251',97.61)

    insert into #T(VisitID, PatientID, PatientBalance) values('94358625','21104768',213.03)

    insert into #T(VisitID, PatientID, PatientBalance) values('11555963','44539813',119.58)

    insert into #T(VisitID, PatientID, PatientBalance) values('85647823','35729134',994.48)

    insert into #T(VisitID, PatientID, PatientBalance) values('70916865','14303197',150)

    insert into #T(VisitID, PatientID, PatientBalance) values('13647326','82836785',20.21)

    insert into #T(VisitID, PatientID, PatientBalance) values('52033419','10100341',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('34014488','13119024',703.01)

    insert into #T(VisitID, PatientID, PatientBalance) values('72824222','13119024',456.19)

    insert into #T(VisitID, PatientID, PatientBalance) values('62926828','13119024',505.78)

    insert into #T(VisitID, PatientID, PatientBalance) values('95306764','20917629',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('91950570','20917629',14)

    insert into #T(VisitID, PatientID, PatientBalance) values('53811420','00133397',210.74)

    insert into #T(VisitID, PatientID, PatientBalance) values('61823349','00133397',25.94)

    insert into #T(VisitID, PatientID, PatientBalance) values('31946172','22406939',120)

    insert into #T(VisitID, PatientID, PatientBalance) values('75102912','22406939',320)

    insert into #T(VisitID, PatientID, PatientBalance) values('30413075','22406939',60)

    insert into #T(VisitID, PatientID, PatientBalance) values('61022421','22406939',40)

    insert into #T(VisitID, PatientID, PatientBalance) values('91121387','22406939',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('75336261','94643492',10.61)

    insert into #T(VisitID, PatientID, PatientBalance) values('73002246','64358667',162.4)

    insert into #T(VisitID, PatientID, PatientBalance) values('23605877','40111471',2382.75)

    insert into #T(VisitID, PatientID, PatientBalance) values('60329608','54440876',40.12)

    insert into #T(VisitID, PatientID, PatientBalance) values('44215434','93949950',31.51)

    insert into #T(VisitID, PatientID, PatientBalance) values('12659172','20820328',198.05)

    insert into #T(VisitID, PatientID, PatientBalance) values('03638395','21431029',24.07)

    insert into #T(VisitID, PatientID, PatientBalance) values('83220943','21431029',24.07)

    insert into #T(VisitID, PatientID, PatientBalance) values('02741810','01210030',47.27)

    insert into #T(VisitID, PatientID, PatientBalance) values('03424192','01210030',150)

    insert into #T(VisitID, PatientID, PatientBalance) values('82020006','13007105',193.56)

    insert into #T(VisitID, PatientID, PatientBalance) values('54009927','45431288',94.63)

    insert into #T(VisitID, PatientID, PatientBalance) values('24407000','94556251',30)

    insert into #T(VisitID, PatientID, PatientBalance) values('40406248','12640613',215.86)

    insert into #T(VisitID, PatientID, PatientBalance) values('55912558','54001448',1156.56)

    insert into #T(VisitID, PatientID, PatientBalance) values('51035929','43500671',-97)

    insert into #T(VisitID, PatientID, PatientBalance) values('15704445','50525624',19.37)

    insert into #T(VisitID, PatientID, PatientBalance) values('10300742','33031149',3090.06)

    insert into #T(VisitID, PatientID, PatientBalance) values('25701676','84537239',326.23)

    insert into #T(VisitID, PatientID, PatientBalance) values('22814125','81729678',414.29)

    insert into #T(VisitID, PatientID, PatientBalance) values('12655783','81729678',414.29)

    insert into #T(VisitID, PatientID, PatientBalance) values('02344320','22005951',151440.99)

    insert into #T(VisitID, PatientID, PatientBalance) values('60002277','85633094',25)

    insert into #T(VisitID, PatientID, PatientBalance) values('45352985','80240127',58.87)

    insert into #T(VisitID, PatientID, PatientBalance) values('75408337','95351020',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('70403794','83816147',300)

    insert into #T(VisitID, PatientID, PatientBalance) values('75322182','92641110',4602.47)

    insert into #T(VisitID, PatientID, PatientBalance) values('64438739','83426438',40)

    insert into #T(VisitID, PatientID, PatientBalance) values('32812717','12722791',78.85)

    insert into #T(VisitID, PatientID, PatientBalance) values('04611218','05900136',-17.22)

    insert into #T(VisitID, PatientID, PatientBalance) values('60256977','33312124',251.9)

    insert into #T(VisitID, PatientID, PatientBalance) values('62914452','33312124',243.69)

    insert into #T(VisitID, PatientID, PatientBalance) values('15506608','15440710',1426.24)

    insert into #T(VisitID, PatientID, PatientBalance) values('53501995','53837295',550.97)

    insert into #T(VisitID, PatientID, PatientBalance) values('55336125','53837295',499.53)

    insert into #T(VisitID, PatientID, PatientBalance) values('92003219','92944829',4668.41)

    insert into #T(VisitID, PatientID, PatientBalance) values('55856725','41242212',157.51)

    insert into #T(VisitID, PatientID, PatientBalance) values('00724303','45611950',628.82)

    insert into #T(VisitID, PatientID, PatientBalance) values('35437192','13545427',-43)

    insert into #T(VisitID, PatientID, PatientBalance) values('35939293','13545427',5.34)

    insert into #T(VisitID, PatientID, PatientBalance) values('05721915','13545427',-75.36)

    insert into #T(VisitID, PatientID, PatientBalance) values('52758175','13545427',52.12)

    insert into #T(VisitID, PatientID, PatientBalance) values('44306844','02136307',96.28)

    insert into #T(VisitID, PatientID, PatientBalance) values('15738693','02136307',667.76)

    insert into #T(VisitID, PatientID, PatientBalance) values('85707059','92256982',53.78)

    insert into #T(VisitID, PatientID, PatientBalance) values('55558555','51452296',62.33)

    insert into #T(VisitID, PatientID, PatientBalance) values('05458089','72657165',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('52235430','04659679',-223.41)

    insert into #T(VisitID, PatientID, PatientBalance) values('92753092','13101872',275.9)

    insert into #T(VisitID, PatientID, PatientBalance) values('15519021','22134547',99.24)

    insert into #T(VisitID, PatientID, PatientBalance) values('84039935','22134547',202.18)

    insert into #T(VisitID, PatientID, PatientBalance) values('21211176','22134547',56.63)

    insert into #T(VisitID, PatientID, PatientBalance) values('61251666','22134547',95.12)

    insert into #T(VisitID, PatientID, PatientBalance) values('61959636','22134547',541.68)

    insert into #T(VisitID, PatientID, PatientBalance) values('82130180','62710593',-20)

    insert into #T(VisitID, PatientID, PatientBalance) values('33854334','22857237',150)

    insert into #T(VisitID, PatientID, PatientBalance) values('33953979','04628203',111.05)

    insert into #T(VisitID, PatientID, PatientBalance) values('22945273','32942670',50)

    insert into #T(VisitID, PatientID, PatientBalance) values('11705647','52213675',1829.94)

    insert into #T(VisitID, PatientID, PatientBalance) values('53824362','14006073',150)

    insert into #T(VisitID, PatientID, PatientBalance) values('22610256','14006073',150)

    insert into #T(VisitID, PatientID, PatientBalance) values('10927209','25753206',50)

    insert into #T(VisitID, PatientID, PatientBalance) values('32625619','32625572',1043.64)

    insert into #T(VisitID, PatientID, PatientBalance) values('13517290','95809213',-26.9)

    insert into #T(VisitID, PatientID, PatientBalance) values('01209556','51209197',113.67)

    insert into #T(VisitID, PatientID, PatientBalance) values('81116914','13659950',1449.01)

    insert into #T(VisitID, PatientID, PatientBalance) values('83655154','21911048',35)

    insert into #T(VisitID, PatientID, PatientBalance) values('40136557','21911048',35)

    insert into #T(VisitID, PatientID, PatientBalance) values('60925332','92953451',73.48)

    insert into #T(VisitID, PatientID, PatientBalance) values('00411064','30803512',217.14)

    insert into #T(VisitID, PatientID, PatientBalance) values('91537696','02823006',1110.68)

    insert into #T(VisitID, PatientID, PatientBalance) values('21348125','44329551',18091.19)

    insert into #T(VisitID, PatientID, PatientBalance) values('10915090','90215439',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('40259599','65322862',275)

    insert into #T(VisitID, PatientID, PatientBalance) values('05928415','24511216',244.05)

    insert into #T(VisitID, PatientID, PatientBalance) values('01847298','24511216',526.41)

    insert into #T(VisitID, PatientID, PatientBalance) values('05658188','61209166',803)

    insert into #T(VisitID, PatientID, PatientBalance) values('42125254','92652000',149.14)

    insert into #T(VisitID, PatientID, PatientBalance) values('35925062','22444927',14.7)

    insert into #T(VisitID, PatientID, PatientBalance) values('44436304','22444927',26.9)

    insert into #T(VisitID, PatientID, PatientBalance) values('15840360','82700306',467.43)

    insert into #T(VisitID, PatientID, PatientBalance) values('04425772','04425710',1058.84)

    insert into #T(VisitID, PatientID, PatientBalance) values('20931256','62929460',2041.66)

    insert into #T(VisitID, PatientID, PatientBalance) values('80556168','80556121',834)

    insert into #T(VisitID, PatientID, PatientBalance) values('13758460','55313577',50)

    insert into #T(VisitID, PatientID, PatientBalance) values('81853736','01338677',252.3)

    insert into #T(VisitID, PatientID, PatientBalance) values('45510902','23913691',75)

    insert into #T(VisitID, PatientID, PatientBalance) values('60003112','11408413',2177.55)

    insert into #T(VisitID, PatientID, PatientBalance) values('95055972','95055940',-56.37)

    insert into #T(VisitID, PatientID, PatientBalance) values('84354792','73241901',170)

    insert into #T(VisitID, PatientID, PatientBalance) values('93620784','04418399',324.37)

    insert into #T(VisitID, PatientID, PatientBalance) values('52624063','04418399',15.6)

    insert into #T(VisitID, PatientID, PatientBalance) values('03539836','04418399',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('25027843','04418399',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('21341926','04418399',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('45212693','81027684',183.17)

    insert into #T(VisitID, PatientID, PatientBalance) values('64301236','81027684',165.68)

    insert into #T(VisitID, PatientID, PatientBalance) values('03206613','03037529',380)

    insert into #T(VisitID, PatientID, PatientBalance) values('81145988','24102392',187.2)

    insert into #T(VisitID, PatientID, PatientBalance) values('63508261','03811506',147.88)

    insert into #T(VisitID, PatientID, PatientBalance) values('95215346','04323581',33.91)

    insert into #T(VisitID, PatientID, PatientBalance) values('01351133','04323581',303.36)

    insert into #T(VisitID, PatientID, PatientBalance) values('20508077','04323581',139.68)

    insert into #T(VisitID, PatientID, PatientBalance) values('55718481','04244331',251.26)

    insert into #T(VisitID, PatientID, PatientBalance) values('94401191','04244331',152.85)

    insert into #T(VisitID, PatientID, PatientBalance) values('94446561','02829259',241.76)

    insert into #T(VisitID, PatientID, PatientBalance) values('32826432','23830136',5042.31)

    insert into #T(VisitID, PatientID, PatientBalance) values('44701765','52829981',76.42)

    insert into #T(VisitID, PatientID, PatientBalance) values('24632900','85124741',108.69)

    insert into #T(VisitID, PatientID, PatientBalance) values('40817152','72833876',358.26)

    insert into #T(VisitID, PatientID, PatientBalance) values('61328372','72833876',32.26)

    insert into #T(VisitID, PatientID, PatientBalance) values('24804580','14609403',1006.03)

    insert into #T(VisitID, PatientID, PatientBalance) values('25115321','82421794',123.7)

    insert into #T(VisitID, PatientID, PatientBalance) values('30525702','82421794',115.87)

    insert into #T(VisitID, PatientID, PatientBalance) values('15552133','04001829',6123.5)

    insert into #T(VisitID, PatientID, PatientBalance) values('15647535','05911373',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('94651374','05510791',22.06)

    insert into #T(VisitID, PatientID, PatientBalance) values('24725629','45155350',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('45709074','03034883',183.2)

    insert into #T(VisitID, PatientID, PatientBalance) values('05842399','71512027',132)

    insert into #T(VisitID, PatientID, PatientBalance) values('05250314','73347454',25.46)

    insert into #T(VisitID, PatientID, PatientBalance) values('14730252','05442261',36.28)

    insert into #T(VisitID, PatientID, PatientBalance) values('52401573','05442261',141.86)

    insert into #T(VisitID, PatientID, PatientBalance) values('85650826','85331575',173.17)

    insert into #T(VisitID, PatientID, PatientBalance) values('41413938','85459945',83.34)

    insert into #T(VisitID, PatientID, PatientBalance) values('32247883','03134102',180)

    insert into #T(VisitID, PatientID, PatientBalance) values('60103453','60103406',-57.6)

    insert into #T(VisitID, PatientID, PatientBalance) values('33408179','73616235',50)

    insert into #T(VisitID, PatientID, PatientBalance) values('95406083','92406811',-25)

    insert into #T(VisitID, PatientID, PatientBalance) values('83746483','82027452',260.22)

    insert into #T(VisitID, PatientID, PatientBalance) values('33207273','82027452',201.16)

    insert into #T(VisitID, PatientID, PatientBalance) values('22659993','85836979',8785.74)

    insert into #T(VisitID, PatientID, PatientBalance) values('95226943','95226896',-27.29)

    insert into #T(VisitID, PatientID, PatientBalance) values('33005515','24646442',341.55)

    insert into #T(VisitID, PatientID, PatientBalance) values('63248340','30818145',30)

    insert into #T(VisitID, PatientID, PatientBalance) values('05544701','45208664',20)

    insert into #T(VisitID, PatientID, PatientBalance) values('94145997','45208664',11.8)

    insert into #T(VisitID, PatientID, PatientBalance) values('40931459','94538643',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('25813298','94538643',362.74)

    insert into #T(VisitID, PatientID, PatientBalance) values('84422639','70630655',101.79)

    insert into #T(VisitID, PatientID, PatientBalance) values('22033074','85912046',599.37)

    insert into #T(VisitID, PatientID, PatientBalance) values('43424891','10044464',52.79)

    insert into #T(VisitID, PatientID, PatientBalance) values('62325978','10044464',19.74)

    insert into #T(VisitID, PatientID, PatientBalance) values('83733681','10358857',75.4)

    insert into #T(VisitID, PatientID, PatientBalance) values('84311728','10358857',176.56)

    insert into #T(VisitID, PatientID, PatientBalance) values('24506138','04728183',30)

    insert into #T(VisitID, PatientID, PatientBalance) values('55006201','13514050',131.79)

    insert into #T(VisitID, PatientID, PatientBalance) values('40503336','33626756',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('83922889','10539485',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('01713744','61126368',-14.39)

    insert into #T(VisitID, PatientID, PatientBalance) values('34331692','82943361',39)

    insert into #T(VisitID, PatientID, PatientBalance) values('02923483','00839563',47.14)

    insert into #T(VisitID, PatientID, PatientBalance) values('70715806','70715759',-350)

    insert into #T(VisitID, PatientID, PatientBalance) values('81821610','52349723',-100)

    insert into #T(VisitID, PatientID, PatientBalance) values('95508453','54005434',200)

    insert into #T(VisitID, PatientID, PatientBalance) values('32606691','55311256',1572.15)

    insert into #T(VisitID, PatientID, PatientBalance) values('21108503','70022679',1502.68)

    insert into #T(VisitID, PatientID, PatientBalance) values('00407533','33546344',75)

    insert into #T(VisitID, PatientID, PatientBalance) values('23313114','10627232',37.6)

    insert into #T(VisitID, PatientID, PatientBalance) values('30408252','44353455',108.2)

    insert into #T(VisitID, PatientID, PatientBalance) values('95848055','55125543',130.06)

    insert into #T(VisitID, PatientID, PatientBalance) values('03233417','03233370',406.77)

    insert into #T(VisitID, PatientID, PatientBalance) values('03153343','03606985',387.41)

    insert into #T(VisitID, PatientID, PatientBalance) values('03219343','75622656',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('82225527','75622656',100)

    insert into #T(VisitID, PatientID, PatientBalance) values('52334990','91356682',55.11)

    insert into #T(VisitID, PatientID, PatientBalance) values('90738626','91356682',78.18)

    insert into #T(VisitID, PatientID, PatientBalance) values('14706677','02601193',107.76)

    insert into #T(VisitID, PatientID, PatientBalance) values('42148617','04425070',1171.62)

    insert into #T(VisitID, PatientID, PatientBalance) values('34857076','04425070',183.2)

    insert into #T(VisitID, PatientID, PatientBalance) values('93226625','03401267',1429.34)

    insert into #T(VisitID, PatientID, PatientBalance) values('24724942','61851307',90)

    insert into #T(VisitID, PatientID, PatientBalance) values('62405981','60009196',111.05)

    insert into #T(VisitID, PatientID, PatientBalance) values('75818305','11326133',1449.01)

    insert into #T(VisitID, PatientID, PatientBalance) values('11650259','63034562',75)

    insert into #T(VisitID, PatientID, PatientBalance) values('83350141','65757546',4105.24)

    insert into #T(VisitID, PatientID, PatientBalance) values('40524907','84956957',275)

    insert into #T(VisitID, PatientID, PatientBalance) values('24047555','84956957',346.46)

    insert into #T(VisitID, PatientID, PatientBalance) values('15356411','05703302',1138.5)

    insert into #T(VisitID, PatientID, PatientBalance) values('40632242','05703302',-14)

    insert into #T(VisitID, PatientID, PatientBalance) values('35834857','05703302',-14)

    insert into #T(VisitID, PatientID, PatientBalance) values('32130417','40933171',1462.98)

    insert into #T(VisitID, PatientID, PatientBalance) values('50909624','40933171',1449.01)

    insert into #T(VisitID, PatientID, PatientBalance) values('64805941','00039511',75)

    insert into #T(VisitID, PatientID, PatientBalance) values('61556617','12123494',32.26)

    insert into #T(VisitID, PatientID, PatientBalance) values('91918917','12322496',-10)

    insert into #T(VisitID, PatientID, PatientBalance) values('94012184','00825952',150)

    insert into #T(VisitID, PatientID, PatientBalance) values('94521828','95225201',20)

    insert into #T(VisitID, PatientID, PatientBalance) values('02824541','34819121',-26.91)

    insert into #T(VisitID, PatientID, PatientBalance) values('94442355','84325256',-199.8)

    insert into #T(VisitID, PatientID, PatientBalance) values('23129374','84325256',-28.5)

    insert into #T(VisitID, PatientID, PatientBalance) values('41234824','44234232',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('45014185','44234232',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('61347625','63658985',-25)

    insert into #T(VisitID, PatientID, PatientBalance) values('84027756','82257420',-195.03)

    insert into #T(VisitID, PatientID, PatientBalance) values('64132953','70000686',235)

    insert into #T(VisitID, PatientID, PatientBalance) values('23649463','23649385',200)

    insert into #T(VisitID, PatientID, PatientBalance) values('73729121','00155371',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('63546850','41545963',200)

    insert into #T(VisitID, PatientID, PatientBalance) values('05104951','41545963',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('10209515','41545963',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('14058670','84636978',6033.58)

    insert into #T(VisitID, PatientID, PatientBalance) values('53004451','32733934',-116.54)

    insert into #T(VisitID, PatientID, PatientBalance) values('75746088','84646600',29.13)

    insert into #T(VisitID, PatientID, PatientBalance) values('84903826','01349993',70.32)

    insert into #T(VisitID, PatientID, PatientBalance) values('42446057','05524673',2878.03)

    insert into #T(VisitID, PatientID, PatientBalance) values('52203661','82641599',303.36)

    insert into #T(VisitID, PatientID, PatientBalance) values('71744800','51723398',250)

    insert into #T(VisitID, PatientID, PatientBalance) values('25758379','61425020',271.94)

    insert into #T(VisitID, PatientID, PatientBalance) values('02315639','00422041',1688.42)

    insert into #T(VisitID, PatientID, PatientBalance) values('63352563','63352500',-100)

    insert into #T(VisitID, PatientID, PatientBalance) values('23847306','75644026',600)

    insert into #T(VisitID, PatientID, PatientBalance) values('12734215','71532472',2590.01)

    insert into #T(VisitID, PatientID, PatientBalance) values('71706293','85620476',158)

    insert into #T(VisitID, PatientID, PatientBalance) values('82933784','55601245',53.15)

    insert into #T(VisitID, PatientID, PatientBalance) values('82843878','82842534',-221.82)

    insert into #T(VisitID, PatientID, PatientBalance) values('01030006','10446586',50)

    insert into #T(VisitID, PatientID, PatientBalance) values('11520976','10446586',50)

    insert into #T(VisitID, PatientID, PatientBalance) values('32541384','95157590',308.51)

    insert into #T(VisitID, PatientID, PatientBalance) values('05520472','95157590',390.89)

    insert into #T(VisitID, PatientID, PatientBalance) values('63020446','95954750',8762.42)

    insert into #T(VisitID, PatientID, PatientBalance) values('55718264','11945243',9.45)

    insert into #T(VisitID, PatientID, PatientBalance) values('05803869','90556014',5690.89)

    insert into #T(VisitID, PatientID, PatientBalance) values('11205570','60552512',10.88)

    insert into #T(VisitID, PatientID, PatientBalance) values('44156707','60703403',700)

    insert into #T(VisitID, PatientID, PatientBalance) values('15541611','75035332',41.38)

    insert into #T(VisitID, PatientID, PatientBalance) values('11113694','75035332',44.03)

    insert into #T(VisitID, PatientID, PatientBalance) values('11432278','15036444',10)

    insert into #T(VisitID, PatientID, PatientBalance) values('20003543','12530104',8.14)

    insert into #T(VisitID, PatientID, PatientBalance) values('91100141','12530104',41.65)

    insert into #T(VisitID, PatientID, PatientBalance) values('85342719','12530104',32.43)

    insert into #T(VisitID, PatientID, PatientBalance) values('55430579','05850065',231.81)

    insert into #T(VisitID, PatientID, PatientBalance) values('82530338','83552422',442.21)

    insert into #T(VisitID, PatientID, PatientBalance) values('51655667','51655408',-219.8)

    insert into #T(VisitID, PatientID, PatientBalance) values('65541024','53359494',269.26)

    insert into #T(VisitID, PatientID, PatientBalance) values('64428336','94559307',199.17)

    insert into #T(VisitID, PatientID, PatientBalance) values('10728134','10728087',-1067.09)

    insert into #T(VisitID, PatientID, PatientBalance) values('01115067','83825607',2552.32)

    insert into #T(VisitID, PatientID, PatientBalance) values('91337835','04040944',413.39)

    insert into #T(VisitID, PatientID, PatientBalance) values('30552457','81956359',1572.21)

    insert into #T(VisitID, PatientID, PatientBalance) values('33702323','03756840',14.7)

    insert into #T(VisitID, PatientID, PatientBalance) values('22341147','03756840',1364.14)

    insert into #T(VisitID, PatientID, PatientBalance) values('44633108','03756840',59.79)

    insert into #T(VisitID, PatientID, PatientBalance) values('72150396','24252770',-6.09)

    insert into #T(VisitID, PatientID, PatientBalance) values('35837363','80047060',2438.72)

    insert into #T(VisitID, PatientID, PatientBalance) values('44158431','23312560',1071.65)

    insert into #T(VisitID, PatientID, PatientBalance) values('50007358','23312560',5.6)

    insert into #T(VisitID, PatientID, PatientBalance) values('00617864','23312560',17.59)

    insert into #T(VisitID, PatientID, PatientBalance) values('90326355','23312560',222.1)

    insert into #T(VisitID, PatientID, PatientBalance) values('12155612','23312560',-75)

    insert into #T(VisitID, PatientID, PatientBalance) values('82705981','23312560',182.39)

  • October 19, 2018 at 12:15 pm

    #2010463

    SELECT PatientID, COUNT(DISTINCT SIGN(PatientBalance))
    FROM #t
    WHERE PatientBalance <> 0
    GROUP BY PatientID
    HAVING COUNT(DISTINCT SIGN(PatientBalance)) > 1

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • October 19, 2018 at 12:18 pm

    #2010465

    Here's another method.


    SELECT PatientID
    FROM #t
    WHERE PatientBalance <> 0
    GROUP BY PatientID
    HAVING MAX(PatientBalance) > 0
        AND MIN(PatientBalance) < 0

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • October 19, 2018 at 12:21 pm

    #2010468

    Wonderful. Thanx

  • October 26, 2018 at 10:24 am

    #2011378

    Never even heard of SIGN(). Cool. 🙂

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply