Query with History table 2

[youngcougar]

I'm trying to create a start and end date so that I can have a better idea of history.

When I run this query:

SELECT [Item Number], [Item Date], [% OFF MARKED PRICE]
FROM item master file history]
where [item number] like '86%9355'
And [% off marked price] is not null
order by [item number], [Item Date]

I get the following result:

Item number Item Date % off marked price
869000000009355 2006-12-18 00:00:00 70
869000000009355 2007-01-08 00:00:00 80
869000000009355 2007-02-26 00:00:00 90

what I want my data to look like is:

Item Number Start Date End Date % off marked price
869000000009355 2006-12-18 00:00:00 2007-01-07 00:00:00 70
869000000009355 2007-01-08 00:00:00 2007-02-25 00:00:00 80
869000000009355 2007-02-26 00:00:00 getdate() 90


any ideas?

And Thanks!!

 

[gmmastros]

My suggestion would be to create a table variable with an identity column. Then, insert the data in to this identity column. Then, finally, do a left self join on this table (making sure you include the item number in the join condition).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Lamprey13]

Something like:

SELECT     
	[Item Number], 
	[Item Date] AS StartDate, 
	COALESCE((SELECT MIN([Item Date]) FROM [item master file history] WHERE [Item Date] > h.[Item Date]), GETDATE()) AS EndDate,
	[% OFF MARKED PRICE]
FROM         item master file history] AS h
where  [item number] like '86%9355'
And [% off marked price] is not null
order by [item number], [Item Date]

 

[Lamprey13]

Opps, missed the date subtraction:

SELECT     
    [Item Number], 
    [Item Date] AS StartDate, 
    COALESCE((SELECT MIN([Item Date]) FROM [item master file history] WHERE [Item Date] > h.[Item Date]) - 1, GETDATE()) AS EndDate,
    [% OFF MARKED PRICE]
FROM         item master file history] AS h
where  [item number] like '86%9355'
And [% off marked price] is not null
order by [item number], [Item Date]

 

[youngcougar]

Close.... here's the scenario....we can go in and increase the discount amount on product - every time we do, the day is recorded. We record inventory on different count dates - and depending on that count date has to be between the start and end dates.

So your query returns the end date as the day after it was entered. I need the end date to be the day before the next start date. The closest I've come to is:

select a.[Item Number] 'Item',b.[Item Date] 'Start',a.[% off marked price] 'Curr Dis',
a.[Item Date] 'END',b.[% off marked price] 'prev Dis'
from [item master file history] a join [item master file history] b on a.[item number] = b.[item number]
and b.[item date] <= a.[item date]
and a.[item date] in (select max([item date]) from [item master file history] group by [Item Number])
and b.[item date] in (select max([item date]) from [item master file history] where [item date] < a.[item date] group by [Item Number])

and a.[item number] like '86%9355'
and a.[% off marked price] is not null
and b.[% off marked price] is not null

From another post

 

[youngcougar]

that returns this:

869000000009355 2006-12-18 00:00:00 2006-12-18 00:00:00.000 70
869000000009355 2007-01-08 00:00:00 2007-01-08 00:00:00.000 80
869000000009355 2007-02-26 00:00:00 2007-02-26 00:00:00.000 90

the 'end date' needs to be 1 day less than the start date of the next row - so for the first row, the end date needs to be 2007-01-07, the second row - the end date needs to be 2007-02-25, and the last row the end date needs to be current date (unless there are more rows!)

 

[youngcougar]

No more ideas??

 

[LNBruno]

Did you run Lamprey's second code block? It appears to return exactly what you originally requested.

< M!ke >

 

[youngcougar]

Lamprey's second block returns:

869000000009355 2006-12-18 00:00:00 2006-12-18 00:00:00.000 70
869000000009355 2007-01-08 00:00:00 2007-01-08 00:00:00.000 80
869000000009355 2007-02-26 00:00:00 2007-02-26 00:00:00.000 90

 

[Lamprey13]

what I want my data to look like is:

Item Number Start Date End Date % off marked price
869000000009355 2006-12-18 00:00:00 2007-01-07 00:00:00 70
869000000009355 2007-01-08 00:00:00 2007-02-25 00:00:00 80
869000000009355 2007-02-26 00:00:00 getdate() 90

