Ok, this is crazy. I had this thread Splitting Ordered Groups into Ordered Subgroups and got a great solution from sunitabeck.So I modified it to apply it to the real world table (which has oodles of other fields) and a strange thing happened. When I try to restrict the columns on the select statement it does goofy things. When I don't restrict the columns it works.Unfortunately I cannot give example data because when I restrict it to just a few groups that are causing the problem it works and there are over 20000 records in the actual data.Note it fails on groups that have "Multi Dosages" for a given event. These groups will have more than one record with the same order number. In my original thread I threw in a couple of groups that had multi dosages just in case this could cause a problem. The solution sunitabeck gave worked perfectly on the sample data.Anyway this query works:SELECT t1.* , ROW_NUMBER() OVER (ORDER BY groupid,ordernumber)- ROW_NUMBER() OVER (PARTITION BY MedicationNameId,groupId,COALESCE(N,0) ORDER BY groupid,ordernumber) AS SubGroup--into ISC_ODE_SplitChainGroupsFROM v_ISC_OrderDataExpanded t1 OUTER APPLY ( SELECT TOP 1 OrderNumber AS N FROM v_ISC_OrderDataExpanded t2 WHERE t2.GroupId = t1.GroupId AND t2.OrderNumber < t1.OrderNumber AND t2.MedicationNameId <> t1.MedicationNameId ORDER BY t2.orderNumber DESC ) t2ORDER BY groupid, subgroup,ordernumber;
And gives me this (not showing every column):GroupId OrderNumber event NumDosages MedicationNameId ODE_Id SubGroup------- ----------- ---------- ---------- ---------------- ------ --------8615 ... 1 ... Active ... 2 ... 3721 ... 24138 67778615 ... 1 ... Active ... 2 ... 3721 ... 24139 67778615 ... 2 ... Change ... 2 ... 53 ... 24136 67798615 ... 2 ... Change ... 2 ... 53 ... 24137 67798615 ... 3 ... Reorder ... 2 ... 53 ... 24134 67798615 ... 3 ... Reorder ... 2 ... 53 ... 24135 67798615 ... 4 ... Reorder ... 2 ... 53 ... 24130 67798615 ... 4 ... Reorder ... 2 ... 53 ... 24132 67798615 ... 5 ... Discontinu ... 2 ... 53 ... 24133 67798615 ... 5 ... Discontinu ... 2 ... 53 ... 24131 6779
Note:- Records in correct order (by OrderNumber)
- Two SubGroups (because MedicationNameId only changes once)
This does not work:SELECT t1.ODE_Id , t1.GroupId , t1.OrderNumber , t1.event , t1.NumDosages , t1.MedicationNameId , ROW_NUMBER() OVER (ORDER BY groupid,ordernumber)- ROW_NUMBER() OVER (PARTITION BY MedicationNameId,groupId,COALESCE(N,0) ORDER BY groupid,ordernumber) AS SubGroupFROM v_ISC_OrderDataExpanded t1 OUTER APPLY ( SELECT TOP 1 OrderNumber AS N FROM v_ISC_OrderDataExpanded t2 WHERE t2.GroupId = t1.GroupId AND t2.OrderNumber < t1.OrderNumber AND t2.MedicationNameId <> t1.MedicationNameId ORDER BY t2.orderNumber DESC ) t2ORDER BY groupid, subgroup,ordernumber;
And gives me this:ODE_Id GroupId OrderNumber event NumDosages MedicationNameId SubGroup----------- ----------- ----------- ---------- ----------- ---------------- --------------------24138 8615 1 Active 2 3721 677624139 8615 1 Active 2 3721 677824136 8615 2 Change 2 53 677824132 8615 4 Reorder 2 53 677824135 8615 3 Reorder 2 53 677924134 8615 3 Reorder 2 53 677924131 8615 5 Discontinu 2 53 677924133 8615 5 Discontinu 2 53 677924137 8615 2 Change 2 53 678024130 8615 4 Reorder 2 53 6780
Note:- Records are not in correct order (by OrderNumber)
- Four SubGroups (but MedicationNameId only changes once)
If I put a where clause like this (to restrict it to a couple problem groups and a few groups that work): where t1.GroupId in (10, 8615, 14159,10231,10228,10220,223,100)
Then it works with or without restricting the columns in the select clause.So does anybody have any idea what could be causing the problem? I am not even sure what to look for. I don't know if I should be concerned as the only reason I was restricting the columns in the first place was for ease in checking the results. It was sheer luck that I found the problem. I initially copied sunitabeck's solution and modified to pull against the actual table and restrict the columns. When the query didn't work after I spot checked some groups, I recopied the original query, remodified it to go against the actual table, but didn't bother to restrict the columns. It then looked like it worked so I assumed I had done something wrong the first time I did the modifications. So I then restricted the columns and IT DID NOT WORK.So here I am and I don't like unsolved mysteries. Any insights somebody could give would be most welcome.Thanks, LaurieHere is the actual table description (again giving example data won't help):CREATE TABLE [dbo].[ISC_ODE_NotProcessed]( [MyIdentity] [int] NULL, [GroupId] [int] NULL, [MyGroupId] [int] NULL, [OrigGroupid] [int] NULL, [MyEvent] [varchar](10) NULL, [OCEvent] [varchar](10) NULL, [RxEvent] [varchar](54) NULL, [OrderNumber] [int] NULL, [RxHistoryId] [int] NULL, [Dosage_Combined] [varchar](330) NULL, [rx_dosage_id] [int] NULL, [NDC_Code] [varchar](12) NULL, [orderhistory_id] [int] NULL, [event] [varchar](10) NULL, [rx_ID] [int] NULL, [srcrx_id] [int] NULL, [NumDosages] [int] NULL, [ClientId] [varchar](50) NULL, [OriginalDrug_Name] [varchar](87) NULL, [MedicationNameId] [int] NULL, [MedicationId] [int] NULL, [StrengthId] [int] NULL, [Quantity] [real] NULL, [Unit] [int] NULL, [Schedule] [int] NULL, [MedicationStartDate] [datetime] NULL, [MedicationEndDate] [datetime] NULL, [DateStamp] [datetime] NULL, [DateStamp_Last] [datetime] NULL, [Pharmacy] [varchar](105) NULL, [Pharmacy_ID] [int] NULL, [Refill_Auth] [real] NULL, [Duration] [smallint] NULL, [Diagnosis_ID] [int] NULL, [DrugPurpose] [varchar](255) NULL, [DSMCode] [varchar](10) NULL, [DSMNumber] [int] NULL, [SLStaffid] [int] NULL, [PrescriberName] [varchar](100) NULL, [Special_Inst] [varchar](537) NULL, [DAW] [varchar](1) NULL, [titrationid] [int] NULL, [Step] [smallint] NULL, [TitrationComment] [varchar](100) NULL, [GroupTitration] [bit] NULL, [Pills] [smallint] NULL, [GroupBadDiag] [bit] NULL, [GroupBadDrug] [bit] NULL, [GroupBadUnits] [bit] NULL, [GroupBadRecord] [bit] NULL, [GroupMissingProv] [bit] NULL, [GroupMultiDosages] [bit] NULL, [GroupBadNDCCode] [bit] NULL, [ODE_Id] [int] IDENTITY(1,1) NOT NULL,) ON [PRIMARY]
Columns selected changes results huh?
Posted - 2012-08-20 : 13:35:05
