April 9, 2012 at 8:50 am
Here's a type of query that has always puzzled me, in fact, I've even asked SQL gurus about it.
In a loop, I'm looking at each row and want the average cost column from the history table with the greatest date less than the transaction date. So inside my loop I have...
select top 1 @a = AverageCost
from AverageCostHistory where .. bla bla bla.. and HistoryDateTime < TransactionDatetime
And, of course, this is a dreadful rebar operation having to be done once for each row being examined. So I'm trying, with little luck, to convert this into a set based operation.
Might a group by with a having clause help me? Like...
select JournalDetailpk, InventoryPK, AverageCost
from JournalDetail
join AverageCostHistory on AverageCostHistory.InventoryPK = JournalDetail.InventoryPK
group by JournalDetailPK, InventoryPK
having (max(AverageCostHistory.HistoryDateTime)<JournalDetail.DeliveredPK)
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
April 9, 2012 at 8:55 am
To really help you we will need DDL (CREATE TABLE statement) for the table(s) involved, sample data (series of INSERT INTO statements) for the table(s), expected results based onthe sample data, and the code you have currently written to try and solve the problem.
April 9, 2012 at 9:16 am
It's in a system of 436 tables.
CREATE TABLE [dbo].[JournalDetail](
[JournalDetailPK] [char](16) NOT NULL,
[JournalHeaderPK] [char](16) NULL,
[DetailType] [char](2) NULL,
[NonInventory] [tinyint] NULL,
[InventoryPK] [char](16) NULL,
[JournalDetailDescription] [varchar](30) NULL,
[Price] [money] NULL,
[CostAbsorbed] [tinyint] NULL,
[DeliveredDateTime] [datetime] NULL,
[OriginalPK] [char](16) NULL,
[BackorderPK] [char](16) NULL,
[ReceivedByPK] [char](16) NULL,
[Source] [char](2) NULL,
[ParentPK] [char](16) NULL,
[Extended] [money] NULL,
[ShipToPK] [char](16) NULL,
[KitchenPrinted] [tinyint] NULL,
[EmployeePK] [char](16) NULL,
[DrawerPK] [char](16) NULL,
[OriginalCost] [money] NULL,
[ButtonPK] [char](16) NULL,
[Increment] [int] NULL,
[DeliveredQuantity] [numeric](15, 3) NULL,
[OrderedQuantity] [numeric](15, 3) NULL,
[BackorderedQuantity] [numeric](15, 3) NULL,
[MD] [tinyint] NULL,
CONSTRAINT [PK_JournalDetail] PRIMARY KEY CLUSTERED
(
[JournalDetailPK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[InventoryCostHistory](
[InventoryCostHistoryUID] [uniqueidentifier] NOT NULL,
[InventoryPK] [char](16) NULL,
[UpdatedDateTime] [datetime] NULL,
[AverageCost] [money] NULL,
[LocationPK] [char](16) NULL,
[ReceivedQuantity] [int] NULL,
[Price] [numeric](18, 2) NULL,
CONSTRAINT [PK_InventoryCostHistory] PRIMARY KEY CLUSTERED
(
[InventoryCostHistoryUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
some rows from InventoryCostHistory:
InventoryPK Updated DateTime Average Cost
------00000014112011-03-28 09:58:02.650100.00X5 0000000001-1
------00000014112011-03-28 09:47:14.090100.00X5 00000000011062
------00000014112010-12-06 12:00:27.71020.00X5 00000000021
------00000014112010-12-03 15:44:37.79062.50X5 00000000015
------00000014112009-09-23 18:34:16.170100.00X5 0000000001-2
------00000014112009-08-28 15:18:44.760100.00X5 0000000001-8
------00000014112009-08-13 11:53:22.210100.00X5 000000000124
(This is developer's messy data sometimes designed to crash the program.)
Some data from the JournalDetail table...
InventoryPK Delivered Date Time Original Cost
------000000141125.002012-01-23 10:15:48.740100.00
------000000141125.002012-03-08 11:08:23.220100.00
------000000141125.002010-12-03 15:23:19.680100.00
------000000141125.002010-12-03 15:26:36.190100.00
------000000141125.002009-09-04 13:22:49.890100.00
------000000141125.002009-09-23 19:24:28.120100.00
------000000141125.002009-10-02 12:20:46.030100.00
------000000141125.002009-12-10 14:47:24.960100.00
------000000141125.002009-10-01 11:57:26.250100.00
------000000141125.002009-04-21 09:14:24.3100.00
------000000141125.002009-06-20 16:09:21.4400.00
------000000141125.002009-07-09 13:36:55.2900.00
------000000141125.002009-09-11 09:57:15.790100.00
I don't like putting columns like "original Cost" in a table because of the likelyhood if the value being wrong. It's there as I couldn't figure an effective way to look it up in the history table. Now, of course, a user has their average costs goofed up and went 1 1/2 years without noticing. Rebuilding the history table isn't a real problem, but updating thousands and thousands of journaldetails.... Well, I'd like to get rid of the rebar operation.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
April 9, 2012 at 9:25 am
Need the sample data in a format that is readily consumable. A series of INSERT INTO statments fits that bill.
April 9, 2012 at 4:52 pm
Lynn Pettis (4/9/2012)
Need the sample data in a format that is readily consumable. A series of INSERT INTO statments fits that bill.
+1
@Half Bubble
SSMS Tools Pack can help.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 9, 2012 at 6:24 pm
Or, you can just write a little bit of code to do the same thing. Please see the following article.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply