Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: SQL Syntax for YOY Comparison Post Reply Post New Topic
Author Message
srklg1
Newbie
Newbie
Avatar

Joined: 10 Sep 2007
Location: United States
Online Status: Offline
Posts: 22
Quote srklg1 Replybullet Topic: SQL Syntax for YOY Comparison
    Posted: 15 Oct 2007 at 11:56am
Good afternoon, all!
 
I need to compare the following Data side-by-side in a CR Year-over-Year for the following time periods:
1.  YTD
2.  MTD
3.  Last Month
 
On one half of the report would be 2007 data and 2006 on the other side for TotalRevenue, Charges, and a couple of Formula Fields.  Any ideas?  I have toyed with creating Formula Fields for the 2006 data and throwing a between statement in the HAVING clause.  I just wonder if there is a more efficient way to do this because we will use a scheduler to kick this off daily.
 
Looking for ideas . . .
 
I'm using the following Command to get data by time period through the Select Expert, I just need to combine them on the same report.
 
SELECT

  "tblLoads"."BillToID",

  "tblLoads"."BillToName",

  AVG("tblLoads"."TotalRevenue"),

  "tblLoads"."InvoiceDate",

  YEAR(tblLoads.InvoiceDate),

  "tblLoads"."PickupDate",

  "tblLoads"."DeliveryDate",

  "tblLoads"."LoadID",

  SUM("tblLoadDispatch"."DriverPay") as Charges,

  "tblLoads"."LoadClass",

  "tblLoads"."Status",

  "tblLoads"."Salesperson"

 

FROM   tblLoads LEFT OUTER JOIN tblLoadDispatch ON (tblLoads.LoadID = tblLoadDispatch.LoadID)

 

GROUP BY

  "tblLoads"."BillToID",

  "tblLoads"."BillToName",

  "tblLoads"."InvoiceDate",

  "tblLoads"."PickupDate",

  "tblLoads"."DeliveryDate",

  "tblLoads"."LoadID",

  "tblLoads"."TotalRevenue",

  "tblLoads"."LoadClass",

  "tblLoads"."Status",

  "tblLoads"."Salesperson"

 

HAVING tblLoads.Status <> 'CANCELLED' And tblLoads.Status <> 'INACTIVE' AND tblLoads.LoadClass <> 'TLQUOTE'  And tblLoads.LoadClass <> 'LTLQUOTE' and

 

ORDER BY tblLoads.Salesperson DESC, tblLoads.TotalRevenue DESC
srklg1
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 16 Oct 2007 at 8:10am
Just a couple of thoughts...
 
You mights create a Command oject that uses the Load id which I am guessing is the unique identifier.  You can use it to pull LoadId for selected date range. You would use parameters to do this.
 
Then use another Command Object for the details you want and link the two together on the LoadId.
 
I dont understand your data enough to know how the years relate to each other. Because of that I am not certain which way to get a side by side comparison might work best. If the loadid or some other field is not consistent from year to year to year it may hard to get the format you are seeking
 
Perhaps one of the CrossTab experts could tell you whether a CrossTab report is good option for what you are doing.
 
Regards,
 
John W.
IP IP Logged
srklg1
Newbie
Newbie
Avatar

Joined: 10 Sep 2007
Location: United States
Online Status: Offline
Posts: 22
Quote srklg1 Replybullet Posted: 16 Oct 2007 at 12:43pm
John,
 
Thank for the ideas!
 
The problem gets dicey because I'm looking at 2006 data and 2007 data whete BillToID and BillToName may or may not be the same.  We may have added a customer in 2007 that wasn't a customer in 2006.  So what I would need to see would be a UNION of BillToID and BillToName with the fields listed in the query for the time period of MTD and LYMTD.  Except in my case, the table names aren't different.
 
Perhaps a Crosstab would work, if I can get the right format out of it.
 
I'll check into it.
 
Ciao!
srklg1
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.