SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to get last data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

osupratt
Posting Yak Master

233 Posts

Posted - 11/09/2012 :  11:42:05  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/09/2012 :  11:50:11  Show Profile  Reply with Quote
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;
Go to Top of Page

osupratt
Posting Yak Master

233 Posts

Posted - 11/30/2012 :  14:37:23  Show Profile  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/30/2012 :  14:46:02  Show Profile  Reply with Quote
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
Go to Top of Page

osupratt
Posting Yak Master

233 Posts

Posted - 11/30/2012 :  15:50:56  Show Profile  Reply with Quote
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.
Go to Top of Page

Howard43Willard
Starting Member

USA
8 Posts

Posted - 12/02/2012 :  20:21:59  Show Profile  Reply with Quote
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
Go to Top of Page

osupratt
Posting Yak Master

233 Posts

Posted - 12/03/2012 :  14:08:42  Show Profile  Reply with Quote
i can't view these pictures if that is what they are.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/03/2012 :  14:19:02  Show Profile  Reply with Quote
I think it is just spam, osupratt.

Have you yet been able to solve the problem you were trying to solve?
Go to Top of Page

osupratt
Posting Yak Master

233 Posts

Posted - 12/03/2012 :  16:04:51  Show Profile  Reply with Quote
not yet. i am trying but haven't been able to. struggling.
Go to Top of Page

osupratt
Posting Yak Master

233 Posts

Posted - 12/04/2012 :  12:22:35  Show Profile  Reply with Quote
i have figured this out. thanks.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
308 Posts

Posted - 12/04/2012 :  15:03:06  Show Profile  Reply with Quote
osupratt, What did you finally do?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000