Simphony POS: SQL Export to Consolidate Split Checks

[mmajors]

I'm currently trying to get a transaction report exported from Simphony POS.
Please know that I'm not an expert with the databases and I've had zero luck finding a comprehensive schema.

I'm currently stuck at attempting to consolidate split checks.
With my current query, if 1 Burger is sold for $10 and is split into two checks, the resultant sales qty & revenue are 1 sold for $5 and 0 sold for $5. This present a problem for in calculating results on 0 quantity sales so I'm trying to 'paste' the sales back together.

My first inclination is to reference the original guestcheckid but I'm not finding it.
Is this information stored anywhere and, if not, is there an alternative solution I'm not considering?

My current query follows:

SELECT r.[name] as 'revenuecentername'
      ,[businessdate]
      ,[transdatetime]
      ,c.[guestcheckid]
	  ,(CASE WHEN k.[splitOut] IS NULL THEN 0 ELSE 1 END) as 'isSplitCheck'
	  ,u.[name] as 'uwsname'
      ,[checkEmpName]
      ,[transEmpName]
      ,[authEmpName]
      ,[transactionType]
      ,[linenum]
      ,[linecount]
      ,[linetotal]
      ,LTRIM(RTRIM([itemName])) + 
	   (CASE WHEN [referenceInfo] IS NOT NULL THEN (' [' + LTRIM(RTRIM([referenceInfo])) + ']') ELSE '' END) as 'itemName'
      ,(CASE [itemNumber] 
		WHEN 100001 THEN ('Open Food [' + LTRIM(RTRIM([referenceInfo])) + ']')
		ELSE CAST([itemNumber] AS VARCHAR) END) as 'itemNumber'
      ,(CASE WHEN ([transactionType] = 'Item Sale') THEN
		  (SELECT MAX(SUBSTRING(s.[OptionBits],2,1))
			 FROM [MCRSPOS].[dbo].[MENU_ITEM_MASTER] a
			    , [MCRSPOS].[dbo].[MENU_ITEM_DEFINITION] d
			    , [MCRSPOS].[dbo].[MENU_ITEM_CLASS] s
			 WHERE c.[itemNumber] = a.[ObjectNumber]
			    AND a.[MenuItemMasterID] = d.[MenuItemMasterID]
			    AND d.[MenuItemClassObjNum] = s.[ObjectNumber])
		ELSE
		  0
		END
		) as 'isCondiment'
	  ,[familyGroupName]
      ,[majorGroupName]
      ,[pricelevel]
      ,[weighttotal]
      ,[discountName]
      ,[serviceChargeName]
      ,c.[tax1total]
      ,[tenderName]
      ,[tenderNumber]
      ,[referenceinfo]
      ,[voidFlagTF]
      ,[returnFlagTF]
      ,[errorCorrectFlagTF]
      ,[reasonName]
  FROM [LOCATION_ACTIVITY_DB].[dbo].[vmm_check_details] c
  JOIN [LOCATION_ACTIVITY_DB].[dbo].[LOCATION_HIERARCHY_ITEM] l
  ON c.locationID = l.locationID
  JOIN [LOCATION_ACTIVITY_DB].[dbo].[REVENUE_CENTER] r
  ON c.revenuecenterID = r.revenuecenterID
  JOIN [LOCATION_ACTIVITY_DB].[dbo].[USER_WORKSTATION] u
  ON c.uwsID = u.uwsID
  JOIN [LOCATION_ACTIVITY_DB].[dbo].[GUEST_CHECK_HIST] k
  ON c.[guestCheckID] = k.[guestCheckID]
  WHERE [businessDate] = dateadd(day,datediff(day,1,GETDATE()),0)

 

[codexPOSed]

Use the transaction DB(MCRPOS) rather than the reporting DB (LOCATION_ACTIVITY_DB). Here is UML that might help:

Also, keep in mind that you won't have access to databases if it's Simphony SE on the cloud. then your options are to:
1. Build a custom R&A report
2. Use Enterprise Back Office Standard Export files to create your report

Hope this helps

 

[mmajors]

This is perfect!
I’ve been doing everything manual up until this point. Do you happen to have a schema for MCRPOS or will what I have thus far translate correctly by simply switching databases?