3 Replies - 139 Views - Last Post: 29 March 2019 - 11:55 AM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 612
  • View blog
  • Posts: 4,039
  • Joined: 12-January 10

tricky question on how to find a value on previous date

Posted 29 March 2019 - 09:22 AM

the query i am trying to create has to do the following:

when i user is on a job they select the date of the job which can be any date
when they put input into the fields and the field that holds remaining product is saved to the db
when the user uses the same machine a few days later and enters a date it should search the last date the machine was used and find whether there are items remaining from the last time the machine was used

the question is this:

how does one search for the very last date the machine was used and then verify the field in question doesnt have information and if it has add it to the table information being pulled back:

example:

i use the machine on 1/1/2019 and i have 35 items remaining

i use the machine many days later and i need to have the 35 items returned as must do

any thoughts?

my main issue is trying to get the date that was last used without a range set because it could be used 1 day before or 1 year before

Is This A Good Question/Topic? 0
  • +

Replies To: tricky question on how to find a value on previous date

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15058
  • View blog
  • Posts: 60,124
  • Joined: 12-June 08

Re: tricky question on how to find a value on previous date

Posted 29 March 2019 - 09:37 AM

Max(date column) works pretty well.


create table #foo(lval int, date_entered datetime)

insert into #foo(lval, date_entered) values(1, '01/05/2018')
insert into #foo(lval, date_entered) values(2, '5/05/2018')
insert into #foo(lval, date_entered) values(12, '02/05/2019')
insert into #foo(lval, date_entered) values(41, '03/05/2019')

select *
from #foo

select *
from #foo a
join (
select max(date_entered)  as max_date
from #foo
) b on a.date_entered = b.max_date
 

drop table #foo



lval        date_entered
----------- -----------------------
1           2018-01-05 00:00:00.000
2           2018-05-05 00:00:00.000
12          2019-02-05 00:00:00.000
41          2019-03-05 00:00:00.000

(4 row(s) affected)

lval        date_entered            max_date
----------- ----------------------- -----------------------
41          2019-03-05 00:00:00.000 2019-03-05 00:00:00.000


Then you would use what ever key you pull from that to pull the items stashed in your table.
Was This Post Helpful? 1
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 612
  • View blog
  • Posts: 4,039
  • Joined: 12-January 10

Re: tricky question on how to find a value on previous date

Posted 29 March 2019 - 10:49 AM

ok i got it to pull the right rows using this:

select * from dbo.aaa_tUptimeFeedstock a join (select max(dbo.aaa_tUptimeFeedstock.ProdDate) as maxDate from dbo.aaa_tUptimeFeedstock
inner join dbo.aaa_tUptimeCategorydivision
on dbo.aaa_tUptimeCategorydivision.DivisionKey = dbo.aaa_tUptimeFeedstock.DivisionKey
where dbo.aaa_tUptimeFeedstock.ProdDate [email protected]
and dbo.aaa_tUptimeFeedstock.DivisionKey = @DivisionKey) b
on a.proddate<b.maxDate
where (BLK_RemainingOnHand <> 0 or BLKWT_RemainingOnHand <> 0)
and DivisionKey = @DivisionKey

returned data
31	2019-01-08 00:00:00.000	2019-03-29 11:46:43.760	drrodriguez	1900-01-01 00:00:00.000		2.00	0.00	2.00	0.00	0.00	100.00	632	13	1	2019-01-09 00:00:00.000
31	2019-01-08 00:00:00.000	2019-03-29 11:46:43.760	drrodriguez	1900-01-01 00:00:00.000		2.00	0.00	2.00	0.00	0.00	100.00	632	13	1	2019-01-09 00:00:00.000
31	2019-01-08 00:00:00.000	2019-03-29 11:46:43.760	drrodriguez	1900-01-01 00:00:00.000		2.00	0.00	2.00	0.00	0.00	100.00	632	13	1	2019-01-09 00:00:00.000
31	2019-01-08 00:00:00.000	2019-03-29 11:46:43.760	drrodriguez	1900-01-01 00:00:00.000		2.00	0.00	50.00	0.00	0.00	100.00	632	1	1	2019-01-09 00:00:00.000




