13 Replies - 512 Views - Last Post: 15 May 2019 - 07:50 AM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 05:20 AM

ok i need to find the last items entered in a hour which could be 1 or 10 items
they all have the same shift id
the products have their own id
the hours can be mixed
the time is a string and it is set that way from a listbox (their request)

not really sure how i can limit it to just the items in the previous hour for that shift

sample of the data: 2nd column is shiftid 1st column is tHourlyTransactions.HourlyTransactionsID

1	1	2	2019-05-08	10:00 AM	12000.00	False
2	1	2	2019-05-08	11:00 AM	11000.00	False
3	1	1	2019-05-08	12:00 PM	13000.00	False
4	2	1	2019-05-08	10:00 AM	11000.00	False
5	7	1	2019-05-08	11:00 AM	12000.00	False
6	8	2	2019-05-08	12:00 PM	10000.00	False
7	8	1	2019-05-08	12:00 AM	5000.00	False
8	8	2	2019-05-08	12:00 AM	10000.00	False
NULL	NULL	NULL	NULL	NULL	NULL	NULL




this gets all item from the shift

declare @ShiftID int = 8


Select   sum(dttime) as dtt, txtProductName, tdowntimedetails.HourlyTransactionsID from tdowntimedetails
   inner join tHourlyTransactions 
   on 
   tdowntimedetails.HourlyTransactionsID = tHourlyTransactions.HourlyTransactionsID
   inner join tProduct
   on tProduct.productid = tHourlyTransactions.productid
   where tHourlyTransactions.ShiftID = @ShiftID 
   --and TransStartTime in (select TransStartTime from tHourlyTransactions
   --where tHourlyTransactions.ShiftID = @ShiftID and productid 



   group by txtProductName , tdowntimedetails.HourlyTransactionsID
     order by tdowntimedetails.HourlyTransactionsID desc


results last column is tHourlyTransactions.HourlyTransactionsID
50.00	BUFFINGS	8
30.00	NUGGETS	7
20.00	BUFFINGS	6


so in this scenario it should just display
20.00	BUFFINGS	6
since it was the previous item entered on that shift

Is This A Good Question/Topic? 0
  • +

Replies To: how to find items only in last hour but the hours are strings

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2956
  • View blog
  • Posts: 11,476
  • Joined: 03-December 12

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 05:52 AM

Any reason you can't just do a date time difference?
Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 06:01 AM

because the time is stored as a string and not time?
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2956
  • View blog
  • Posts: 11,476
  • Joined: 03-December 12

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 06:05 AM

so concatenate and cast.
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2956
  • View blog
  • Posts: 11,476
  • Joined: 03-December 12

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 07:00 AM

Something like,


Declare @date varchar(20) = '2019-05-08'
Declare @time varchar(20) = '10:00 AM'

SELECT  convert(datetime, CONCAT(@date, ' ', @time), 120) AS 'DateTime'

Was This Post Helpful? 2
  • +
  • -

#6 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 07:23 AM

lol figures cant use that -- seems their sql server is on 2008
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2956
  • View blog
  • Posts: 11,476
  • Joined: 03-December 12

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 07:27 AM

Then you change convert to cast, not literally, you would need to proper syntax, but still the same process.

https://www.fmsinc.c...time_format.htm
Was This Post Helpful? 0
  • +
  • -

#8 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 08:35 AM

yeah i tried doing that -- convert is the word you are looking for

the issue that i have come across is that it takes the time and looks for all items that are 1 hour before

example as follows:

35.00	NUGGETS	7	1900-01-01 11:19:00.000	1900-01-01 10:19:00.000
35.00	NUGGETS	6	1900-01-01 10:19:00.000	1900-01-01 09:19:00.000
20.00	NUGGETS	5	1900-01-01 10:19:00.000	1900-01-01 09:19:00.000
15.00	BUFFINGS	1	1900-01-01 10:02:00.000	1900-01-01 09:02:00.000




running this query:

declare @ShiftID int = 1
--SELECT CONVERT( TIME, '10:00:22 PM' );

Select sum(dttime) as dtt, txtProductName, tdowntimedetails.HourlyTransactionsID, convert(datetime,tHourlyTransactions.TransStartTime) as starttime , DATEADD(hh,-1,convert(datetime,tHourlyTransactions.TransStartTime)) as blah from tdowntimedetails  
   inner join tHourlyTransactions 
   on 
   tdowntimedetails.HourlyTransactionsID = tHourlyTransactions.HourlyTransactionsID
   inner join tProduct
   on tProduct.productid = tHourlyTransactions.productid
   where tHourlyTransactions.ShiftID = @ShiftID 
 and 
 DATEADD(hh,-1,convert(datetime,tHourlyTransactions.TransStartTime)) < convert(datetime,tHourlyTransactions.TransStartTime)
 
 
   group by txtProductName , tdowntimedetails.HourlyTransactionsID, tHourlyTransactions.TransStartTime
     order by tdowntimedetails.HourlyTransactionsID desc




as you see by the 2 columns it grabs everything not just the items that fall 1 hour before the 11:19 item
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2956
  • View blog
  • Posts: 11,476
  • Joined: 03-December 12

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 08:42 AM

That's because there is a parse error in your date format,

1900-01-01 11:19:00.000 1900-01-01 10:19:00.000


1/1/1900 translates to a bad date and I bet that is the date for every item using the query.

View PostDarenR, on 09 May 2019 - 09:23 AM, said:

lol figures cant use that -- seems their sql server is on 2008


So, what can't you use? As that seems to be the issue you are having. The comparison needs the entire timestamp to work correctly, not just a portion.
Was This Post Helpful? 0
  • +
  • -

#10 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 09:19 AM

this is how i solved it


Select sum(dttime) as dtt, txtProductName,  convert(datetime,tHourlyTransactions.TransStartTime) as starttime  from tdowntimedetails  
   inner join tHourlyTransactions 
   on 
   tdowntimedetails.HourlyTransactionsID = tHourlyTransactions.HourlyTransactionsID
   inner join tProduct
   on tProduct.productid = tHourlyTransactions.productid
   where tHourlyTransactions.ShiftID = @ShiftID 
 and 


convert(datetime,tHourlyTransactions.TransStartTime) > DATEADD(HOUR, -1, (select top 1 convert(datetime,tHourlyTransactions.TransStartTime) 
 from tdowntimedetails  
   inner join tHourlyTransactions 
   on 
   tdowntimedetails.HourlyTransactionsID = tHourlyTransactions.HourlyTransactionsID
   inner join tProduct
   on tProduct.productid = tHourlyTransactions.productid
   where tHourlyTransactions.ShiftID = @ShiftID 
   order by tHourlyTransactions.HourlyTransactionsID desc))


  AND convert(datetime,tHourlyTransactions.TransStartTime) <  DATEADD(HOUR, -1, GETDATE())


   group by txtProductName , tHourlyTransactions.TransStartTime
     order by txtProductName desc




Was This Post Helpful? 0
  • +
  • -

#11 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2956
  • View blog
  • Posts: 11,476
  • Joined: 03-December 12

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 09:49 AM

View Postastonecipher, on 09 May 2019 - 09:00 AM, said:

Something like,


convert(datetime, CONCAT(tHourlyTransactions.TransStartDate, ' ', tHourlyTransactions.TransStartTime), 120) <=  DATEADD(HOUR, -1, GETDATE())


Was This Post Helpful? 0
  • +
  • -

#12 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

Re: how to find items only in last hour but the hours are strings

Posted 09 May 2019 - 10:44 AM

hmm i dont see that reply above but oh well
Was This Post Helpful? 0
  • +
  • -

#13 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

Re: how to find items only in last hour but the hours are strings

Posted 15 May 2019 - 05:30 AM

ok the thing still isnt working correctly

 SELECT Sum(poundsperhour) AS poundsperhour, basepoundsperhour,
     thourlytransactions.productid,  thourlytransactions.transstarttime
INTO   #temppounds 
FROM   thourlytransactions 
       INNER JOIN tplantproducts 
               ON tplantproducts.productid = thourlytransactions.productid 
       INNER JOIN tproduct 
               ON tproduct.productid = thourlytransactions.productid 
       
WHERE  thourlytransactions.shiftid = @Shiftid 
--and thourlytransactions.HourlyTransactionsID = @hourlyID


       AND CONVERT(DATETIME, thourlytransactions.transstarttime)>=
           Dateadd(hour, -1, (SELECT TOP 1 CONVERT(DATETIME, 
           thourlytransactions.transstarttime) 
          -- FROM   tdowntimedetails 
		   FROM thourlytransactions
         --  INNER JOIN thourlytransactions 
          -- ON tdowntimedetails.hourlytransactionsid = 
          -- thourlytransactions.hourlytransactionsid 
           INNER JOIN tproduct 
           ON tproduct.productid = 
           thourlytransactions.productid 
           WHERE  thourlytransactions.shiftid = @ShiftID 
           ORDER  BY thourlytransactions.hourlytransactionsid DESC)) 
       AND CONVERT(DATETIME, thourlytransactions.transstarttime)<
           Dateadd(hour, +1, Getdate()) 
GROUP  BY  
         thourlytransactions.productid ,
		 basepoundsperhour,
		 thourlytransactions.transstarttime



it brings back the following
poundsperhour	basepoundsperhour	productid	transstarttime
15000.00	15000.00	           2	            7:58 AM
10000.00	15000.00	           2	            8:00 AM



it should only be bringing back the 7 am hour time

any thoughts as to why this isnt working as expected?
Was This Post Helpful? 0
  • +
  • -

#14 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

Re: how to find items only in last hour but the hours are strings

Posted 15 May 2019 - 07:50 AM

never mind im just gonna pass the date in and check the date for that hour--- easy peasy
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1