March 8, 2015 at 9:06 pm
Hello everyone. I will do my best to give all the info needed for assistance by some guidelines from the Mods and others who have been long standing member's here. 🙂
I have a query that for some reason is giving me a NULL value return.
Background on this project: I am writing a custom report for a client, we are in the Point of Sale business. I am the technical director of the company, but I am hardly a true SQL expert, hence why I am asking for advice on this code.
Caveat:[/u] The WHERE clause cannot be used in writing this Query as it is used to determine the time span of the report by a line called WHERE %PARAMS%, where PARAMS is the date range of the report being queried from the UI of the POS software.
Scope: The client wishes to have a custom report that returns the values of: Total Sales , Covers (# of Patrons), and the Avg $ Amount per Cover.
The total sales are derived from what we call 'Revenue Centers' & 'Day Parts' in a restaurant.
A revenue center is any location, could be 'Bar', or Dining Room, or 'Patio', or a 'Lounge'...I am certain you get the idea.
Now, a 'Day Part', in the POS software we sell (Dinerware), is broken down into 4 'parts', by default.
The 4 'Day Parts' and their times are:
Breakfast (4am - 10am)
Lunch (10am - 4pm)
Dinner (4pm - 10pm)
Late Night (10pm - 4am)
However...
This location has had me edit the DayParts to:
Breakfast (4am - 10am)
Lunch (10am - 3pm) <--
Dinner (3pm - 10pm) <--
Late Night (10pm - 4am)
The business 'day' in the Dinerware software is from 4am to 4am, by default...due to many clubs and venues that stay open beyond Midnight (this value can also be changed in the UI of the software).
The code:
SELECT
'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
SUM(t.c_items_total) / SUM(t.cover_count) as AvgPer_Cover
FROM
dbo.Ticket AS t JOIN
dbo.PeriodDefinition AS pd ON CAST(t.dt_close_time AS time) BETWEEN CAST(pd.dt_start AS time) AND CAST(pd.dt_finish AS time) AND
pd.i_period_definition_id IN(1, 2, 3, 4) and t.i_revcenter_id = 7 and t.i_void_ticket_id IS NULL and t.b_closed = 1 and t.c_items_total > 0
WHERE %PARAMS%
This is only one part but all parts of the code include the similar code past the FROM statement.
Issue: DayPart (i_period_definition_id) = 4 is returning a NULL value and I cannot figure out why. DayParts IN(1, 2, 3) all return the data the are supposed to. But 4, will not, it returns NULL. i_period_definition_id # 4 is the 'Late Night' (10pm - 4am) DayPart. So this would return the amount of sales from Tickets closed after 10pm.
There are 3 Tables involved to bring this data alive: Ticket, PeroidDefinition, RevenueCenters
Expected Revenue Output on the Custom Report:
Banquets - All Day, Any revenue center
Bar - All Day, only Bar Revenue Center
Lunch - From 10am to 3pm, Lounge & Dining Revenue Centers (i_revcenter_id IN(3, 8) Dining & Loung) (i_period_definition_id = 1, Lunch)
Dining - After 3pm til close, (i_revcenter_id = 3) (i_period_definition_id IN(1, 2, 4)
Lounge - After 3pm til Close, (i_revcenter_id = 8) (i_period_definition_id IN(1, 2, 4)
RevenueCenters Table
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#RevenueCenters','U') IS NOT NULL
DROP TABLE #RevenueCenters
--===== Create the test table with
CREATE TABLE #RevenueCenters
(
revcent_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
revcenter_name nvchar(255),
revcent_ordinal int,
revcent_active bit,
rv_RevenueCenters timestamp,
g_RevenueCenters_id uniqueidentifier
)
SET IDENTITY_INSERT #RevenueCenters ON
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT YMD;
GO
DECLARE @datevar datetime2 = '1899-12-30 10:00:00.000';
GO
SELECT 'SELECT '
+ QUOTENAME(revcent_id,'''')+','
+ QUOTENAME(revcenter_name,'''')+','
+ QUOTENAME(revcent_ordinal,'''')+','
+ QUOTENAME(revcent_active,'''')+','
+ QUOTENAME(rv_RevenueCenters,'''')+','
+ QUOTENAME(g_RevenueCenters_id,'''')
+ ' UNION ALL'
FROM RevenueCenters
TEST DATA:
SELECT '2', 'Bar', '2', '1', '0x0000000002D05683', 'A447DD6E-7EC6-45D0-B3A0-C1F9A6BA21A2' UNION ALL
SELECT '3', 'Dining Room', '3', '1' '0x0000000002D05684', 559C89D2-4956-433A-B3EB-16889662E943' UNION ALL
SELECT '4', 'Private Events', '4', '0' '0x0000000002D05685', 5968DC4F-2E66-457C-97CC-050986D62B5D' UNION ALL
SELECT '5', 'Take Out', '5' '1', '0x0000000002D05686', '89252675-70DE-4595-84E4-E982B6436542' UNION ALL
SELECT '6', 'Retail', '6' '1', '0x0000000002D05687', 'D717A02F-FB97-4B40-93AD-723F3C03A6DD' UNION ALL
SELECT '7', 'Banquet', '7' '1', '0x0000000002D05688', '954387F3-04A8-4C23-BAD9-61369E4C8EDD' UNION ALL
SELECT '8', 'Lounge', '8' '1', '0x0000000002D05689', '0D4ACADC-F128-4CF5-BF8A-B1974684F901'
Ticket Table
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Ticket','U') IS NOT NULL
DROP TABLE #Ticket
--===== Create the test table with
CREATE TABLE #Ticket
(
i_ticket_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
i_customer_id int,
s_table_name nvarchar(50),
c_items_total money,
c_taxes_total money,
c_discount_amount money,
c_grand_total money,
c_payment_total money,
c_auto_gratuity money,
f_auto_gratuity_pct float,
i_void_ticket_id int,
i_user_id int,
dt_create_time datetime,
dt_close_time datetime,
b_closed bit,
dt_lock_time datetime,
i_lock_user_id int,
dt_cached_time datetime,
i_created_by_user_id int,
f_ticket_discount_factor float,
i_ticket_revision int,
i_ticket_actual_close_employee_id int,
c_ticket_tips money,
i_ticket_seq_number int,
c_ticket_display_subtotal money,
c_ticket_display_tax_total money,
i_ticket_timesheet_id int,
dt_ticket_promise_time datetime,
i_section_id int,
i_address_id int,
i_revcenter_id int,
manual_hold bit,
choice_hold bit,
preauth_amount money,
cover_count int,
s_cached_xml nvarchar(MAX),
g_ticket_id uniqueidentifier,
rv_Ticket timestamp,
dt_recent_guest_check_print datetime,
i_location_id int,
s_custom_status nvarchar(50),
s_status nvarchar(50),
preauth_total_no_bar_tab money,
dt_last_item_time datetime,
dt_last_payment_time datetime
)
SET IDENTITY_INSERT #Ticket ON
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT YMD;
GO
DECLARE @datevar datetime2 = '1899-12-30 10:00:00.000';
GO
SELECT 'SELECT '
+ QUOTENAME(i_ticket_id,'''')+','
+ QUOTENAME(i_customer_id,'''')+','
+ QUOTENAME(s_table_name,'''')+','
+ QUOTENAME(c_items_total,'''')+','
+ QUOTENAME(c_taxes_total,'''')+','
+ QUOTENAME(c_discount_amount,'''')+','
+ QUOTENAME(c_grand_total,'''')+','
+ QUOTENAME(c_payment_total,'''')+','
+ QUOTENAME(c_auto_gratuity,'''')+','
+ QUOTENAME(f_auto_gratuity_pct,'''')
+ QUOTENAME(i_void_ticket_id,'''')+','
+ QUOTENAME(i_user_id,'''')+','
+ QUOTENAME(dt_create_time,'''')+','
+ QUOTENAME(dt_close_time,'''')+','
+ QUOTENAME(b_closed,'''')+','
+ QUOTENAME(dt_lock_time,'''')+','
+ QUOTENAME(i_lock_user_id,'''')+','
+ QUOTENAME(dt_cached_time,'''')+','
+ QUOTENAME(i_created_by_user_id,'''')+','
+ QUOTENAME(f_ticket_discount_factor,'''')+','
+ QUOTENAME(i_ticket_revision,'''')+','
+ QUOTENAME(i_ticket_actual_close_employee_id,'''')+','
+ QUOTENAME(c_ticket_tips,'''')+','
+ QUOTENAME(i_ticket_seq_number,'''')+','
+ QUOTENAME(c_ticket_display_subtotal,'''')
+ QUOTENAME(c_ticket_display_tax_total,'''')+','
+ QUOTENAME(i_ticket_timesheet_id,'''')+','
+ QUOTENAME(dt_ticket_promise_time,'''')+','
+ QUOTENAME(i_section_id,'''')+','
+ QUOTENAME(i_address_id,'''')+','
+ QUOTENAME(i_revcenter_id,'''')+','
+ QUOTENAME(manual_hold,'''')+','
+ QUOTENAME(choice_hold,'''')+','
+ QUOTENAME(preauth_amount,'''')+','
+ QUOTENAME(cover_count,'''')+','
+ QUOTENAME(s_cached_xml,'''')+','
+ QUOTENAME(g_ticket_id,'''')+','
+ QUOTENAME(rv_Ticket,'''')+','
+ QUOTENAME(dt_recent_guest_check_print,'''')+','
+ QUOTENAME(i_location_id,'''')+','
+ QUOTENAME(b_use_custom_status,'''')+','
+ QUOTENAME(s_custom_status,'''')+','
+ QUOTENAME(s_status,'''')+','
+ QUOTENAME(preauth_total_no_bar_tab,'''')+','
+ QUOTENAME(i_active_seat_count,'''')+','
+ QUOTENAME(dt_last_item_time,'''')+','
+ QUOTENAME(dt_last_payment_time,'''')
+ ' UNION ALL'
FROM Ticket
PeriodDefinition Table
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#PeriodDefinition','U') IS NOT NULL
DROP TABLE #PeriodDefinition
--===== Create the test table with
CREATE TABLE #PeriodDefinition
(
i_period_definition_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
s_name char(50),
b_ignore_dayofweek bit,
b_ignore_year bit,
b_start_of_day_is_start bit,
b_end_of_day_is_end bit,
dt_start datetime,
dt_finish datetime,
s_weekdayssunday1 char(7),
b_std_day_part bit,
std_day_part_ordinal int,
i_period_definition_revcent_id int,
rv_PeriodDefinition timestamp,
g_PeriodDefinition_id uniqueidentifier
)
SET IDENTITY_INSERT #PeriodDefinition ON
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT YMD;
GO
DECLARE @datevar datetime2 = '1899-12-30 10:00:00.000';
GO
SELECT 'SELECT '
+ QUOTENAME(i_period_definition_id,'''')+','
+ QUOTENAME(s_name,'''')+','
+ QUOTENAME(b_ignore_dayofweek,'''')+','
+ QUOTENAME(b_ignore_year,'''')+','
+ QUOTENAME(b_start_of_day_is_start,'''')+','
+ QUOTENAME(b_end_of_day_is_end,'''')+','
+ QUOTENAME(dt_start,'''')+','
+ QUOTENAME(dt_finish,'''')+','
+ QUOTENAME(s_weekdayssunday1,'''')+','
+ QUOTENAME(b_std_day_part,'''')+','
+ QUOTENAME(std_day_part_ordinal,'''')+','
+ QUOTENAME(i_period_definition_revcent_id,'''')+','
+ QUOTENAME(rv_PeriodDefinition,'''')+','
+ QUOTENAME(g_PeriodDefinition_id,'''')
+ ' UNION ALL'
FROM PeriodDefinition
SELECT '1' 'Lunch' '1' '1' '0' '0' '1899-12-30 10:00:00.000' '1899-12-30 15:00:00.000' '1234567' '1' '2' 'NULL' '0x000000000031EBBD' '1B774ABA-E577-4BD2-AA3D-E55555E297E6' UNION ALL
SELECT '2' 'Dinner' '1' '1' '0' '0' '1899-12-30 15:00:00.000' '1899-12-30 22:00:00.000' '1234567' '1' '3' 'NULL' '0x000000000031EBBE' '7DCF6550-AC4E-45C1-AB6C-E60D86EB0C52' UNION ALL
SELECT '3' 'Breakfast' '1' '1' '1' '0' '1899-12-30 04:00:00.000' '1899-12-30 10:00:00.000' '1234567' '1' '1' 'NULL' '0x0000000000005CD1' '7867235B-4AB2-42AE-B028-C640EF52EB08' UNION ALL
SELECT '4' 'Late Night' '1' '1' '0' '1' '1899-12-30 22:00:00.000' '1899-12-30 04:00:00.000' '1234567' '1' '4' 'NULL' '0x0000000000005CD2' '370C8A6E-CB16-4382-B6AC-A1A6C99CFA59' UNION ALL
SELECT '5' '0:00' '1' '1' '0' '0' '1900-01-01 00:00:00.000' '1900-01-01 01:00:00.000' '1234567' '0' '100' 'NULL' '0x0000000000005CD3' 'AE1FE8EF-4BE9-4B49-B189-696A564DBE23' UNION ALL
SELECT '6' '1:00' '1' '1' '0' '0' '1900-01-01 01:00:00.000' '1900-01-01 02:00:00.000' '1234567' '0' '101' 'NULL' '0x0000000000005CD4' 'CEE63FCD-74D5-4C0A-BCA8-8B286C8B209F' UNION ALL
SELECT '7' '2:00' '1' '1' '0' '0' '1900-01-01 02:00:00.000' '1900-01-01 03:00:00.000' '1234567' '0' '102' 'NULL' '0x0000000000005CD5' 'EBDB9700-E9E9-40DE-8FAD-051AC35F44C2' UNION ALL
SELECT '8' '3:00' '1' '1' '0' '0' '1900-01-01 03:00:00.000' '1900-01-01 04:00:00.000' '1234567' '0' '103' 'NULL' '0x0000000000005CD6' '48E08108-53E7-4EDD-A7B1-7159AD91C36C' UNION ALL
SELECT '9' '4:00' '1' '1' '0' '0' '1900-01-01 04:00:00.000' '1900-01-01 05:00:00.000' '1234567' '0' '104' 'NULL' '0x0000000000005CD7' '3A40DBA3-3484-4104-9384-81A7B85CBCE4' UNION ALL
SELECT '10' '5:00' '1' '1' '0' '0' '1900-01-01 05:00:00.000' '1900-01-01 06:00:00.000' '1234567' '0' '105' 'NULL' '0x0000000000005CD8' '352640D0-70BF-4A19-A4C8-FAE924C7233B' UNION ALL
SELECT '11' '6:00' '1' '1' '0' '0' '1900-01-01 06:00:00.000' '1900-01-01 07:00:00.000' '1234567' '0' '106' 'NULL' '0x0000000000005CD9' '8588C1D1-B39E-4868-80B9-1498749BDE99' UNION ALL
SELECT 12 7:00 1 1 0 0 1900-01-01 07:00:00.000 1900-01-01 08:00:00.000 1234567 0 107 NULL 0x0000000000005CDA C71B7793-B91E-433A-A75F-001C8DAFE397 UNION ALL
SELECT 13 8:00 1 1 0 0 1900-01-01 08:00:00.000 1900-01-01 09:00:00.000 1234567 0 108 NULL 0x0000000000005CDB 1E5B0909-997B-4E3A-A0DE-1872960CB47F UNION ALL
SELECT 14 9:00 1 1 0 0 1900-01-01 09:00:00.000 1900-01-01 10:00:00.000 1234567 0 109 NULL 0x0000000000005CDC BA0C596E-F279-4C73-82F3-77C98EDB78FB UNION ALL
SELECT 15 10:00 1 1 0 0 1900-01-01 10:00:00.000 1900-01-01 11:00:00.000 1234567 0 110 NULL 0x0000000000005CDD 19243589-4513-4DC4-B8A5-B951B5D4EDCB UNION ALL
SELECT 16 11:00 1 1 0 0 1900-01-01 11:00:00.000 1900-01-01 12:00:00.000 1234567 0 111 NULL 0x0000000000005CDE 4C1A98FD-1B18-4C0B-A278-FE762308A058 UNION ALL
SELECT 17 12:00 1 1 0 0 1900-01-01 12:00:00.000 1900-01-01 13:00:00.000 1234567 0 112 NULL 0x0000000000005CDF DDE531A7-3C6A-404D-BE67-2FFA4A42E40B UNION ALL
SELECT 18 13:00 1 1 0 0 1900-01-01 13:00:00.000 1900-01-01 14:00:00.000 1234567 0 113 NULL 0x0000000000005CE0 5D368BB2-39BE-4C2D-AD6B-DDD0CFF18FEE UNION ALL
SELECT19 14:00 1 1 0 0 1900-01-01 14:00:00.000 1900-01-01 15:00:00.000 1234567 0 114 NULL 0x0000000000005CE1 287B611C-B6A0-40BE-8611-91F0144D6676 UNION ALL
SELECT 20 15:00 1 1 0 0 1900-01-01 15:00:00.000 1900-01-01 16:00:00.000 1234567 0 115 NULL 0x0000000000005CE2 4E73D1B0-21DB-4C8A-ADE1-9B6AEE718975 UNION ALL
SELECT 21 16:00 1 1 0 0 1900-01-01 16:00:00.000 1900-01-01 17:00:00.000 1234567 0 116 NULL 0x0000000000005CE3 20FB0E94-F923-4EE3-B0E4-E976B70F408F UNION ALL
SELECT 22 17:00 1 1 0 0 1900-01-01 17:00:00.000 1900-01-01 18:00:00.000 1234567 0 117 NULL 0x0000000000005CE4 B899CAFB-71A2-481F-BD36-5A7FF95C17B6 UNION ALL
SELECT 23 18:00 1 1 0 0 1900-01-01 18:00:00.000 1900-01-01 19:00:00.000 1234567 0 118 NULL 0x0000000000005CE5 F3127843-A34D-4316-81A7-63DEB258E665 UNION ALL
SELECT 24 19:00 1 1 0 0 1900-01-01 19:00:00.000 1900-01-01 20:00:00.000 1234567 0 119 NULL 0x0000000000005CE6 E615C520-A295-4037-9B47-FEAF659BC4A7 UNION ALL
SELECT 25 20:00 1 1 0 0 1900-01-01 20:00:00.000 1900-01-01 21:00:00.000 1234567 0 120 NULL 0x0000000000005CE7 DE2A57B6-60CB-40E0-BA8D-1544927E4BBF UNION ALL
SELECT '26' '21:00' '1' '1' '0' '0' '1900-01-01 21:00:00.000' '1900-01-01 22:00:00.000' '1234567' '0' '121' 'NULL' '0x0000000000005CE8' '2E7455C4-AECE-4868-9A93-D7253B3CEDF4' UNION ALL
SELECT '27' '22:00' '1' '1' '0' '0' '1900-01-01 22:00:00.000' '1900-01-01 23:00:00.000' '1234567' '0' '122' 'NULL' '0x0000000000005CE9' 'C02BC81D-8809-46D4-BC70-1BAC60647811' UNION ALL
SELECT '28' '23:00' '1' '1' '0' '0' '1900-01-01 23:00:00.000' '1900-01-02 00:00:00.000' '1234567' '0' '123' 'NULL' '0x0000000000005CEA' 'E48FAEF5-703D-41C1-8FB6-CCFD68DF1D93' UNION ALL
March 9, 2015 at 4:39 am
If you post sample data so people can just run it & see the problem, you will get plenty of replies.
See this:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
March 9, 2015 at 6:40 am
This ON clause will generate an error as it breaks at least two syntax rules:
JOIN dbo.PeriodDefinition AS pd
ON CAST(t.dt_close_time AS time) BETWEEN CAST(pd.dt_start AS time) CASE when i_period_definition_id = 3
AND CAST(pd.dt_finish AS time) CASE when i_period_definition_id = 4
Explain in words what you are attempting to do and someone will write a valid ON clause for you.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2015 at 9:37 am
chef423 (3/8/2015)
Background on this project: I am writing a custom report for a client, we are in the Point of Sale business. I am the technical director of the company, but I am hardly a true SQL expert, hence why I am asking for advice on this code.Scope: The client wishes to have a custom report that returns the values of: Total Sales , Covers (# of Patrons), and the Avg $ Amount per Cover.
The total sales are derived from what we call 'Revenue Centers' & 'Day Parts' in a restaurant.
A revenue center is any location, could be 'Bar', or Dining Room, or 'Patio', or a 'Lounge'...I am certain you get the idea.
Now, a 'Day Part', in the POS software we sell (Dinerware), is broken down into 4 'parts', by default.
The 4 'Day Parts' and their times are:
Breakfast (4am - 10am)
Lunch (10am - 4pm)
Dinner (4pm - 10pm)
Late Night (10pm - 4am)
However...
This location has had me edit the DayParts to:
Breakfast (4am - 10am)
Lunch (10am - 3pm) <--
Dinner (3pm - 10pm) <--
Late Night (10pm - 4am)
The business 'day' in the Dinerware software is from 4am to 4am, by default...due to many clubs and venues that stay open beyond Midnight (this value can also be changed in the UI of the software).
The code:
SELECT'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
SUM(t.c_items_total) / SUM(t.cover_count) as AvgPer_Cover
FROM
dbo.Ticket AS t JOIN
dbo.PeriodDefinition AS pd ON CAST(t.dt_close_time AS time) BETWEEN CAST(pd.dt_start AS time) AND CAST(pd.dt_finish AS time) AND
pd.i_period_definition_id IN(1, 2, 3, 4) and t.i_revcenter_id = 7 and t.i_void_ticket_id IS NULL and t.b_closed = 1 and t.c_items_total > 0
WHERE %PARAMS%
chef423
That's what I am trying to do....write a SQL Query that returns the values of said dayParts & revenueCenters
March 9, 2015 at 9:41 am
ChrisM@Work (3/9/2015)
This ON clause will generate an error as it breaks at least two syntax rules:
JOIN dbo.PeriodDefinition AS pdON CAST(t.dt_close_time AS time) BETWEEN CAST(pd.dt_start AS time) CASE when i_period_definition_id = 3
AND CAST(pd.dt_finish AS time) CASE when i_period_definition_id = 4
Explain in words what you are attempting to do and someone will write a valid ON clause for you.
Yes, Sorry, that was code I was attempting to learn from, here is the actual code that works for DatParts IN(1, 2, 3)
updated in original post as well
SELECT
'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
SUM(t.c_items_total) / SUM(t.cover_count) as AvgPer_Cover
FROM
dbo.Ticket AS t JOIN
dbo.PeriodDefinition AS pd ON CAST(t.dt_close_time AS time) BETWEEN CAST(pd.dt_start AS time) AND CAST(pd.dt_finish AS time) AND
pd.i_period_definition_id IN(1, 2, 3, 4) and t.i_revcenter_id = 7 and t.i_void_ticket_id IS NULL and t.b_closed = 1 and t.c_items_total > 0
March 9, 2015 at 9:53 am
I moved the code from here...up to original post.
March 9, 2015 at 7:56 pm
laurie-789651 (3/9/2015)
If you post sample data so people can just run it & see the problem, you will get plenty of replies.See this:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Hi Laurie...can you let me know how I am doing? This is my first time to post data in the 'correct' format. Any feedback is appreciative.
Thanks so much.
Chris
EDIT: I must be doing this all wrong....the link you posted claims 'with a little effort...' I have been at this ALL DAY. Would be sooo much easier to load up the .bak file and run the query!
March 10, 2015 at 11:12 pm
So I figured out the issues...well, whats wrong anyway.
i_period_definition = 4, the Late Night dayPart, from '1899-12-30 22:00:00.000' to '1899-12-30 04:00:00.000'
SO...22:00 hours, or 10pm to 4:00, or 4am is going from 22 to 4 and the 24 hr Windows clock (time) does not like this, it seems....so when I changed the dayPart to '1899-12-30 22:00:00.000' to '1899-12-30 23:59:59.000', the NULL vaule goes away...
So how to write this part? DATE + 1?
Help.
March 11, 2015 at 2:46 am
chef423 (3/8/2015)
...Caveat:[/u] The WHERE clause cannot be used in writing this Query as it is used to determine the time span of the report by a line called WHERE %PARAMS%, where PARAMS is the date range of the report being queried from the UI of the POS software.
...
Can we have some sample data for the tickets table please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2015 at 10:23 am
Late Night (10pm - 4am)
Although the POS software's 'day' is from 4am to 4am...our clock is 12am to 12am. So I have ascertained the issue is with the dayPart moving from 10pm to 4am. Whiich is moving into the next day, even tho the 'business day' in the POS software is the same.
So this is a simple DATE conversion for the Late Night day part after '1899-10-30 23.59.59.000'
Ill post the sample data in a bit, but do you have any suggestion on the language to use here?
DATEADD(dd, DATEDIFF(dd, '17530101', GETDATE()) + 1, '17530101'))
CASE IF TIME > '1899-10-30 23.59.59.000' then DATEDIFF +1
Something like the above?
March 11, 2015 at 11:06 am
Ticket Table Test Data
SELECT '66517', '0', 'Deu Bank', '1386.00', '203.74', '0.00', '1866.94', '1866.94', '277.20', 'NULL', 'NULL', '205', '2014-06-25 17:03:31.407', '2014-06-25 22:21:12.120', '1', 'NULL', 'NULL', 'NULL', '205', '0', '0', '205', 'NULL', '154', '1386.00', '203.74', '2679', '1753-01-01 00:00:00.000', '22', '0', '7', '0', '0', '0.00', '1', 'NULL', '53292974-1149-4AEE-B8C9-1BE0990A989F', '0x000000000177F502', '2014-06-25 19:56:55.633', 'NULL', '0', 'FullyPaid', '0.00', '0', 'NULL', 'NULL'
March 12, 2015 at 7:59 am
chef423 (3/11/2015)
Ticket Table Test Data
SELECT '66517', '0', 'Deu Bank', '1386.00', '203.74', '0.00', '1866.94', '1866.94', '277.20', 'NULL', 'NULL', '205', '2014-06-25 17:03:31.407', '2014-06-25 22:21:12.120', '1', 'NULL', 'NULL', 'NULL', '205', '0', '0', '205', 'NULL', '154', '1386.00', '203.74', '2679', '1753-01-01 00:00:00.000', '22', '0', '7', '0', '0', '0.00', '1', 'NULL', '53292974-1149-4AEE-B8C9-1BE0990A989F', '0x000000000177F502', '2014-06-25 19:56:55.633', 'NULL', '0', 'FullyPaid', '0.00', '0', 'NULL', 'NULL'
Msg 213, Level 16, State 1, Line 2
Column name or number of supplied values does not match table definition.
You're not helping us much here Chris. There's a link to the forum guide in my signature block which Laurie has already posted, have a look through it and try posting up this sample data again.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2015 at 11:04 am
I've solved it anyway. Thanks for your time.
Funny when I posted SQL script with fragments I was getting plenty of help. Then I attempt to do it the proper way and nothing...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply