SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Columns selected changes results huh?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LaurieCox
Posting Yak Master

USA
143 Posts

Posted - 08/20/2012 :  13:35:05  Show Profile  Reply with Quote
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_SplitChainGroups
FROM
	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 
	) t2
ORDER 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   6777
8615    ...  1            ...   Active     ...  2          ... 3721           ...  24139   6777
8615    ...  2            ...   Change     ...  2          ... 53             ...  24136   6779
8615    ...  2            ...   Change     ...  2          ... 53             ...  24137   6779
8615    ...  3            ...   Reorder    ...  2          ... 53             ...  24134   6779
8615    ...  3            ...   Reorder    ...  2          ... 53             ...  24135   6779
8615    ...  4            ...   Reorder    ...  2          ... 53             ...  24130   6779
8615    ...  4            ...   Reorder    ...  2          ... 53             ...  24132   6779
8615    ...  5            ...   Discontinu ...  2          ... 53             ...  24133   6779
8615    ...  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 SubGroup
FROM
	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 
	) t2
ORDER BY
	groupid, subgroup,ordernumber;

And gives me this:

ODE_Id      GroupId     OrderNumber event      NumDosages  MedicationNameId SubGroup
----------- ----------- ----------- ---------- ----------- ---------------- --------------------
24138       8615        1           Active     2           3721             6776
24139       8615        1           Active     2           3721             6778
24136       8615        2           Change     2           53               6778
24132       8615        4           Reorder    2           53               6778
24135       8615        3           Reorder    2           53               6779
24134       8615        3           Reorder    2           53               6779
24131       8615        5           Discontinu 2           53               6779
24133       8615        5           Discontinu 2           53               6779
24137       8615        2           Change     2           53               6780
24130       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,

Laurie

Here 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]

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 08/20/2012 :  16:55:24  Show Profile  Reply with Quote
sorry for the stupid question - I didn't bother trying to understand everything yet but...By "not working" do you just mean the results are not ordered by ordernumber? If so can't you just change the ORDER BY:
ORDER BY groupid, ordernumber, subgroup


Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000