June 14, 2017 at 1:32 pm
Jacob Wilkins - Wednesday, June 14, 2017 1:19 PMThanks for putting in the effort to give us consumable DDL and sample data!As J pointed out, the scripts don't work in their current form, for a few reasons.
The INSERTs are all inserting into an object called 'mytable', when they need to be inserting into the created tables.
Also, the datatypes for some of the columns have issues.
There's a VARCHAR2 column, which is a data type in Oracle but not SQL Server.
One column tries to be defined as a VARCHAR(10018); that should be VARCHAR(MAX).
The ticket id columns got inferred as DATE for some reason, which causes the INSERTs to fail.
For table definitions, it's much better to just right click and script them out so data types are correct. The other tool simplifies generating the INSERT statements, but it can't really be trusted to infer all data types correctly.
As nice as it is to do everything in one place, I highly recommend using SSMS to script out your tables, and then you can use that tool to generate the INSERTs. If you do use that tool to do everything, then you have to go through and check all the datatypes to make sure they're correct.
Having said all that, I think I understand what you're asking, and if I do, that's honestly not something that should be handled at the SQL Server layer.
As I understand it, your point is that for Rows with a Row#>1, you don't want values in any of the columns except for Choice Added?
If so, that really should be handled at the presentation layer.
For the other requirement, the straightforward way would be to change do a LEFT JOIN to the ChoiceItem table instead of doing an INNER JOIN.
Cheers!
Thanks! I will fix now for more experience with this.
The LEFT join worked, but hmmm, why...I guess I need to refresh on my JOIN examples.
You are correct about the presentation layer...but I Ive seen IF THEN's that work for SQL in this instance...
June 14, 2017 at 2:07 pm
This should be a more educated response!!
Please let me know...this is actual queried data that I pulled the Ticket_ID from and all the corresponding data to get a good result from this test data.
CREATE TABLE [dbo].[Ticket](
[i_ticket_id] [int] IDENTITY(1,1) NOT NULL,
[i_customer_id] [int] NULL,
[s_table_name] [nvarchar](50) NULL,
[c_items_total] [money] NULL CONSTRAINT [DF_Ticket_c_items_total] DEFAULT (0),
[c_taxes_total] [money] NULL CONSTRAINT [DF_Ticket_c_taxes_total] DEFAULT (0),
[c_discount_amount] [money] NULL CONSTRAINT [DF_Ticket_c_discount_amount] DEFAULT (0),
[c_grand_total] [money] NULL CONSTRAINT [DF_Ticket_c_grand_total] DEFAULT (0),
[c_payment_total] [money] NULL CONSTRAINT [DF_Ticket_c_payment_total] DEFAULT (0),
[c_auto_gratuity] [money] NULL,
[f_auto_gratuity_pct] [float] NULL,
[i_void_ticket_id] [int] NULL,
[i_user_id] [int] NULL,
[dt_create_time] [datetime] NULL,
[dt_close_time] [datetime] NULL,
[b_closed] [bit] NOT NULL CONSTRAINT [DF_Ticket_b_closed] DEFAULT (0),
[dt_lock_time] [datetime] NULL,
[i_lock_user_id] [int] NULL,
[dt_cached_time] [datetime] NULL,
[i_created_by_user_id] [int] NULL,
[f_ticket_discount_factor] [float] NULL,
[i_ticket_revision] [int] NULL CONSTRAINT [DF_Ticket_i_ticket_revision] DEFAULT ((0)),
[i_ticket_actual_close_employee_id] [int] NULL,
[c_ticket_tips] [money] NULL,
[i_ticket_seq_number] [int] NULL CONSTRAINT [DF_Ticket_i_ticket_seq_number] DEFAULT ((0)),
[c_ticket_display_subtotal] [money] NULL,
[c_ticket_display_tax_total] [money] NULL,
[i_ticket_timesheet_id] [int] NULL,
[dt_ticket_promise_time] [datetime] NULL,
[i_section_id] [int] NULL CONSTRAINT [df_ticket_section_id] DEFAULT (0),
[i_address_id] [int] NULL,
[i_revcenter_id] [int] NULL,
[manual_hold] [bit] NULL,
[choice_hold] [bit] NULL,
[preauth_amount] [money] NULL,
[cover_count] [int] NULL,
[g_ticket_id] [uniqueidentifier] NULL CONSTRAINT [df_ticket_g_local] DEFAULT (newid()),
[rv_Ticket] [timestamp] NOT NULL,
[dt_recent_guest_check_print] [datetime] NULL,
[i_location_id] [int] NULL,
[s_cached_xml] [nvarchar](max) NULL,
[b_use_custom_status] [bit] NOT NULL CONSTRAINT [DF_ticket_custom_status] DEFAULT ((0)),
[s_custom_status] [nvarchar](50) NULL,
[s_status] [nvarchar](50) NULL,
[preauth_total_no_bar_tab] [money] NULL CONSTRAINT [DF_preauth_total_no_bar_tab] DEFAULT ((0)),
[i_active_seat_count] [int] NOT NULL CONSTRAINT [DF_i_active_seat_count] DEFAULT ((0)),
[dt_last_item_time] [datetime] NULL,
[dt_last_payment_time] [datetime] NULL,
[b_gratuity_removed] [bit] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED
(
[i_ticket_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ticket] WITH CHECK ADD CONSTRAINT [FK_Ticket_Employees] FOREIGN KEY([i_user_id])
REFERENCES [dbo].[Employees] ([i_employee_id])
GO
ALTER TABLE [dbo].[Ticket] CHECK CONSTRAINT [FK_Ticket_Employees]
GO
INSERT INTO Ticket(i_ticket_id,i_customer_id,s_table_name,c_items_total,c_taxes_total,c_discount_amount,c_grand_total,c_payment_total,c_auto_gratuity,f_auto_gratuity_pct,i_void_ticket_id,i_user_id,dt_create_time,dt_close_time,b_closed,dt_lock_time,i_lock_user_id,dt_cached_time,i_created_by_user_id,f_ticket_discount_factor,i_ticket_revision,i_ticket_actual_close_employee_id,c_ticket_tips,i_ticket_seq_number,c_ticket_display_subtotal,c_ticket_display_tax_total,i_ticket_timesheet_id,dt_ticket_promise_time,i_section_id,i_address_id,i_revcenter_id,manual_hold,choice_hold,preauth_amount,cover_count,g_ticket_id,rv_Ticket,dt_recent_guest_check_print,i_location_id,s_cached_xml,b_use_custom_status,s_custom_status,s_status,preauth_total_no_bar_tab,i_active_seat_count,dt_last_item_time,dt_last_payment_time,b_gratuity_removed) VALUES ('130299',1437,'McGowan, Casey',11.00,0.00,0.00,11.00,11.00,0.00,NULL,NULL,154,'2017-06-13 09:12:55.047','2017-06-13 09:18:23.370',1,NULL,NULL,NULL,154,0,0,133,NULL,44,11.00,0.00,16031,'1753-01-01 00:00:00.000',0,1437,2,0,0,0.00,1,'41AE8049-786A-4A63-8BF3-0119C62EBFF8',0x00000000010618D2,NULL,NULL,NULL,0,NULL,'FullyPaid',0.00,1,'2017-06-13 09:13:11.590','2017-06-13 09:18:22.083',0);
INSERT INTO Ticket(i_ticket_id,i_customer_id,s_table_name,c_items_total,c_taxes_total,c_discount_amount,c_grand_total,c_payment_total,c_auto_gratuity,f_auto_gratuity_pct,i_void_ticket_id,i_user_id,dt_create_time,dt_close_time,b_closed,dt_lock_time,i_lock_user_id,dt_cached_time,i_created_by_user_id,f_ticket_discount_factor,i_ticket_revision,i_ticket_actual_close_employee_id,c_ticket_tips,i_ticket_seq_number,c_ticket_display_subtotal,c_ticket_display_tax_total,i_ticket_timesheet_id,dt_ticket_promise_time,i_section_id,i_address_id,i_revcenter_id,manual_hold,choice_hold,preauth_amount,cover_count,g_ticket_id,rv_Ticket,dt_recent_guest_check_print,i_location_id,s_cached_xml,b_use_custom_status,s_custom_status,s_status,preauth_total_no_bar_tab,i_active_seat_count,dt_last_item_time,dt_last_payment_time,b_gratuity_removed) VALUES ('130305',2428,'thibault, kendra',5.00,0.00,0.00,5.00,5.00,0.00,NULL,NULL,154,'2017-06-13 09:31:04.827','2017-06-13 09:31:32.533',1,NULL,NULL,NULL,154,0,0,154,NULL,50,5.00,0.00,16031,'1753-01-01 00:00:00.000',0,2428,2,0,0,0.00,1,'DE7C5663-6479-4CDF-B854-E9EDAB476DBE',0x00000000010618D4,NULL,NULL,NULL,0,NULL,'FullyPaid',0.00,1,'2017-06-13 09:31:20.743','2017-06-13 09:31:31.967',0);
INSERT INTO Ticket(i_ticket_id,i_customer_id,s_table_name,c_items_total,c_taxes_total,c_discount_amount,c_grand_total,c_payment_total,c_auto_gratuity,f_auto_gratuity_pct,i_void_ticket_id,i_user_id,dt_create_time,dt_close_time,b_closed,dt_lock_time,i_lock_user_id,dt_cached_time,i_created_by_user_id,f_ticket_discount_factor,i_ticket_revision,i_ticket_actual_close_employee_id,c_ticket_tips,i_ticket_seq_number,c_ticket_display_subtotal,c_ticket_display_tax_total,i_ticket_timesheet_id,dt_ticket_promise_time,i_section_id,i_address_id,i_revcenter_id,manual_hold,choice_hold,preauth_amount,cover_count,g_ticket_id,rv_Ticket,dt_recent_guest_check_print,i_location_id,s_cached_xml,b_use_custom_status,s_custom_status,s_status,preauth_total_no_bar_tab,i_active_seat_count,dt_last_item_time,dt_last_payment_time,b_gratuity_removed) VALUES ('130307',2370,'Travers, Diana',22.00,0.00,0.00,22.00,22.00,0.00,NULL,NULL,154,'2017-06-13 09:40:28.587','2017-06-13 09:52:59.933',1,NULL,NULL,NULL,154,0,0,133,NULL,52,22.00,0.00,16031,'1753-01-01 00:00:00.000',0,2370,2,0,0,0.00,1,'A16E2ECC-1F03-464E-8AF7-FF9A6A359754',0x00000000010618D5,NULL,NULL,NULL,0,NULL,'FullyPaid',0.00,1,'2017-06-13 09:40:44.803','2017-06-13 09:52:58.230',0);
INSERT INTO Ticket(i_ticket_id,i_customer_id,s_table_name,c_items_total,c_taxes_total,c_discount_amount,c_grand_total,c_payment_total,c_auto_gratuity,f_auto_gratuity_pct,i_void_ticket_id,i_user_id,dt_create_time,dt_close_time,b_closed,dt_lock_time,i_lock_user_id,dt_cached_time,i_created_by_user_id,f_ticket_discount_factor,i_ticket_revision,i_ticket_actual_close_employee_id,c_ticket_tips,i_ticket_seq_number,c_ticket_display_subtotal,c_ticket_display_tax_total,i_ticket_timesheet_id,dt_ticket_promise_time,i_section_id,i_address_id,i_revcenter_id,manual_hold,choice_hold,preauth_amount,cover_count,g_ticket_id,rv_Ticket,dt_recent_guest_check_print,i_location_id,s_cached_xml,b_use_custom_status,s_custom_status,s_status,preauth_total_no_bar_tab,i_active_seat_count,dt_last_item_time,dt_last_payment_time,b_gratuity_removed) VALUES ('130310',481,'Cappuccilli, Allison',12.00,0.00,0.00,12.00,12.00,0.00,NULL,NULL,154,'2017-06-13 09:44:30.770','2017-06-13 09:44:45.660',1,NULL,NULL,NULL,154,0,0,154,NULL,55,12.00,0.00,16031,'1753-01-01 00:00:00.000',0,481,2,0,0,0.00,1,'043A2982-C78C-47C5-82E4-1C3605107A1C',0x00000000010618D6,NULL,NULL,NULL,0,NULL,'FullyPaid',0.00,1,'2017-06-13 09:44:41.630','2017-06-13 09:44:45.093',0);
INSERT INTO Ticket(i_ticket_id,i_customer_id,s_table_name,c_items_total,c_taxes_total,c_discount_amount,c_grand_total,c_payment_total,c_auto_gratuity,f_auto_gratuity_pct,i_void_ticket_id,i_user_id,dt_create_time,dt_close_time,b_closed,dt_lock_time,i_lock_user_id,dt_cached_time,i_created_by_user_id,f_ticket_discount_factor,i_ticket_revision,i_ticket_actual_close_employee_id,c_ticket_tips,i_ticket_seq_number,c_ticket_display_subtotal,c_ticket_display_tax_total,i_ticket_timesheet_id,dt_ticket_promise_time,i_section_id,i_address_id,i_revcenter_id,manual_hold,choice_hold,preauth_amount,cover_count,g_ticket_id,rv_Ticket,dt_recent_guest_check_print,i_location_id,s_cached_xml,b_use_custom_status,s_custom_status,s_status,preauth_total_no_bar_tab,i_active_seat_count,dt_last_item_time,dt_last_payment_time,b_gratuity_removed) VALUES ('130315',2583,'Simmons, Jesse',10.00,0.00,0.00,10.00,10.00,0.00,NULL,NULL,154,'2017-06-13 09:59:05.157','2017-06-13 09:59:23.130',1,NULL,NULL,NULL,154,0,0,154,NULL,60,10.00,0.00,16031,'1753-01-01 00:00:00.000',0,2583,2,0,0,0.00,1,'D620CCCB-F07E-4599-BB31-8D4A43AC0DF7',0x00000000010618D8,NULL,NULL,NULL,0,NULL,'FullyPaid',0.00,1,'2017-06-13 09:59:16.847','2017-06-13 09:59:22.413',0);
CREATE TABLE [dbo].[TicketItem](
[i_ticket_item_id] [int] IDENTITY(1,1) NOT NULL,
[i_ticket_id] [int] NOT NULL,
[i_menu_item_id] [int] NOT NULL,
[s_item] [nvarchar](50) NULL,
[dt_when] [datetime] NULL,
[c_price] [money] NULL,
[c_discount_amount] [money] NULL,
[c_tax_total] [money] NULL,
[i_employee_id] [int] NULL,
[i_cover_id] [int] NULL,
[i_void_item_id] [int] NULL,
[i_held] [int] NOT NULL CONSTRAINT [DF_TicketItem_b_hold] DEFAULT (0),
[b_printed] [bit] NULL,
[i_split_group] [int] NOT NULL CONSTRAINT [DF_TicketItem_i_split_group] DEFAULT (0),
[f_split] [float] NOT NULL CONSTRAINT [DF_TicketItem_f_split] DEFAULT (1),
[i_held_for_choice] [int] NULL CONSTRAINT [DF_TicketItem_b_held_for_choice] DEFAULT (0),
[s_qty] [char](9) NULL,
[c_ticketitem_choices_amount] [money] NULL,
[c_ticketitem_net_price] [money] NULL,
[f_ticketitem_ticket_discount_factor] [float] NULL,
[i_ticketitem_actual_employee_id] [int] NULL,
[c_ticketitem_tip_share] [money] NULL,
[f_ticketitem_share_qty] [float] NULL,
[f_ticketitem_real_qty] [float] NULL,
[i_ticketitem_timesheet_id] [int] NULL,
[c_ticketitem_gross_price] [money] NULL,
[c_ticketitem_manual_discounts] [money] NULL,
[c_ticketitem_auto_discounts] [money] NULL,
[i_ti_customer_id] [int] NULL,
[i_ti_void_ticket_id] [int] NULL,
[i_ti_customer_orig_id] [int] NULL,
[dt_ti_ticket_close_time] [datetime] NULL,
[i_ti_revcenter_id] [int] NULL,
[c_hidden_taxes] [money] NULL,
[i_course_id] [int] NULL,
[original_add_time] [datetime] NULL,
[s_applicable_ticket_discounts] [varchar](1024) NULL,
[s_applicable_ticket_taxes] [varchar](1024) NULL,
[s_special_request] [nvarchar](1024) NULL,
[g_ticketitem_id] [uniqueidentifier] NULL CONSTRAINT [df_ticketitem_g_local] DEFAULT (newid()),
[c_autograt_amount] [money] NULL,
[rv_TicketItem] [timestamp] NOT NULL,
[g_combination_guid] [uniqueidentifier] NULL,
CONSTRAINT [PK_TicketItem] PRIMARY KEY CLUSTERED
(
[i_ticket_item_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[TicketItem] WITH CHECK ADD CONSTRAINT [FK_TicketItem_Employees] FOREIGN KEY([i_employee_id])
REFERENCES [dbo].[Employees] ([i_employee_id])
GO
ALTER TABLE [dbo].[TicketItem] CHECK CONSTRAINT [FK_TicketItem_Employees]
GO
ALTER TABLE [dbo].[TicketItem] WITH CHECK ADD CONSTRAINT [FK_TicketItem_MenuItem] FOREIGN KEY([i_menu_item_id])
REFERENCES [dbo].[MenuItem] ([i_menu_item_id])
GO
ALTER TABLE [dbo].[TicketItem] CHECK CONSTRAINT [FK_TicketItem_MenuItem]
GO
ALTER TABLE [dbo].[TicketItem] WITH CHECK ADD CONSTRAINT [FK_TicketItem_Ticket] FOREIGN KEY([i_ticket_id])
REFERENCES [dbo].[Ticket] ([i_ticket_id])
GO
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579249,'130299',487,'PB Cheesecake','2017-06-13 09:13:13.247',9.00,0.00,NULL,154,74689,NULL,0,NULL,1,1,0,1,0.00,9.00,0,154,NULL,1,1,16031,9.00,0.00,0.00,1437,NULL,1437,'2017-06-13 09:18:23.370',2,0.00,0,'2017-06-13 09:13:07.277',NULL,NULL,NULL,'CE590790-EE52-4AFD-BDD1-3DAB44E38A95',0.00,0x0000000001061904,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579250,'130299',545,'Liftoff','2017-06-13 09:13:13.270',2.00,0.00,NULL,154,74689,NULL,0,NULL,2,1,0,1,0.00,2.00,0,154,NULL,1,1,16031,2.00,0.00,0.00,1437,NULL,1437,'2017-06-13 09:18:23.370',2,0.00,0,'2017-06-13 09:13:11.590',NULL,NULL,NULL,'00BBD07A-EBB5-4693-9A37-830BEB3EBF8D',0.00,0x0000000001061905,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579257,'130305',580,'Tea & Aloe','2017-06-13 09:31:24.767',3.00,0.00,NULL,154,74695,NULL,0,NULL,1,1,0,1,0.00,3.00,0,154,NULL,1,1,16031,3.00,0.00,0.00,2428,NULL,2428,'2017-06-13 09:31:32.533',2,0.00,0,'2017-06-13 09:31:08.573',NULL,NULL,NULL,'836BE7CD-0354-4C36-B5B8-13B575A044C0',0.00,0x0000000001061901,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579258,'130305',545,'Liftoff','2017-06-13 09:31:24.787',2.00,0.00,NULL,154,74695,NULL,0,NULL,2,1,0,1,0.00,2.00,0,154,NULL,1,1,16031,2.00,0.00,0.00,2428,NULL,2428,'2017-06-13 09:31:32.533',2,0.00,0,'2017-06-13 09:31:20.743',NULL,NULL,NULL,'F88027A3-E502-4BE3-9F97-F69A1C6F4561',0.00,0x0000000001061902,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579260,'130307',472,'Hannah Banana','2017-06-13 09:40:46.280',9.00,0.00,NULL,154,74697,NULL,0,NULL,1,1,0,1,0.00,9.00,0,154,NULL,1,1,16031,9.00,0.00,0.00,2370,NULL,2370,'2017-06-13 09:52:59.933',2,0.00,0,'2017-06-13 09:40:32.790',NULL,NULL,NULL,'4650A4C2-2BEE-405B-92AA-66C350A3AA0D',0.00,0x00000000010618FC,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579261,'130307',467,'French Toast','2017-06-13 09:40:46.310',9.00,0.00,NULL,154,74697,NULL,0,NULL,2,1,0,1,0.00,9.00,0,154,NULL,1,1,16031,9.00,0.00,0.00,2370,NULL,2370,'2017-06-13 09:52:59.933',2,0.00,0,'2017-06-13 09:40:35.040',NULL,NULL,NULL,'E66BC557-D56B-4620-B915-04F262981E6F',0.00,0x00000000010618FD,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579262,'130307',545,'Liftoff','2017-06-13 09:40:46.337',2.00,0.00,NULL,154,74697,NULL,0,NULL,3,1,0,1,0.00,2.00,0,154,NULL,1,1,16031,2.00,0.00,0.00,2370,NULL,2370,'2017-06-13 09:52:59.933',2,0.00,0,'2017-06-13 09:40:43.243',NULL,NULL,NULL,'67E15C56-72E7-4249-9966-FCBB0F17F7BE',0.00,0x00000000010618FE,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579263,'130307',545,'Liftoff','2017-06-13 09:40:46.353',2.00,0.00,NULL,154,74697,NULL,0,NULL,4,1,0,1,0.00,2.00,0,154,NULL,1,1,16031,2.00,0.00,0.00,2370,NULL,2370,'2017-06-13 09:52:59.933',2,0.00,0,'2017-06-13 09:40:44.803',NULL,NULL,NULL,'C5793AD2-BCD4-4DD2-ACB8-6BAC7ABF0DF1',0.00,0x00000000010618FF,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579270,'130310',581,'Rebuild Strength Plain','2017-06-13 09:44:43.213',10.00,0.00,NULL,154,74700,NULL,0,NULL,1,1,0,1,0.00,10.00,0,154,NULL,1,1,16031,10.00,0.00,0.00,481,NULL,481,'2017-06-13 09:44:45.660',2,0.00,0,'2017-06-13 09:44:37.567',NULL,NULL,NULL,'44B2CF22-B62D-4D89-AA83-F3A08FFA308A',0.00,0x00000000010618FA,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579271,'130310',545,'Liftoff','2017-06-13 09:44:43.367',2.00,0.00,NULL,154,74700,NULL,0,NULL,2,1,0,1,0.00,2.00,0,154,NULL,1,1,16031,2.00,0.00,0.00,481,NULL,481,'2017-06-13 09:44:45.660',2,0.00,0,'2017-06-13 09:44:41.630',NULL,NULL,NULL,'C7DEBB82-D8D3-46A5-8AE3-D22AC5E14BB0',0.00,0x00000000010618FB,'00000000-0000-0000-0000-000000000000');
INSERT INTO TicketItem(i_ticket_item_id,i_ticket_id,i_menu_item_id,s_item,dt_when,c_price,c_discount_amount,c_tax_total,i_employee_id,i_cover_id,i_void_item_id,i_held,b_printed,i_split_group,f_split,i_held_for_choice,s_qty,c_ticketitem_choices_amount,c_ticketitem_net_price,f_ticketitem_ticket_discount_factor,i_ticketitem_actual_employee_id,c_ticketitem_tip_share,f_ticketitem_share_qty,f_ticketitem_real_qty,i_ticketitem_timesheet_id,c_ticketitem_gross_price,c_ticketitem_manual_discounts,c_ticketitem_auto_discounts,i_ti_customer_id,i_ti_void_ticket_id,i_ti_customer_orig_id,dt_ti_ticket_close_time,i_ti_revcenter_id,c_hidden_taxes,i_course_id,original_add_time,s_applicable_ticket_discounts,s_applicable_ticket_taxes,s_special_request,g_ticketitem_id,c_autograt_amount,rv_TicketItem,g_combination_guid) VALUES (579279,'130315',580,'Tea & Aloe','2017-06-13 09:59:18.470',3.00,0.00,NULL,154,74705,NULL,0,NULL,2,1,0,1,0.00,3.00,0,154,NULL,1,1,16031,3.00,0.00,0.00,2583,NULL,2583,'2017-06-13 09:59:23.130',2,0.00,0,'2017-06-13 09:59:12.083',NULL,NULL,NULL,'6FE0ACB8-C63D-46DE-AD27-F9E5A2008D1C',0.00,0x00000000010618F6,'00000000-0000-0000-0000-000000000000');
CREATE TABLE [dbo].[ChoiceItem](
[i_choice_item_id] [int] IDENTITY(1,1) NOT NULL,
[i_choice_id] [int] NOT NULL,
[i_ticket_item_id] [int] NULL,
[i_employee_id] [int] NOT NULL,
[dt_when] [datetime] NOT NULL,
[s_choice_name] [nvarchar](50) NOT NULL,
[m_choice_price_mod] [money] NOT NULL CONSTRAINT [DF_ChoiceItem_m_choice_price_mod] DEFAULT (0),
[m_choiceitem_price] [money] NULL CONSTRAINT [DF_ChoiceItem_m_choiceitem_price] DEFAULT ((0)),
[i_choiceitem_timesheet_id] [int] NULL,
[i_choiceitem_parent_cci_id] [int] NULL CONSTRAINT [DF_ChoiceItem_i_choiceitem_parent_cci_id] DEFAULT ((0)),
[i_choiceitem_ordinal] [int] NULL CONSTRAINT [DF_ChoiceItem_i_choiceitem_ordinal] DEFAULT ((0)),
[i_choiceitem_cc_id] [int] NULL CONSTRAINT [DF_ChoiceItem_i_choiceitem_cc_id] DEFAULT ((0)),
[dt_added] [datetime] NULL,
[m_ci_quantity] [money] NULL,
[m_ci_original_quantity] [money] NULL,
[rv_ChoiceItem] [timestamp] NOT NULL,
[g_ChoiceItem_id] [uniqueidentifier] NULL CONSTRAINT [df_ChoiceItem_g_local] DEFAULT (newid()),
[m_ci_original_share_quantity] [decimal](18, 0) NULL,
CONSTRAINT [PK_ChoiceItem] PRIMARY KEY CLUSTERED
(
[i_choice_item_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChoiceItem] WITH NOCHECK ADD CONSTRAINT [FK_ChoiceItem_Choices] FOREIGN KEY([i_choice_id])
REFERENCES [dbo].[Choices] ([i_choice_id])
GO
ALTER TABLE [dbo].[ChoiceItem] CHECK CONSTRAINT [FK_ChoiceItem_Choices]
GO
ALTER TABLE [dbo].[ChoiceItem] WITH CHECK ADD CONSTRAINT [FK_ChoiceItem_Employees] FOREIGN KEY([i_employee_id])
REFERENCES [dbo].[Employees] ([i_employee_id])
GO
ALTER TABLE [dbo].[ChoiceItem] CHECK CONSTRAINT [FK_ChoiceItem_Employees]
GO
ALTER TABLE [dbo].[ChoiceItem] WITH CHECK ADD CONSTRAINT [FK_ChoiceItem_TicketItem] FOREIGN KEY([i_ticket_item_id])
REFERENCES [dbo].[TicketItem] ([i_ticket_item_id])
GO
ALTER TABLE [dbo].[ChoiceItem] CHECK CONSTRAINT [FK_ChoiceItem_TicketItem]
GO
ALTER TABLE [dbo].[TicketItem] CHECK CONSTRAINT [FK_TicketItem_Ticket]
GO
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163057,134,579249,154,'2017-06-13 09:13:13.250','Pomegranate Green Tea Cold',0.00,0.00,NULL,369552,1,0,'2017-06-13 09:13:09.200',1.00,1.00,0x000000000105F795,'FEC891C7-65F7-4B06-A5DF-74D8BA1F84BA',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163058,120,579249,154,'2017-06-13 09:13:13.253','Mango Aloe Shot',0.00,0.00,NULL,369553,1,0,'2017-06-13 09:13:09.760',1.00,1.00,0x000000000105F797,'29DB58B5-03A1-483E-B253-C95B7C6BE520',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163059,107,579250,154,'2017-06-13 09:13:13.273','Orange Liftoff',0.00,0.00,NULL,369557,1,0,'2017-06-13 09:13:12.353',1.00,1.00,0x000000000105F7A0,'5CA7DCFC-750B-4BAA-AE80-4773B89051E2',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163073,136,579257,154,'2017-06-13 09:31:24.770','NRG Tea Cold',0.00,0.00,NULL,369584,1,0,'2017-06-13 09:31:12.697',1.00,1.00,0x000000000105FB5D,'1AB89159-941B-42F2-9C36-11A6B026EBA3',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163074,120,579257,154,'2017-06-13 09:31:24.773','Mango Aloe Shot',0.00,0.00,NULL,369585,1,0,'2017-06-13 09:31:15.557',1.00,1.00,0x000000000105FB5F,'ED8C7315-C378-4EE3-A303-8B79EDDC8DDE',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163075,110,579258,154,'2017-06-13 09:31:24.790','Tropical Fruit Liftoff',0.00,0.00,NULL,369586,1,0,'2017-06-13 09:31:22.947',1.00,1.00,0x000000000105FB65,'78648378-53EC-4E34-8B29-9FB6DFED4A49',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163077,130,579260,154,'2017-06-13 09:40:46.287','Lemon Tea Cold',0.00,0.00,NULL,369592,1,0,'2017-06-13 09:40:37.587',1.00,1.00,0x000000000105FDEE,'DA55D94C-FFC1-4F4F-A8B7-B9918C180124',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163078,120,579260,154,'2017-06-13 09:40:46.290','Mango Aloe Shot',0.00,0.00,NULL,369593,1,0,'2017-06-13 09:40:38.133',1.00,1.00,0x000000000105FDF0,'1C973228-7B0A-4EF8-85A2-76D4EEB8115D',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163079,130,579261,154,'2017-06-13 09:40:46.317','Lemon Tea Cold',0.00,0.00,NULL,369597,1,0,'2017-06-13 09:40:40.960',1.00,1.00,0x000000000105FDF9,'DA2AB9E7-9BA2-42C4-9E2E-99DE8953814B',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163080,159,579261,154,'2017-06-13 09:40:46.320','Mandarin Aloe Shot',0.00,0.00,NULL,369598,1,0,'2017-06-13 09:40:41.493',1.00,1.00,0x000000000105FDFB,'CA0A9C73-A3A4-451E-9F2F-645D83F254C7',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163081,109,579262,154,'2017-06-13 09:40:46.343','Lemon Lime Liftoff',0.00,0.00,NULL,369602,1,0,'2017-06-13 09:40:44.180',1.00,1.00,0x000000000105FE05,'A3A9FAFD-2C83-4E6C-88CA-EFCC88AB05F1',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163082,109,579263,154,'2017-06-13 09:40:46.360','Lemon Lime Liftoff',0.00,0.00,NULL,369603,1,0,'2017-06-13 09:40:45.320',1.00,1.00,0x000000000105FE0B,'F9C43ED6-0655-4AEC-B084-4EDC7CC99ACE',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163092,129,579270,154,'2017-06-13 09:44:43.250','Raspberry Tea Cold',0.00,0.00,NULL,369622,1,0,'2017-06-13 09:44:39.410',1.00,1.00,0x00000000010600EA,'840BA3D3-49E1-4F20-BF95-2520A90F8E6E',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163093,119,579270,154,'2017-06-13 09:44:43.250','Cranberry Aloe Shot',0.00,0.00,NULL,369623,1,0,'2017-06-13 09:44:39.863',1.00,1.00,0x00000000010600EC,'02149297-9309-436E-B08C-B6D4C1B5DE67',1);
INSERT INTO ChoiceItem(i_choice_item_id,i_choice_id,i_ticket_item_id,i_employee_id,dt_when,s_choice_name,m_choice_price_mod,m_choiceitem_price,i_choiceitem_timesheet_id,i_choiceitem_parent_cci_id,i_choiceitem_ordinal,i_choiceitem_cc_id,dt_added,m_ci_quantity,m_ci_original_quantity,rv_ChoiceItem,g_ChoiceItem_id,m_ci_original_share_quantity) VALUES (163094,107,579271,154,'2017-06-13 09:44:43.377','Orange Liftoff',0.00,0.00,NULL,369626,1,0,'2017-06-13 09:44:42.380',1.00,1.00,0x00000000010600F4,'39B20EA3-79D6-44C5-B4FF-1E81E25CC611',1);
CREATE TABLE [dbo].[Employees](
[i_employee_id] [int] IDENTITY(27,1) NOT NULL,
[s_name] [varchar](50) NULL,
[s_login] [nvarchar](128) NULL,
[b_active] [bit] NOT NULL CONSTRAINT [DF_Employees_b_active] DEFAULT (1),
[b_admin] [bit] NOT NULL CONSTRAINT [DF_Employees_b_admin] DEFAULT (0),
[b_is_clocked_in] [bit] NOT NULL CONSTRAINT [DF_Employees_b_is_clocked_in] DEFAULT (0),
[b_is_logged_in] [bit] NOT NULL CONSTRAINT [DF_Employees_b_is_logged_in] DEFAULT (0),
[dt_clock_in] [datetime] NULL,
[dt_expected_clock_out] [datetime] NULL,
[i_employ_id] [nvarchar](10) NULL,
[s_phone1] [varchar](15) NULL,
[s_phone2] [varchar](15) NULL,
[s_emerg_phone] [varchar](15) NULL,
[s_SSN] [varchar](15) NULL,
[dt_hire] [datetime] NULL,
[dt_term] [datetime] NULL,
[dt_review] [datetime] NULL,
[dt_birth] [datetime] NULL,
[s_first] [nvarchar](30) NULL,
[s_middle] [nvarchar](30) NULL,
[s_last] [nvarchar](50) NULL,
[s_street] [nvarchar](50) NULL,
[s_street2] [nvarchar](50) NULL,
[s_city] [nvarchar](50) NULL,
[s_state] [nvarchar](50) NULL,
[s_postal] [nvarchar](15) NULL,
[s_email] [nvarchar](100) NULL,
[s_employee_cardswipe] [varchar](256) NULL,
[i_employee_ordinal] [int] NULL,
[i_employees_current_timesheet_id] [int] NULL,
[i_employee_ticketlist_schema] [int] NULL CONSTRAINT [DF_Employees_i_employee_ticketlist_schema] DEFAULT ((0)),
[dt_employee_liquor_card_exp_date] [datetime] NULL,
[dt_employee_health_card_exp_date] [datetime] NULL,
[s_employee_payroll_id] [nvarchar](50) NULL,
[s_password_hash] [varchar](128) NULL,
[s_password_salt] [varchar](24) NULL,
[dt_password_changed] [datetime] NULL,
[i_authentication_failures] [int] NULL,
[dt_lockout] [datetime] NULL,
[i_security_template_id] [int] NULL,
[rv_Employees] [timestamp] NOT NULL,
[g_Employees_id] [uniqueidentifier] NULL CONSTRAINT [df_Employees_g_local] DEFAULT (newid()),
[i_external_id] [int] NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[i_employee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT INTO Employees(i_employee_id,s_name,s_login,b_active,b_admin,b_is_clocked_in,b_is_logged_in,dt_clock_in,dt_expected_clock_out,i_employ_id,s_phone1,s_phone2,s_emerg_phone,s_SSN,dt_hire,dt_term,dt_review,dt_birth,s_first,s_middle,s_last,s_street,s_street2,s_city,s_state,s_postal,s_email,s_employee_cardswipe,i_employee_ordinal,i_employees_current_timesheet_id,i_employee_ticketlist_schema,dt_employee_liquor_card_exp_date,dt_employee_health_card_exp_date,s_employee_payroll_id,s_password_hash,s_password_salt,dt_password_changed,i_authentication_failures,dt_lockout,i_security_template_id,rv_Employees,g_Employees_id,i_external_id) VALUES (154,'Mat Moreira',7298,1,0,1,0,'2017-06-13 11:05:28.993',NULL,0,NULL,NULL,NULL,NULL,'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000','1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,15,16035,15837,'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',NULL,NULL,NULL,NULL,NULL,NULL,3,0x000000000106608E,'4BD64193-EB71-4617-A001-029F95B40D8D',NULL);
CREATE TABLE [dbo].[Customers](
[cust_id] [int] IDENTITY(1,1) NOT NULL,
[cust_added] [datetime] NULL,
[cust_edited] [datetime] NULL,
[cust_fname] [nvarchar](50) NULL,
[cust_lname] [nvarchar](50) NULL,
[cust_phone] [varchar](30) NULL,
[cust_email] [varchar](255) NULL,
[cust_email_two] [varchar](255) NULL,
[cust_dob] [datetime] NULL,
[cust_anniversary] [datetime] NULL,
[cust_notes] [nvarchar](max) NULL,
[cust_active] [bit] NULL CONSTRAINT [DF_cust_active] DEFAULT ((1)),
[cust_fullname] AS (case when [cust_lname] IS NULL then '' when [cust_fname] IS NULL then [cust_lname] else ([cust_lname]+' ')+[cust_fname] end),
[cust_added_by_user] [int] NULL,
[cust_last_edit_by_user] [int] NULL,
[cust_original_id] [int] NULL,
[cust_superceded_by_id] [int] NULL,
[cust_current_id] [int] NULL,
[cust_phone_prefix] AS (case when len([cust_phone])>(6) then substring([cust_phone],(4),(3)) else '' end),
[cust_phone_last_four] AS (case when len([cust_phone])>=(4) then substring([cust_phone],len([cust_phone])-(3),(4)) else '' end),
[cust_phone_last_seven] AS (case when len([cust_phone])>(7) then substring([cust_phone],len([cust_phone])-(6),(7)) else '' end),
[cust_membership_id] [nvarchar](50) NULL,
[cust_membership_cardinfo] [nvarchar](255) NULL,
[cust_callerID] [nvarchar](30) NULL,
[g_customers_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_customers_g_customers_id] DEFAULT (newid()),
[rv_Customers] [timestamp] NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY NONCLUSTERED
(
[cust_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT INTO Customers(cust_id,cust_added,cust_edited,cust_fname,cust_lname,cust_phone,cust_email,cust_email_two,cust_dob,cust_anniversary,cust_notes,cust_active,cust_fullname,cust_added_by_user,cust_last_edit_by_user,cust_original_id,cust_superceded_by_id,cust_current_id,cust_phone_prefix,cust_phone_last_four,cust_phone_last_seven,cust_membership_id,cust_membership_cardinfo,cust_callerID,g_customers_id,rv_Customers) VALUES (481,'2015-04-15 09:10:35.893','2017-02-16 10:22:56.507','Allison','Cappuccilli',4018593434,NULL,NULL,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','Hannah Snow',1,'Cappuccilli Allison',111,161,481,NULL,481,859,3434,8593434,NULL,NULL,NULL,'E8645025-41AC-4574-8521-982C6F0B9D2C',0x0000000000BEE1CC);
INSERT INTO Customers(cust_id,cust_added,cust_edited,cust_fname,cust_lname,cust_phone,cust_email,cust_email_two,cust_dob,cust_anniversary,cust_notes,cust_active,cust_fullname,cust_added_by_user,cust_last_edit_by_user,cust_original_id,cust_superceded_by_id,cust_current_id,cust_phone_prefix,cust_phone_last_four,cust_phone_last_seven,cust_membership_id,cust_membership_cardinfo,cust_callerID,g_customers_id,rv_Customers) VALUES (1437,'2015-04-22 12:31:15.540','2017-04-03 13:43:45.133','Casey','McGowan',4014470116,'caseymcgowan2@yahoo.com',NULL,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','Hannah Snow',1,'McGowan Casey',119,153,1437,NULL,1437,447,0116,4470116,NULL,NULL,NULL,'C17F4BE2-B16C-4912-85D0-F8C18E8E02A6',0x0000000000DCA52C);
INSERT INTO Customers(cust_id,cust_added,cust_edited,cust_fname,cust_lname,cust_phone,cust_email,cust_email_two,cust_dob,cust_anniversary,cust_notes,cust_active,cust_fullname,cust_added_by_user,cust_last_edit_by_user,cust_original_id,cust_superceded_by_id,cust_current_id,cust_phone_prefix,cust_phone_last_four,cust_phone_last_seven,cust_membership_id,cust_membership_cardinfo,cust_callerID,g_customers_id,rv_Customers) VALUES (2370,'2016-04-10 14:24:23.620','2017-03-28 09:33:24.987','Diana','Travers',4018550367,'cycling0701@me.com',NULL,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','Distributor',1,'Travers Diana',132,150,2370,NULL,2370,855,0367,8550367,NULL,NULL,NULL,'399A2D34-DDEB-4C20-97F1-3265EA3AF127',0x0000000000D96038);
INSERT INTO Customers(cust_id,cust_added,cust_edited,cust_fname,cust_lname,cust_phone,cust_email,cust_email_two,cust_dob,cust_anniversary,cust_notes,cust_active,cust_fullname,cust_added_by_user,cust_last_edit_by_user,cust_original_id,cust_superceded_by_id,cust_current_id,cust_phone_prefix,cust_phone_last_four,cust_phone_last_seven,cust_membership_id,cust_membership_cardinfo,cust_callerID,g_customers_id,rv_Customers) VALUES (2428,'2016-04-21 08:44:22.090','2017-05-13 09:35:09.537','kendra','thibault',4014809337,'kendra.leigh123@aol.com',NULL,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000',NULL,1,'thibault kendra',147,159,2428,NULL,2428,480,9337,4809337,NULL,NULL,NULL,'AB88F9D7-87F0-4C06-A030-C8A00174FCBE',0x0000000000F31C46);
June 14, 2017 at 4:28 pm
As I understand it, your point is that for Rows with a Row#>1, you don't want values in any of the columns except for Choice Added?
If so, that really should be handled at the presentation layer.
You was so correct! I was able to filter the data I did not need at the Report Workbench!
Thank you to anyone who gave a moment to chime in!
Chris
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply