sql server - SQL Complex Transformation -

-

i have bellow, want person move gp due change of address should have start date , end date in period. end date date less next start date. please how write query? 

declare @tab table(local_patient_identifier varchar(70),         nhs_number varchar(70), gmp varchar(70), practice_code_gp varchar(70),  cds_date date) insert @tab values ('a111111111',  '8bfd000',  'g111111',  'n77777',   '2016-05-23'), ('a111111111',  '8bfd000',  'g222222',  'n77777',   '2016-06-13'), ('a111111111',  '8bfd000',  'g222222',  'n77777',   '2016-06-13'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-02-09'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-03-06'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-03-15'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-03-29'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-05-10'), ('a111111112',  '8bfd002',  'g3333332', 'jj44444',  '2015-05-21'), ('a111111112',  '8bfd002',  'g3333332', 'kk44445',  '2016-05-02'), ('a111111112',  '8bfd002',  'g3333332', 'ww44444',  '2017-02-13') select*from @tab

expected output enter image description here

you can use row_number results:

;with cte (     select *,joinkey = row_number() over(partition local_patient_identifier order cds_date) (         select *, rown = row_number() over(partition local_patient_identifier, gmp, practice_code_gp order cds_date)              #tab         )     a.rown = 1 ) select c1.local_patient_identifier,c1.nhs_number, c1.gmp, c1.practice_code_gp, c1.cds_date startdate,      dateadd(day, -1 , c2.cds_date) enddate cte c1 left join cte c2 on c1.local_patient_identifier = c2.local_patient_identifier , c1.joinkey = c2.joinkey - 1

output below:

+--------------------------+------------+----------+------------------+------------+------------+ | local_patient_identifier | nhs_number |   gmp    | practice_code_gp | startdate  |  enddate   | +--------------------------+------------+----------+------------------+------------+------------+ | a111111111               | 8bfd000    | g111111  | n77777           | 2016-05-23 | 2016-06-12 | | a111111111               | 8bfd000    | g222222  | n77777           | 2016-06-13 | 2017-02-08 | | a111111111               | 8bfd000    | g3333333 | zz44444          | 2017-02-09 | null       | | a111111112               | 8bfd002    | g3333332 | jj44444          | 2015-05-21 | 2016-05-01 | | a111111112               | 8bfd002    | g3333332 | kk44445          | 2016-05-02 | 2017-02-12 | | a111111112               | 8bfd002    | g3333332 | ww44444          | 2017-02-13 | null       | +--------------------------+------------+----------+------------------+------------+------------+

you can use windowing function lead if using sql server >= 2012


Comments

Post a Comment

Popular posts from this blog

ios - MKAnnotationView layer is not of expected type: MKLayer -

-
Image
so code works fine logger riddled message. there way rid of or suppress it? postannotation.swift class postannotation: mkpointannotation { //mark: properties let post: post //mark: initialization init(post: post) { self.post = post super.init() self.coordinate = cllocationcoordinate2d(latitude: post.latitude, longitude: post.longitude) self.title = post.title self.subtitle = post.timestring() } } adding annotation let annotation = postannotation(post: post) self.map.addannotation(annotation) func mapview func mapview(_ mapview: mkmapview, viewfor annotation: mkannotation) -> mkannotationview? { if annotation mkuserlocation { return nil } var annotationview = mapview.dequeuereusableannotationview(withidentifier: "pin") as? mkpinannotationview if annotationview == nil { annotationview = mkpinannotationview(annotation: annotation, reuseidentifier: "pin")
Read more

ZeroMQ on Windows, with Qt Creator -

-
i have installed zmq link given below on windows 7. http://zeromq.org/distro:microsoft-windows 1) have c++ project in qt-creator , i'm using zmq c++ binding (zhelpers.hpp etc). 2) have added .lib installed zmq folder qtcreator 3) while compiling, compiler looks _imp__zmq_verion, in .dll files present in .lib have __imp_zmq_version. 4) causing undefined reference zmq_version. anyone else faced problem , has solution ?
Read more

unity3d - Unity SceneManager.LoadScene quits application -

-
i have 2 different scenes 1 named 'main' , other 'game' both added scenes in load. when use scenemanager.loadscene("game"); works no problem when tried return main scene code if (input.getkeydown (keycode.escape)) { scenemanager.loadscene("main"); } strangely game quits. thanks. sorry interruption. somewhere on other script found code. if (input.getkeydown (keycode.escape)) { application.quit (); } that's bad, know...
Read more