Author Topic: SQL query to retrieving recent 8 tickets  (Read 2749 times)


  • Jr. Member
  • **
  • Posts: 82
SQL query to retrieving recent 8 tickets
« on: October 31, 2013, 05:50:19 pm »
I am implementing Kitchen display for my store where I need to display last 8 tickets with items, void and other details.  What would be the SQL query to retrieve this data?


My idea is running a python web server on a back office computer where MS SQL Server is running.   Web Server will process incoming request, execute SQL query to retrieve recent 8 tickets details and return data in a formated HTML page.

Client (Raspberry Pi) will be accessing web page and refresh every 1 minute.



- See more at:


  • Newbie
  • *
  • Posts: 35
  • Sip some Coffee or Wine at JavaVine!
    • JavaVine Cafe & Wine Bar
Re: SQL query to retrieving recent 8 tickets
« Reply #1 on: November 01, 2013, 12:53:27 pm »
It will be quite a bit more complicated than this, to parse Order Tags and Order States into a friendly format, but you'll want to look at the "Orders" table...
Code: [Select]
SELECT [TicketId]
  FROM [SambaPOS3].[dbo].[Orders]
  WHERE [CreatedDateTime] >= '2013-11-01 07:35:38.220' AND [CreatedDateTime] <= '2013-11-02'
  ORDER BY [TicketId], [OrderNumber]
SambaPOS3 since 2013-10
SambaPOS 3.0.31
SQL Express 2008 R2 x64
Windows 7 SP1 x64


  • Full Member
  • ***
  • Posts: 165
Re: SQL query to retrieving recent 8 tickets
« Reply #2 on: November 01, 2013, 04:41:47 pm »
First it depends if you are referring to V2 or V3 as they use different database tables.

If you just simply want the very last 8 entered a query such as:

  FROM [SambaData2].[dbo].[Tickets]
  ORDER BY [LastUpdateTime] DESC

This is the fastest way to recall the information that you require, from here you will need to reference the [Id] in the dbo.TicketItems table to recall the relevant items in the ticket and then display.

You do not need to include all the columns I mentioned it just depends what information you want to recall from dbo.Tickets, place it in an array, then query the dbo.TicketItems for the product items on each ticket.

V3 has a different setup so you can use the same principal but reference the correct tables and data.


  • Jr. Member
  • **
  • Posts: 82
Re: SQL query to retrieving recent 8 tickets
« Reply #3 on: November 19, 2013, 06:25:32 pm »
Thanks Guys.

 I came up with below query.

SELECT TicketItems.Id as TicketItemsId, TicketId,MenuItemName,TicketItems.PortionName,TicketItems.Quantity,Price,Voided,Gifted, CreatingUserId, Tickets.Id, Tickets.TicketNumber, Tickets.TotalAmount, CONVERT(varchar, Tickets.LastPaymentDate, 20) as LastPaymentDate, Users.Name FROM TicketItems, Tickets, Users where TicketId in (SELECT TOP(8) Id from Tickets ORDER BY LastUpdateTime DESC) AND TicketId = Tickets.Id and CreatingUserId=Users.Id

To get the flavors/properties of each item
SELECT TicketItemProperties.Name as Flavor FROM TicketItemProperties WHERE TicketItemProperties.TicketItemId = " + str(row.TicketItemsId)

Let me know if there is a optimized way combine both the queries.

I have created python based web interface to view live transactions remotely.  Will share with team once completed.