Here we ensure that we are not selecting any rows from holding_value_stamps1 * FROM ( SELECT holding_id, holding_type, MAX ( created_at ) as created_at FROM holding_value_stamps GROUP BY holding_id, holding_type ) AS most_recent_stamps INNER JOIN holding_value_stamps ON holding_value_stamps. This point only StockHoldings and BondHoldings) that belongs to the user. To do this we need the most recent HoldingValueStamps for each holding (at We also need to be able to tell the user the current value of their portfolio. Our users a trend-line of their portfolio’s value overtime. Having these stamps would also allow us to show These stamps are created to keep an historical record of the HoldingValueStamp will be how we track their value.Įvery minute we’ll create a HoldingValueStamp with the current value of yourġ0 shares of AAPL. The value of those AAPL shares wouldįluctuate over time.
You bought 10 shares of AAPL we would create a StockHolding for you withĬompany_id: 'AAPL', quantity: 10. Here StockHolding and BondHolding represent ownership of stock and bonds. HoldingValueStamps: [ user_id, holding_id, holding_type, value,.BondHoldings: [ id, user_id, company_id, purchase_price, par_value,.This strategy can be expanded for any number of similar situations, even those This situation you’d really just want to pick one of the two and adding aĭISTINCT clause on user_id in the above example would do just that. A larger one if you are doing something user-facing. A small one if you areĭisplaying the latest orders to the admins of your application (they see two The upshot is that this query would includeĢ entries for that user. What if two orders have the same datetime (specifically the most recent)? In theĬase of users making orders it’s pretty unlikely short of a double-submitīug, but depending on your use-case for the table in question and the scale of One caveat: there’s a slight issue with using a datetime as an identifier here. Return only the subset of user_ids paired with their most recent order this The orders that match the user_id and the created_at timestamp of the created_atĪlright, this is slightly less intuitive. * FROM ( SELECT user_id, MAX ( created_at ) AS created_at FROM orders GROUP BY user_id ) AS latest_orders INNER JOIN orders ON orders. ( select max(todate) from where id = fr.SELECT orders. ( select min(fromdate) from where id = fr.id) as FROMDATE,
select * from - select the distinct result along with MIN 'from date' and MAX 'to date' and the hours select distinct
current fortnightly hours are different than the previous fortnight - increase the ID and insert the row in the final table set = + 1 insert into select end else begin - current fortnightly hours are same as the previous fortnightly hours - insert the row in the final result table with the previous ID insert into select end end fetch next from rc into end close rc
#Aggregate date up to a date sql code#
But I am confused as it did not serve the purpose.Ĭopy Code declare datetime set = ' 00:00:00.000' - starting point date declare tableīegin - if there are no rows in the final result table then insert the first from from the previous result table - it will be the starting row because the cursor is ordered by from date ascending if not exists ( select 1 from insert into select end else begin - there are rows in the final result table - so fetch the last row total hours and compare it with the current row total hours in the cursor if ( select top 1 totalhours from order by fromdate desc) begin - the hours comparison failed i.e. I tried using cursors for any particular employee and using forward addition to dates and comparing previous fortnight hours to see if there was a difference to break the lines as required by the output. Because of the dynamic nature and the business process about changing hours any time, I want to get the output as the following Now I want to pull out a report on how many hours I work in the organisation. In that case, the system will edit hours between those dates only and the remaining hours will be left as it is up till Dec 2020. But, I want to change my hours from January 2017 to March 2017 as working only 40 hours per fortnight. For example, I normally work 75 hours per fortnight. Now, the business process is that a user can change working hours. Three columns - User ID | Date | Working hoursĭata is filled in for each user (with IDs), each date (from Jan 2010 to Dec 2020) and working hours for each day. I have the following kind of structure in the database as rows