| Author |
 Topic  |
|
|
osupratt
Posting Yak Master
220 Posts |
 Posted - 11/09/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/09/2012 : 11:50:11
|
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;
|
 |
|
|
osupratt
Posting Yak Master
220 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/30/2012 : 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
220 Posts |
Posted - 11/30/2012 : 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
USA
8 Posts |
Posted - 12/02/2012 : 20:21:59
|
so if the location value is NULL then i would like the location of the last or prior location to show.
 |
Edited by - Howard43Willard on 12/02/2012 20:27:18 |
|
|
|
osupratt
Posting Yak Master
220 Posts |
Posted - 12/03/2012 : 14:08:42
|
| i can't view these pictures if that is what they are. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/03/2012 : 14:19:02
|
I think it is just spam, osupratt.
Have you yet been able to solve the problem you were trying to solve? |
|
|
|
osupratt
Posting Yak Master
220 Posts |
Posted - 12/03/2012 : 16:04:51
|
| not yet. i am trying but haven't been able to. struggling. |
|
|
|
osupratt
Posting Yak Master
220 Posts |
Posted - 12/04/2012 : 12:22:35
|
| i have figured this out. thanks. |
|
|
|
djj55
Yak Posting Veteran
USA
98 Posts |
Posted - 12/04/2012 : 15:03:06
|
| osupratt, What did you finally do? |
|
|
| |
Topic |
|
how to get last data
