Datawindow conditional sql
Hi,
I'm using Powerbuilder 12.5.
I'm working on a datawindow that uses ordinal dates as the start and end date criteria. The problem is that this doesn't currently work around the new year since the start is larger than the end. I'm trying to find a way to handle this with a single datawindow instead of having to use two. I've tried the below where start_day is 355 and end_day is 5, but it locks the app up.
I'm using Powerbuilder 12.5.
I'm working on a datawindow that uses ordinal dates as the start and end date criteria. The problem is that this doesn't currently work around the new year since the start is larger than the end. I'm trying to find a way to handle this with a single datawindow instead of having to use two. I've tried the below where start_day is 355 and end_day is 5, but it locks the app up.
IF :end_day > 17
SELECT dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave',
'New' as 'status',
sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os',
sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc',
sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp',
sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao',
' ' as 'ws_os',
' ' as 'ws_acc',
' ' as 'ws_pp',
' ' as 'ws_sao',
' ' as 'wc_os',
' ' as 'wc_acc',
' ' as 'wc_pp',
' ' as 'wc_sao'
FROM dbo.ord_hdr
WHERE ( convert(integer,dbo.ord_hdr.jul_rlse_dy) >= :start_day) AND
( convert(integer,dbo.ord_hdr.jul_rlse_dy) <= :end_day)
group by dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg
ELSE
SELECT dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave',
'New' as 'status',
sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os',
sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc',
sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp',
sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao',
' ' as 'ws_os',
' ' as 'ws_acc',
' ' as 'ws_pp',
' ' as 'ws_sao',
' ' as 'wc_os',
' ' as 'wc_acc',
' ' as 'wc_pp',
' ' as 'wc_sao'
FROM dbo.ord_hdr
WHERE ( convert(integer,dbo.ord_hdr.jul_rlse_dy) >= :start_day) OR
( convert(integer,dbo.ord_hdr.jul_rlse_dy) <= :end_day)
group by dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg
What value is contained in ord_hdr.jul_rlse_dy ? If this field contains a datetime-value (i.e., has a year, month, day, and maybe a time component) then your logic would be incorrect if table ord_hdr has several years of data because each years' data would contain records for early January and late December.
Converting a datetime-value to an integer would be a really big number, it's the number of milliseconds from the fixed-date 1970-Jan-01. You'd be better off (both from a performance perspective and final results) if you converted your start/end ordinal-date values into datetime-values instead of converting the table-field jul_rlse_dy into an integer.
No if-statement needed, I would also add a full day to end-day, in case there is a time component:
Converting a datetime-value to an integer would be a really big number, it's the number of milliseconds from the fixed-date 1970-Jan-01. You'd be better off (both from a performance perspective and final results) if you converted your start/end ordinal-date values into datetime-values instead of converting the table-field jul_rlse_dy into an integer.
No if-statement needed, I would also add a full day to end-day, in case there is a time component:
SELECT dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave'
, 'New' as 'status'
, sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os'
, sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc'
, sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp'
, sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao'
, ' ' as 'ws_os'
, ' ' as 'ws_acc'
, ' ' as 'ws_pp'
, ' ' as 'ws_sao'
, ' ' as 'wc_os'
, ' ' as 'wc_acc'
, ' ' as 'wc_pp'
, ' ' as 'wc_sao'
FROM dbo.ord_hdr
WHERE dbo.ord_hdr.jul_rlse_dy >= :start_day
AND dbo.ord_hdr.jul_rlse_dy < :end_day
GROUP
BY dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_segASKER
The value is the ordinal date ( 1 - 365). It is referred to internally as the julian date, hence the field name. Only the last 90 days of data is retained, so there's no overlap. The ordinal date is used as part of a trailer name, so the users like to use it as criteria.
My problem is that the new year caused the end day to be before the start. I'm looking at adding a second set of parameters that would include a second date range if the end is less than the start. The Where would look something like this:
My problem is that the new year caused the end day to be before the start. I'm looking at adding a second set of parameters that would include a second date range if the end is less than the start. The Where would look something like this:
WHERE (( convert(integer,dbo.ord_hdr.jul_rlse_dy)) BETWEEN :start_day AND :end_day OR
( convert(integer,dbo.ord_hdr.jul_rlse_dy)) BETWEEN :start_day2 AND :end_day2)SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@John Vidmar - Even better!
ASKER
Thanks guys! Those both work for me. I think I'll use the one from @John Vidmar since it is a little more compact.
It will make it easier to determine a solution if all columns and types are known.