how would i grab BLK_RemainingOnHand or BLKWT_RemainingOnHand from that select statement and place it in the current day's work?
just an fyi there are thousands of terminals that use the program all with their own ids and such

the below brings the information how i must return it.

if there is no remaining it would just bring back a 0


this is what i originally used:

Select		Alias
			   ,dbo.aaa_tUptimeCategorydivision.WorkCenter	
			   ,dbo.aaa_tUptimeCategorydivision.[Categorykey] 
			   ,dbo.aaa_tuptimecategory.CategoryID	
			   ,dbo.aaa_tUptimeFeedstock.ProdDate	
			   ,dbo.aaa_tUptimeFeedstock.[CreateDate]
			   ,dbo.aaa_tUptimeFeedstock.[CreateUser]
			   ,dbo.aaa_tUptimeFeedstock.[UpdateDate]
			   ,dbo.aaa_tUptimeFeedstock.[UpdateUser]
			   ,dbo.aaa_tUptimeFeedstock.[BLK_Tons_Added]
			   ,dbo.aaa_tUptimeFeedstock.[BLK_Consumed_Tons]
			   ,dbo.aaa_tUptimeFeedstock.[BLK_RemainingOnHand]
			   ,dbo.aaa_tUptimeFeedstock.[BLKWT_Tons_Added]
			   ,dbo.aaa_tUptimeFeedstock.[BLKWT_Consumed_Tons]
			   ,dbo.aaa_tUptimeFeedstock.[BLKWT_RemainingOnHand]
			 
	
	from dbo.aaa_tuptimecategory
	inner join dbo.aaa_tUptimeCategorydivision 
	on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
	left outer join dbo.aaa_tuptimefeedstock
	on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey 
	and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
	and  dbo.aaa_tUptimeFeedstock.ProdDate = @ProdDate 
	and dbo.aaa_tUptimeFeedstock.status = 1

	where
	dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey 
	and dbo.aaa_tUptimeCategorydivision.active = 1
	end



output:

Shredder	601	1	Primary1	2019-01-09 00:00:00.000	2019-03-29 11:46:42.537	drrodriguez	1900-01-01 00:00:00.000		3.00	0.00	3.00	0.00	0.00	0.00
2" Granulator	632	13	Granulator1	2019-01-09 00:00:00.000	2019-03-29 11:46:43.760	drrodriguez	1900-01-01 00:00:00.000		2.00	0.00	2.00	0.00	0.00	0.00

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15058
  • View blog
  • Posts: 60,124
  • Joined: 12-June 08

Re: tricky question on how to find a value on previous date

Posted 29 March 2019 - 11:55 AM

Quote

how would i grab BLK_RemainingOnHand or BLKWT_RemainingOnHand from that select statement and place it in the current day's work?
I do not know what those fields are from above. Are they keyed fields? If so you could just select those values and INSERT them into "today's work table".

create table #foo(lval int, date_entered datetime)
create table #bar (lval int)

insert into #foo(lval, date_entered) values(1, '01/05/2018')
insert into #foo(lval, date_entered) values(2, '5/05/2018')
insert into #foo(lval, date_entered) values(12, '02/05/2019')
insert into #foo(lval, date_entered) values(41, '03/05/2019')

select *
from #foo

insert into #bar(lval)
select lval
from #foo a
join (
select max(date_entered)  as max_date
from #foo
) b on a.date_entered = b.max_date
 
 select * from #bar

drop table #bar
drop table #foo




lval        date_entered
----------- -----------------------
1           2018-01-05 00:00:00.000
2           2018-05-05 00:00:00.000
12          2019-02-05 00:00:00.000
41          2019-03-05 00:00:00.000

(4 row(s) affected)

(1 row(s) affected)

lval
-----------
41




don't forget your 'with(nolock)' s..
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1