Sort By Variance

[dleewms]

I'm using Crystal XI on SQL Server 2005.

I've seen a couple of posts on the topic but need some help.

I don't use SQL Expressions often, so may just need an example or two there.

I was attempting to sort on my variance formula: Sum ({@Current}, {sales_register.cust_name})-Sum ({@LastYear}, {sales_register.cust_name}). Here are the Current & Last Year formulas
{@Current}: if {sales_register.invoice_date} in YearToDate then
{sales_register.sub_amt}

{@LastYear}: if ({sales_register.invoice_date}) = LastYearYTD
then {sales_register.sub_amt}

Based on the posts I've read, I understand I cannot sort on a field which can not be summarized. It has been suggested I do the sum in the view. I'm attempting to do that, but it looks like TSQL doesn't have YearToDate adn LastYearYTD functions.

My report is grouping by the customer's name. Below is the view upon which the report is based. You see where I attempted to get the current_sub_amt in the view, but got stuck. Therefore, I thought I'd go back to Crystal Reports and try the SQL Expressions route. Any help would be greatly appreciated.

Thanks!

select -- invoice
ivc.batch_no,
ivc.invoice_no,
--Added by DLW 06/29/2011
coalesce(ivc.orig_invoice_no, ivc.invoice_no) as orig_ivc_no,
ivc.invoice_date,
ivc.branch_code,
ivc.customer_id,
current_sub_amt = (select sum(ivc2.sub_amt)
from invoice ivc2
where ivc.invoice_date in year
ivc.cust_name,
ivc.sub_amt,
-- invoice group
ig.doc_no,
doc_type = case
when ig.doc_type IN ('CBL', 'CO', 'COL') then 'CO'
when ig.doc_type IN ('WO', 'WOA', 'WOS', 'WOT') then 'WO'
when ig.doc_type IN ('SO', 'SOL', 'SOS', 'SSL') then 'SO'
when ig.doc_type IN ('IA', 'IAL') then 'IA'
when ig.doc_type IN ('CLH', 'CLA', 'CLT') then 'CLH'
else ig.doc_type
end,
ig.customer_po
from invoice ivc
join invoice_group ig on ig.invoice_no = ivc.invoice_no
join currency cu on cu.curr_code = ivc.curr

 

[dleewms]

The following TSQL statement can be used for the current year to date.
>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

 

[dleewms]

Here is my attempt at the SQL Expression:

select sum(`invoice`.`sub_amt`)
from `invoice`
where `invoice`.`invoice_date` >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
and `invoice`.`customer_id` = `sales_register`.`customer_id`

It is yielding the followng error: Error in compiling SQL Expression. Failed to retrieve data from the database. Details: ADO Error Code 0x80040e14 Source: Microsoft OLE DB Provider for SQL Server Description: Incorrect syntax near the keyword 'select'. SQL State 42000 Native Error: 156 [Database Vendor Code: 156].

 

[lbass]

For subselects, the entire expression must be enclosed in parens for one thing.

-LB

 

[dleewms]

Yes LBass. You're correct. However, I stopped when I got stuck on the current year function.

Nevertheless, I have returned to the SQL expression and I finally got it to return with no error. What I discovered is that SQL Expressions are server specific. In TSQL double quotes can be used to referenced fields instead of single quotes which are used in Oracle.

Here is my expression for the current year to date sub amount

(
select sum(ivc2."sub_amt")
from invoice ivc2
join sales_register
on ivc2."customer_id" = "sales_register"."customer_id"
where ivc2."invoice_date" >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
)

I'm attempting to create an SQL expression for last year with the following syntax, but it is hanging. Not sure why....

(
select sum(ivc2."sub_amt")
from invoice ivc2
join sales_register
on ivc2."customer_id" = "sales_register"."customer_id"
where ivc2."invoice_date" >= DateAdd(yyyy, DateDiff(yyyy, 365, GetDate()), 0)
and inv2."invoice_date < DateAdd(dd, DateDiff(dd, 365, GetDate()) +1, 0)
)

 

[lbass]

I can't really follow your use of dateadd and datediff here. It seems like you are subtracting 365 years from the currentdate in the first line of the where clause, when it should be days. The second line looks correct--why not just use a first line something like:

ivc2."invoice_date" >= DateAdd(dd, DateDiff(dd, 730, GetDate()), 0)

-LB

 

[dleewms]

Thanks so much LB. This is pointing me in the right direction. I think the first line of the where clause should have been
DateAdd(yyyy, DateDiff(yyyy, 1, GetDate()), 0) or DateAdd(yyyy, DateDiff(yyyy, -1, GetDate()), 0). I testing it now to figure out which one will return January 1st of 2010. Is it correct to assume your suggestion would go back 730 days (2 years). How does that get us to January 1st of the year though?

>= DateAdd(dd, DateDiff(dd, 730, GetDate()), 0)

 

[dleewms]

I am grouping first by branch code and then by customer_id. I need to display the subtotal for each customer within each branch. Currently, when I place the SQL Expression in Group Footer 2, it displays a grand total. I attempted to add "Group By" and got an error. Anyone know how I can get the SQL Expression to return a value for each customer?

(
select sum(ivc2."sub_amt")
from invoice ivc2
join sales_register
on ivc2."customer_id" = "sales_register"."customer_id"
and ivc2."invoice_no" = "sales_register"."orig_ivc_no"
and ivc2."branch_code" = "sales_register"."branch_code"
where ivc2."invoice_date" >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
group by ivc2."branch_code", ivc2."customer_id"
)

 

[lbass]

(
select sum(ivc2."sub_amt")
from invoice ivc2
join sales_register
on ivc2."customer_id" = "sales_register"."customer_id"
and ivc2."invoice_no" = "sales_register"."orig_ivc_no"
and ivc2."branch_code" = "sales_register"."branch_code"
and ivc2."customer_id" = invoice."customer_id"
and ivc2."branch_code" = invoice."branch_code"
where ivc2."invoice_date" >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
)

-LB

 

[dleewms]

Thanks for the response LB. However, an error is return: The multi-part identifier "invoice.customer_id" could not be bound.

The report is grouped on the branch_code and customer_id from the sales_register table. The invoice table, which we've referenced once as ivc2 is not linked in Crystal.

 

[lbass]

You mean it has not been brought into the report except in the SQL expression? Not sure, but try this:

(
select sum(ivc2."sub_amt")
from invoice ivc2
join sales_register sr
on ivc2."customer_id" = sr."customer_id"
and ivc2."invoice_no" = sr."orig_ivc_no"
and ivc2."branch_code" = sr."branch_code"
and sr."customer_id" = sales_register."customer_id"
and sr."branch_code" = sales_register."branch_code"
where ivc2."invoice_date" >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
)

-LB