how to get last data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to get last data

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2012-11-09 : 11:42:05

here is the output of my query:

date location item
1/1/12 ABC rig valve
1/2/12
1/3/12 DEF rig valve
1/4/12
1/5/12
1/6/12
1/7/12 ABC rig valve
1/8/12

so if the location value is NULL then i would like the location of the last or prior location to show. here would be my desired output:

date location item
1/1/12 ABC rig valve
1/2/12 ABC rig valve
1/3/12 DEF rig valve
1/4/12 DEF rig valve
1/5/12 DEF rig valve
1/6/12 DEF rig valve
1/7/12 ABC rig valve
1/8/12 ABC rig valve

This is for an Inventory location function. I need to know where an item is every day of the year. So this is tracking the item.

Any help in figuring this out would be appreciated. Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 11:50:11

[code]SELECT
a.[date],
b.location,
b.item
FROM
YourTable a
CROSS APPLY
(
SELECT TOP (1)
b.location,
b.item
FROM
YourTable b
WHERE
b.[date] <= a.[date]
ORDER BY
b.[date] DESC
) b;
[/code]

osupratt
Posting Yak Master

238 Posts

Posted - 2012-11-30 : 14:37:23

sorry had other projects i had to finish up. i am now back at looking at this. the issue that i am having is that it is only pulling in one item because of the TOP (1) as suggested. i need to pull in ALL inventory items for the date range in my table/function. actually in this case i have it filtered for items 'PUMP9' and 'SWEDGE' but the query is only pulling in 'PUMP9'. is this something simple that i am missing. i've been messing with it for an hour or so and can't figure it out. here is the query i am talking about:

with data as(
SELECT
a.[date],b.location,b.item,b.available
FROM
MercerDailyWorkTicket.dbo.FCTN_WellService_BussDayMonth_DtRng ('1/1/2012','11/30/2012') AS a
CROSS APPLY
(
SELECT
top (1) [date], item, location, available
FROM(
SELECT
CASE WHEN dticket IS NULL THEN InvDate ELSE TickDate END AS Date,
InvItem AS item, TickType,
CASE WHEN dticket IS NULL THEN SUBSTRING(InvLoc,1,4) ELSE SUBSTRING(TickLoc,1,4) END AS Location,
CASE WHEN dticket IS NOT NULL AND totalinvent=1 THEN quant ELSE totalinvent END AS Available
FROM(
SELECT
hdr.dticket, hdr.tick_type, inv.item AS InvItem, inv.cat, inv.sub, inv.totalinvent,
CASE WHEN hdr.dticket IS NULL THEN 'D' ELSE hdr.tick_type END AS TickType,
CASE
WHEN hdr.dticket IS NOT NULL THEN dtl.ddate ELSE dtl.ddate END AS TickDate,
CASE
WHEN inv.creation_date IS NULL OR inv.creation_date<DATEADD(YEAR, DATEDIFF(YEAR,0,DATEADD(YEAR,-1,GETDATE())), 0)
THEN DATEADD(YEAR, DATEDIFF(YEAR,0,DATEADD(YEAR,-1,GETDATE())), 0) ELSE inv.creation_date END AS InvDate,
inv.def_bin_loc AS InvLoc, dtl.ucounter,
dtl.item AS TickItem, dtl.quant, dtl.ddate, dtl.return_date, dtl.def_bin_loc AS TickLoc
FROM dbo.UV_RTMS_OTR_DelTickHdr AS hdr INNER JOIN
dbo.UV_RTMS_OTR_DelTickItem AS dtl ON hdr.dticket = dtl.dticket RIGHT OUTER JOIN
dbo.UV_RTMS_OTR_Inventory AS inv ON dtl.item = inv.item
WHERE inv.itype='R' and inv.item in ('pump9','swedge')
) AS X
WHERE TickType='D'
) AS Y
WHERE [date] <= a.[date]

) b
)
select
[date],item,location,available
from data
order by item,[date]
OPTION (MAXRECURSION 10000)

---------------------------------------------------------
any help with this would be greatly appreciated. thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-30 : 14:46:02

I think you can just add a.item = b.item to Sunita's WHERE clause.

Jim

Everyday I learn something that somebody else already knew

osupratt
Posting Yak Master

238 Posts

Posted - 2012-11-30 : 15:50:56

there is no a.item. the function is just all days from start to end date and contains no inventory items so i can't do what you suggest. thanks.

Howard43Willard
Starting Member

8 Posts

Posted - 2012-12-02 : 20:21:59

so if the location value is NULL then i would like the location of the last or prior location to show.