February 28, 2014 at 2:43 pm
Give this a try:
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#history') IS NOT NULL
DROP TABLE #history;
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date DATETIME,
OldLocationID INT NULL,
NewLocationID INT
);
-- insert sample data
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13);
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#results','U') IS NOT NULL
DROP TABLE #results;
-- create table for sample output
CREATE TABLE #results
(
InventoryItemID INT,
LocationID INT,
DaysInLocation INT
);
-- create sample data
INSERT INTO #results (InventoryItemID, LocationID, DaysInLocation)
VALUES
(1,10,41),
(1,13,9);
SELECT * FROM #history;
SELECT * FROM #results;
with basedata as (
select
rn = ROW_NUMBER() over (partition by InventoryItemID order by ActivityCodeID, [Date]),
InventoryItemID,
ActivityCodeID,
[Date],
OldLocationID,
NewLocationID
from
#history
)
select
bd1.InventoryItemID,
bd1.OldLocationID,
sum(datediff(day,bd2.[Date],bd1.[Date])) as DaysInLocation
from
basedata bd1
inner join basedata bd2
on bd1.InventoryItemID = bd2.InventoryItemID
and bd1.rn = bd2.rn + 1
group by
bd1.InventoryItemID,
bd1.OldLocationID;
February 28, 2014 at 2:44 pm
This should work.
with HistoryWithDates as
(
select InventoryItemID, NewLocationID, [DATE] as MyDate, ROW_NUMBER() over (partition by InventoryItemID order by [Date]) as RowNum
from #history
group by [DATE], InventoryItemID, NewLocationID
)
select h1.InventoryItemID, h1.NewLocationID as LocationID, SUM(DATEDIFF(DAY, h1.MyDate, h2.MyDate)) as DaysInLocation
from HistoryWithDates h1
join HistoryWithDates h2 on h1.RowNum = h2.RowNum - 1 and h1.InventoryItemID = h2.InventoryItemID
group by h1.InventoryItemID, h1.NewLocationID
order by InventoryItemID, LocationID
This will even work if you have additional InventoryItemID values.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 2:45 pm
It seems that Lynn posted a solution very similar to mine. 😉 Nicely done sir!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 2:46 pm
EricEyster (2/28/2014)
Try this out:create table inventory (
InvID int,
ActivityCode int,
MovementDate datetime,
OldLocationID int,
NewLocationID int
)
select I.InvID, I.ActivityCode, I.OldLocationID ,
sum(datediff(day,I.inDateTime, I.OutDateTime))
from (
select I.InvID, I.ActivityCode, I.OldLocationID, I.NewLocationID, I.MovementDate as inDateTime, Min(I2.MovementDate) as OutDateTime
from Inventory I
join Inventory I2
on I.InvID = I2.InvID
and I.MovementDate < I2.MovementDate
group by I.InvID, I.ActivityCode, I.OldLocationID, I.NewLocationID, I.MovementDate
--order by I.InvID, I.ActivityCode, I.MovementDate
)as I
group by I.InvID, I.ActivityCode, I.OldLocationID
Be careful here. You have a triangular join in there. Those can be hidden really nasty performance problems.
Hidden RBAR: Triangular Joins[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 2:46 pm
Thanks for the recommendations guys/gals, I really appreciate it! I'm headed home and I'm working on a migration project over the weekend. I'll try to report back on your recommendations ASAP, but it is very possible that it may not be until later tonight or Monday.
February 28, 2014 at 2:51 pm
Lynn Pettis (2/28/2014)
Give this a try:
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#history') IS NOT NULL
DROP TABLE #history;
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date DATETIME,
OldLocationID INT NULL,
NewLocationID INT
);
-- insert sample data
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13);
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#results','U') IS NOT NULL
DROP TABLE #results;
-- create table for sample output
CREATE TABLE #results
(
InventoryItemID INT,
LocationID INT,
DaysInLocation INT
);
-- create sample data
INSERT INTO #results (InventoryItemID, LocationID, DaysInLocation)
VALUES
(1,10,41),
(1,13,9);
SELECT * FROM #history;
SELECT * FROM #results;
with basedata as (
select
rn = ROW_NUMBER() over (partition by InventoryItemID order by ActivityCodeID, [Date]),
InventoryItemID,
ActivityCodeID,
[Date],
OldLocationID,
NewLocationID
from
#history
)
select
bd1.InventoryItemID,
bd1.OldLocationID,
sum(datediff(day,bd2.[Date],bd1.[Date])) as DaysInLocation
from
basedata bd1
inner join basedata bd2
on bd1.InventoryItemID = bd2.InventoryItemID
and bd1.rn = bd2.rn + 1
group by
bd1.InventoryItemID,
bd1.OldLocationID;
At first glance this looks awesome. I admittedly haven't ventured much into CTEs, but no better time to learn than the present. Thanks for this! Once I figure out what its doing and can verify some live-data (non-production of course) results I'll report back. 🙂
Edit: Spelling correction
February 28, 2014 at 10:18 pm
I believe that you shouldn't stop counting days just because of the latest location. I believe that you need to consider how long something has been at the latest location according to GETDATE(). Using Sean's fine code as the basis, which is pretty much the way I would have done this, I changed the JOIN to a LEFT JOIN and added an ISNULL to include/count the number of days since 2014-02-20, as well.
with HistoryWithDates as
(
select InventoryItemID, NewLocationID, [DATE] as MyDate, ROW_NUMBER() over (partition by InventoryItemID order by [Date]) as RowNum
from #history
group by [DATE], InventoryItemID, NewLocationID
)
select h1.InventoryItemID, h1.NewLocationID as LocationID, SUM(DATEDIFF(DAY, h1.MyDate, [font="Arial Black"]ISNULL([/font]h2.MyDate[font="Arial Black"],GETDATE())[/font])) as DaysInLocation
from HistoryWithDates h1
[font="Arial Black"]LEFT[/font] JOIN HistoryWithDates h2 on h1.RowNum = h2.RowNum - 1 and h1.InventoryItemID = h2.InventoryItemID
group by h1.InventoryItemID, h1.NewLocationID
order by InventoryItemID, LocationID
Here's the result set for the given test data.
InventoryItemID LocationID DaysInLocation
--------------- ----------- --------------
1 10 41
1 13 18
--Jeff Moden
March 1, 2014 at 9:21 am
After reading Jeff's post, just a couple of minor changes and my code returns the same results.
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#history') IS NOT NULL
DROP TABLE #history;
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date DATETIME,
OldLocationID INT NULL,
NewLocationID INT
);
-- insert sample data
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13);
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#results','U') IS NOT NULL
DROP TABLE #results;
-- create table for sample output
CREATE TABLE #results
(
InventoryItemID INT,
LocationID INT,
DaysInLocation INT
);
-- create sample data
INSERT INTO #results (InventoryItemID, LocationID, DaysInLocation)
VALUES
(1,10,41),
(1,13,9);
SELECT * FROM #history;
SELECT * FROM #results;
with basedata as (
select
rn = ROW_NUMBER() over (partition by InventoryItemID order by ActivityCodeID, [Date]),
InventoryItemID,
ActivityCodeID,
[Date],
OldLocationID,
NewLocationID
from
#history
)
select
bd1.InventoryItemID,
bd1.NewLocationID,
sum(datediff(day,bd1.[Date],isnull(bd2.[Date],getdate()))) as DaysInLocation
from
basedata bd1
left outer join basedata bd2
on bd1.InventoryItemID = bd2.InventoryItemID
and bd1.rn = bd2.rn - 1
group by
bd1.InventoryItemID,
bd1.NewLocationID;
March 3, 2014 at 10:11 am
Sean Lange (2/28/2014)
JoshDBGuy (2/28/2014)
Do you have a best practice for cursors? Just don't use them at all or only in certain situations? I tend to use them for small sets of data, such as a couple hundred or a thousand rows. Otherwise I generally use while loops.Edit: Interesting. Makes me curious to see code response to this request.
A while loop can very easily perform far worse than a properly defined cursor. I would avoid either construct as a general rule. Just because your dataset is small when you write the code doesn't mean the dataset will always be small.
The basic issue is that you have to change your mindset. Instead of thinking about what you want to do to a row, you have to think about what you want to do to a column. If the body of your loop is an insert, update or similar it can be rewritten as a set based query with no loops.
Looping is very helpful for some administration types of things or sending emails. Things where it is more involved than just modifying some data in some tables.
FYI: My definition of a cursor is any code with a WHILE loop.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 3, 2014 at 3:30 pm
Sorry to have vanished. My migration project over the weekend went LOOOONNNNNGGGG and work today was insane. I'll be looking at this tomorrow and will post back with what I find. Thanks again to everyone for their input! 🙂
March 5, 2014 at 2:11 pm
Here's one way of doing it based on the data you sent. Some things may change if my assumptions are not correct.
Also, I tried to use as many CTE as I could to show the steps - you can probably optimize the query or consolidate some of the CTE into a single one.
IF OBJECT_ID('TempDB..#history','U') IS NOT NULL
DROP TABLE #history
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date1 DATETIME,
OldLocationID INT NULL,
NewLocationID INT
)
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date1, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13)
; With LocOrder As(
select *, ROW_NUMBER () OVER(PARTITION BY InventoryItemId ORDER BY ISNULL(Date1, '1/1/1800')) RowN
from #history h
), LocEffDate AS(
select LO.InventoryItemId, LO.Date1 EffectiveFromDate, LO1.Date1 EffectiveToDate, LO1.OldLocationId LocId
from LocOrder LO
inner join LocOrder LO1 on LO.RowN = LO1.RowN - 1 and LO.InventoryItemID = LO1.InventoryItemID
), LocDays AS(
select InventoryItemID, LocId, DATEDIFF(dd, effectivefromdate, effectivetodate) DaysAtLocation
from LocEffDate
), LocTotalDays AS(
SELECT InventoryItemId, LocId, SUM(DaysAtLocation) TotalDays
FROM LocDays
GROUP BY InventoryItemId, LocId)
select * from LocTotalDays
drop table #history
March 5, 2014 at 2:12 pm
Did not realize there are more pages and similar queries have already been posted. Please ignore.
March 5, 2014 at 2:43 pm
The solution worked perfectly.
Not only did I become much more familiar with CTEs, but I also learned about ROW_NUMBER()! That's a great little built-in function I'll be using frequently I'm sure. I run into this sort of situation with these databases a lot and I'm sure I'll be using a flavor of your solution in the not-so-distant future.
Thanks so much to everyone for your willingness and tenacity to help me out! 😀
Edit: I can't type a decent sentence today to save my life
March 5, 2014 at 3:49 pm
Glad to hear it worked out.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply