sql - Group by unlike values -

-

i'm trying create report shows line counts based on report type. i'm grouping on report type, report types have specific names want combined under 1 listing.

for example: urgent care reports can have type of urgent care, sd494, sd510, sd546 , multiple others. sd### show under urgent care rather separate listing. same op notes, can op note, sd805, polysomnography, etc. under op notes, in 1 report. have 20 different reports can run individually each report type.

how can accomplish task?

declare @officeid int = 93; declare @startdate datetime = '6/01/2015'; declare @enddate datetime = '07/01/2015';  select (r.reporttype),   sum(cast(round(r.transcriptionlinecount,2) decimal(8,2))) "bill lines",  (cast(round(sum(r.transcriptionlinecount) * .13,2) decimal(8,2))) "amount" rptlinecountinfo r join dictation d on d.dictationid = r.dictationid  d.officeid = @officeid  , r.finishedtime between @startdate , @enddate , (d.dictationstatus != 'd' , d.dictationstatus != 'q') group r.reporttype

here results: (i've added numbering column isn't in paragraph form)

  1. asc pre-op consult 1848.96 240.36
  2. audiology report 4403.73 572.48
  3. cardiac cath 452.26 58.79
  4. colon 539 op note 117.49 15.27
  5. colonoscopy - op note 118.11 15.35
  6. colonoscopy-op note 54.88 7.13
  7. consult 3219.01 418.47
  8. cpap titration 1802.61 234.34
  9. discharge summary 5259.79 683.77
  10. egd - op note 316.66 41.17
  11. egd 537 op note 178.71 23.23
  12. egd-op note 194.75 25.32
  13. electrophysiology report 1679.79 218.37
  14. er admit 39402.45 5122.32
  15. er format 150642.53 19583.53
  16. ercp-op note 108.88 14.15
  17. hospital course 5344.91 694.84
  18. multidisciplinary oncology 272.38 35.41
  19. op note 32437.7 4216.9
  20. op note - sd698 57.66 7.5
  21. pain clinic letter 32.71 4.25
  22. pain clinic report 6.71 0.87
  23. pain clinic sd1020 37.75 4.91
  24. php discharge summary 336.81 43.79
  25. pm inpatient consultation 1602.96 208.38
  26. polysomnography 2466.16 320.6
  27. pre-op consult 30812.45 4005.62
  28. sd288 initial injury 6398.55 831.81
  29. sd289 follow 2893.45 376.15
  30. sd289 follow-up 2498.36 324.79
  31. sd488 op note 48.86 6.35
  32. sd496 - urgent care 32.34 4.2
  33. sd503 - urgent care 40.17 5.22
  34. sd504 - urgent care 40.54 5.27
  35. sd509 - urgent care 75.95 9.87
  36. sd541 - urgent care 193.14 25.11
  37. sd594 - urgent care 42.89 5.58
  38. sd606 - urgent care 43.31 5.63
  39. sleep - mslt 85.53 11.12
  40. sleep letter 4505.21 585.68
  41. sleep report 25243.47 3281.65
  42. speech report 357.73 46.5
  43. stat discharge summary 1148.06 149.25
  44. surgical hospitalist clinic note 1961.71 255.02
  45. urgent care 30221.61 3928.81
  46. urgent care - sd494 227.37 29.56
  47. urgent care - sd496 37.17 4.83
  48. urgent care - sd503 202.07 26.27
  49. urgent care - sd504 117.74 15.31
  50. urgent care - sd508 67.97 8.84
  51. urgent care - sd509 60.1 7.81
  52. urgent care - sd510 33.57 4.36
  53. urgent care - sd541 47.4 6.16
  54. urgent care - sd546 27.77 3.61
  55. urgent care - sd594 40.17 5.22

in databases (you didn't specify yours), use case statement group values way want them, , use same case statement in both select , group by clauses.

to started:

select case when r.reporttype 'sd%' 'urgent care'             when r.reporttype in ('op note','sd805','polysomnography') 'op notes'             else r.reporttype end reporttype, sum(cast(round(r.transcriptionlinecount,2) decimal(8,2))) "bill lines",  (cast(round(sum(r.transcriptionlinecount) * .13,2) decimal(8,2))) "amount" rptlinecountinfo r join dictation d on d.dictationid = r.dictationid  d.officeid = @officeid  , r.finishedtime between @startdate , @enddate , (d.dictationstatus != 'd' , d.dictationstatus != 'q') group case when r.reporttype 'sd%' 'urgent care'               when r.reporttype in ('op note','sd805','polysomnography') 'op notes'               else r.reporttype end

i should warn above logic in case statement broken. reason there inconsistency in requirement.

on 1 hand, report types follow pattern sd### should mapped urgent care. sd805? want 1 mapped op notes, it's valid urgent care per specification. you'll have figure want in case. if have 1 exception, maybe need reorder conditions exceptional case checked first.

also, represent sd### requirement, translated standard like 'sd%', may match patterns sd5, or sd, or sddfgdfg. if not want, appropriate regex statement database, , change that.


Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more