DateAdd results in unexpected SQL query- ideas? 1

[metaldude]

Hi and thanks in advance for any help you can give.

Information:
--CR 8.5
--Gupta SQL db
--2 of my parameters are for start and end date
--attempting to also select same start and end date but 1 year before
--parameters I entered were 6/19/07 and 6/21/07
--results show in cross tab but with unexpected end date(and time) for the "last year" part of the SQL Query

my record selection regarding the date looks like this:

(({RECEIPT_LINE.SALES_DATE} >= {?startDate} and
{RECEIPT_LINE.SALES_DATE} <= {?endDate})
or
({RECEIPT_LINE.SALES_DATE} >= DateAdd('yyyy',-1,{?startDate}) and
{RECEIPT_LINE.SALES_DATE} <= DateAdd('yyyy',-1,{?endDate})))

the results of showing the SQL Query from the database menu was not what i was expecting:

((RECEIPT_LINE."SALES_DATE" >= {ts '2007-06-19 00:00:00.00'} AND
RECEIPT_LINE."SALES_DATE" [red]<[/red] {ts '2007-06-21 00:00:00.00'}) OR
(RECEIPT_LINE."SALES_DATE" >= {ts '2006-06-19 00:00:00.00'} AND
RECEIPT_LINE."SALES_DATE" [red]<[/red] {ts '2006-06-[red]20[/red] 00:00:0[red]1[/red].00'}))

For the "Last Year" section, I expected to get the same exact beginning and end dates with the exception of the year. I also thought that the comparisons would stay exactly as I wrote them. I put the weird (to me anyway hehe) parts in red above.

What am I doing wrong?

 

[lbass]

Because your field is a datetime and you are using a date parameter, not a datetime parameter, the date function treats the {?enddate} as
datetime(2006,6,20,0,0,0), NOT as datetime(2006,6,20,23,59,59) as you are expecting, and so the <= is translated to one second past the enddate as it interprets it. To accommodate your needs, change the record selection formula to

(
(
{RECEIPT_LINE.SALES_DATE} >= {?startDate} and
{RECEIPT_LINE.SALES_DATE} <= {?endDate}
)
or
(
{RECEIPT_LINE.SALES_DATE} >= DateAdd('yyyy',-1,
{?startDate}) and
{RECEIPT_LINE.SALES_DATE} < DateAdd('yyyy',-1,
{?endDate}+1)
)
)

-LB

 

[metaldude]

Thanks LB! I was worried about adding the 1 without knowing why (in case that ended up biting me later). Since my <= turned into < on both (last year and this year's) end dates, I ended up doing this:

(
    (
    {RECEIPT_LINE.SALES_DATE} >= {?startDate} and
    ({RECEIPT_LINE.SALES_DATE} <= {?endDate} + 1)
    )
    or
    (
    {RECEIPT_LINE.SALES_DATE} >= DateAdd ('yyyy',-1,{?startDate}) and
    {RECEIPT_LINE.SALES_DATE} <= DateAdd ('yyyy',-1,{?endDate} + 2)
    )
)

I can't tell you if it worked yet because this report is really slow. It has been running for 45 min on only 6 days total. The same report without the part about adding the same days from the previous year takes 30 seconds for 6 days. Should I start a new thread to ask you about this?

Thanks-
E

 

[metaldude]

Hey again LB-
Not sure what I was looking at before but, of course, how you said to do it was right. I put it back to nothing added to the original end date and +1 (not +2) on the previous year's end date.

The report ended up taking about 1:45. Is that the DateAdd function adding all of that extra time to finish the report?

Thanks again for helping me out-
E

 

[lbass]

I don't think the dateadd is the issue, since it passes to the SQL statement. What is the rest of your selection formula? Are there parts of it that do not appear in the "show SQL query"? You might want to check out thread149-1276307.

-LB

 

[metaldude]

Man, that's a great link to have. I understand some of it but unfortunately, some of it is over my head. I saw that you guys were talking about changing the order of things in the WHERE clause so I went to database > show SQL query dialog and switched the store_no = 1 and department <> '97' lines. I was going to do more later but after I hit OK, the report started running again. I'm just letting it run. Here's how it looks right now:

SELECT
RECEIPT_LINE."STORE_NO", RECEIPT_LINE."SALES_DATE", RECEIPT_LINE."SKU", RECEIPT_LINE."QTY", RECEIPT_LINE."SELLING_PRICE",
PRODUCT."STYLE", PRODUCT."DEPARTMENT",
DEPARTMENT."DEPARTMENT_NAME"
FROM
"SYSADM"."RECEIPT_LINE" RECEIPT_LINE,
"SYSADM"."PRODUCT_SIZE" PRODUCT_SIZE,
"SYSADM"."PRODUCT" PRODUCT,
"SYSADM"."DEPARTMENT" DEPARTMENT
WHERE
RECEIPT_LINE."SKU" = PRODUCT_SIZE."SKU" AND
PRODUCT_SIZE."STYLE" = PRODUCT."STYLE" AND
PRODUCT_SIZE."VENDOR_NO" = PRODUCT."VENDOR_NO" AND
PRODUCT."DEPARTMENT" = DEPARTMENT."DEPARTMENT" AND
RECEIPT_LINE."STORE_NO" = 1 AND
PRODUCT."DEPARTMENT" <> '97' AND
((RECEIPT_LINE."SALES_DATE" >= {ts '2007-06-20 00:00:00.00'} AND
RECEIPT_LINE."SALES_DATE" < {ts '2007-06-23 00:00:00.00'}) OR
(RECEIPT_LINE."SALES_DATE" >= {ts '2006-06-20 00:00:00.00'} AND
RECEIPT_LINE."SALES_DATE" < {ts '2006-06-23 00:00:01.00'}))
ORDER BY
RECEIPT_LINE."SALES_DATE" DESC,
PRODUCT."STYLE" ASC,
RECEIPT_LINE."SKU" ASC

Does anything here jump out at you?

metal "Rookie" dude

 

[lbass]

It looks like you aren't linking the tables in the database linking expert and instead are using the record selection formula to do that. You would link from the table that has the least number of records (maybe department to product, product to product size, product size to receipt line). Then remove those lines from the selection formula. Not absolutely sure these steps will make a difference, but the order of linking tables often does affect speed.

You should also make sure that all selection criteria show up in the SQL query.

-LB

 

[metaldude]

Hi LB-

I used the linking expert but it shows in the query too. I'm glad it did because you caught the problem with the order of the tables. I got handed other projects so I haven't had time to finish the report and various configurations but I've already saved 30 min on the report due to your suggestions. Sweet! Thanks again for all of your help. -E