--SET UP
DECLARE @ItemMasterFileHistory TABLE (ItemNumber VARCHAR(30), ItemDate DATETIME, PercentOff INT)
INSERT @ItemMasterFileHistory
SELECT '869000000009355',  '2006-12-18 00:00:00', 70    
UNION SELECT '869000000009355', '2007-01-08 00:00:00', 80    
UNION SELECT '869000000009355', '2007-02-26 00:00:00', 90   

-- Query (same as above)
SELECT     
    [ItemNumber], 
    [ItemDate] AS StartDate, 
    COALESCE((SELECT MIN([ItemDate]) FROM @ItemMasterFileHistory WHERE [ItemDate] > h.[ItemDate]) - 1, GETDATE()) AS EndDate,
    PercentOff AS [% OFF MARKED PRICE]
FROM 
	@ItemMasterFileHistory AS h
where  [ItemNumber] like '86%9355'
And [PercentOff] is not null
order by [ItemNumber], [ItemDate]

-- Result
ItemNumber                     StartDate               EndDate                 % OFF MARKED PRICE
------------------------------ ----------------------- ----------------------- ------------------
869000000009355                2006-12-18 00:00:00.000 2007-01-07 00:00:00.000 70
869000000009355                2007-01-08 00:00:00.000 2007-02-25 00:00:00.000 80
869000000009355                2007-02-26 00:00:00.000 2007-05-29 14:36:06.030 90

Yep, works as expected..

 

[youngcougar]

I should have mentioned - my bad - that this table has more than 3 rows, and this 'item number' has more than 3 rows. Any time there is any update (on any of 20 other columns) the date is recorded. so if I remove the 'null' from the where clause:

SELECT     [Item Number], [Item Date], [% OFF MARKED PRICE]
FROM         [item master file history] 
where  [item number] like '86%0009355'
order by [item number], [Item Date]

I get

869000000009355	2006-11-03 00:00:00	NULL
869000000009355	2006-11-03 00:00:00	NULL
869000000009355	2006-11-06 00:00:00	NULL
869000000009355	2006-11-10 00:00:00	NULL
869000000009355	2006-11-10 00:00:00	NULL
869000000009355	2006-11-16 00:00:00	NULL
869000000009355	2006-12-07 00:00:00	NULL
869000000009355	2006-12-18 00:00:00	NULL
869000000009355	2006-12-18 00:00:00	70    
869000000009355	2006-12-18 00:00:00	NULL
869000000009355	2006-12-27 00:00:00	NULL
869000000009355	2007-01-08 00:00:00	80    
869000000009355	2007-02-26 00:00:00	90    
869000000009355	2007-04-17 00:00:00	NULL

my apologies to you both! I should have been clearer in my initial post!

 

[Lamprey13]

Assuming you still want the same output, add the IS NULL clause to the sub-query:

SELECT     
    [ItemNumber], 
    [ItemDate] AS StartDate, 
    COALESCE((SELECT MIN([ItemDate]) FROM @ItemMasterFileHistory WHERE [ItemDate] > h.[ItemDate] [b][COLOR=red]AND [PercentOff] IS NOT NULL[/color][/b]) - 1, GETDATE()) AS EndDate,
    PercentOff AS [% OFF MARKED PRICE]
FROM 
	@ItemMasterFileHistory AS h
where  [ItemNumber] like '86%9355'
And [PercentOff] is not null
order by [ItemNumber], [ItemDate]

 

[LNBruno]

Lamprey - I'm giving you a star just for sheer persistence!

;-)

< M!ke >

 

[youngcougar]

SELECT
[Item Number],
[Item Date] AS StartDate,
COALESCE((SELECT MIN([Item Date]) FROM (select [Item Number], [Item Date],[% OFF MARKED PRICE]
FROM [item master file history]
where [item number] like '86%9355'
And [% off marked price] is not null) as a WHERE [Item Date] > h.[Item Date]) - 1, GETDATE()) AS EndDate,
[% OFF MARKED PRICE]
FROM (select [Item Number], [Item Date],[% OFF MARKED PRICE]
FROM [item master file history]
where [item number] like '86%9355'
And [% off marked price] is not null
) AS h
where [item number] like '86%9355'
And [% off marked price] is not null
order by [item number], [Item Date]


YES - Lamprey Rocks - nother star!! For some reason, nothing was giving me the same results - it obviously had something to do with something else in the table. But using your idea - I pulled it from the 'data set' that worked using your query - and now it works SAWEET!!

Thanks!!