ACCESS FEED: Re: [MS_AccessPros] RE: SQL Code Corruption

Bob-

It's not certain that even designing in SQL view that the query engine won't redo your code. BTW, what is "param"? A form that's open? If you're trying to drive this from a form, you should build the SQL on the fly in the parameter form and avoid all the ="99" stuff.

It's common to recommend building a query driven off a parameter form as:

WHERE ((MyField = [Forms]![Parameters]![txtFieldParm]) Or ([Forms]![Parameters]![txtFieldParm] IS NULL))

.. so that you get all rows if the filter parameter isn't supplied. But that advice is also given with a caveat that you can't do this for more than 3 or maybe 4 parameters before you'll hit the "query too complex" barrier. At that point, we recommend building the query (or filter to open a form or report) on the fly, ignoring the "null" parameters.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 29, 2014, at 7:11 PM, Bob Phillips <bob.phillips@dsl.pipex.com> wrote:

No offence John, but that is ridiculous. I can't change the relationship view as it corrupts my code, I can't add another table via the designer because it corrupts the code, and so on. I have to do everything in the SQL view, which I find a challenge as Access seems to object to the way I join tables and insists on a oblique nesting and proliferation of brackets.

I hadn't tried to run this 'optimized' code before, but I just tried it and yes, I got that message.

I am so glad that SQL Server is my primary tool, it has some quirks but nothing as out there as that.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: 29 January 2014 16:03
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: SQL Code Corruption

Bob-

That's "normal". Access has "optimized" your predicate and switched it from a series of (A Or B) And (C or D) ["And"ed "Or"s] to this:

(A And C) Or (A And C) Or (B And C) Or (B And D) ["Or"ed "And"s)

In theory, it does that because it can quit looking when the first set of (X And Y) come up true - it doesn't have to evaluate the entire predicate for each row. As originally stated, it has to evaluate ALL the predicates to make a decision.

Did you get "query too complex" when you tried to run it?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 29, 2014, at 4:34 PM, <wrmosca@comcast.net> <wrmosca@comcast.net> wrote:

Bob

For the life of me, I've never seen that happen. Just for kicks I did a count on [param].[property].[id] and found 64 instances! And I see everything got bracketed.

Maybe if you declare all your parameters that won't happen. But I really don't have an explanation for you.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <bob.phillips@dsl.pipex.com> wrote:
This is a bit of an odd one, but here goes.

I have this Access query.

SELECT reg.RegionID, reg.Region, IIF(ISNULL(Round(Sum(UpsellRevenue
                            *Switch(param.report.ccy="EUR",xr.EURRate
                                   ,param.report.ccy="USD",xr.USDRate
                                   ,param.report.ccy="GBP",xr.GBPRate)
                            )
                        ,2)),""
                  ,Round(Sum(UpsellRevenue
                            *Switch(param.report.ccy="EUR",xr.EURRate
                                   ,param.report.ccy="USD",xr.USDRate
                                   ,param.report.ccy="GBP",xr.GBPRate)
                            )
                        ,2)) AS TotalRevenue
FROM ExchangeRates AS xr, (((Region AS reg INNER JOIN ((Country AS cty INNER JOIN Property AS prop ON cty.CountryID = prop.CountryID) INNER JOIN Tracking AS trk ON prop.PropertyID = trk.PropertyID) ON reg.RegionID = cty.RegionID) INNER JOIN Area AS area ON prop.AreaID = area.AreaID) INNER JOIN Brand AS brnd ON prop.BrandID = brnd.BrandID) INNER JOIN Contract AS con ON prop.ContractID = con.ContractID
WHERE (((trk.TrackingYear)=[param].[year])) AND
        (trk.DepartureDate >= param.start.date AND trk.DepartureDate <= param.end.date) AND
        (prop.PropertyID=param.property.id OR param.property.id=99) AND
        (cty.CountryID=param.country.code OR param.country.code="zzz") AND
        (reg.RegionID=param.region.id OR param.region.id="zzz") AND
        (area.AreaID=param.area.id OR param.area.id=99) AND
        (brnd.BrandID=param.brand.id OR param.brand.id=99) AND
        (con.ContractID=param.contract.id OR param.contract.id=99) AND
         xr.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1)
GROUP BY reg.RegionID, reg.Region;

If I go to design view, I find that one of the tables is hidden behind another, so I move the top one, nothing more than just moving it in the relationship poane, go back to the SQL view, and the code is changed to the below. What the heck is going on here?

SELECT reg.RegionID, reg.Region, IIf(IsNull(Round(Sum(UpsellRevenue*Switch(param.report.ccy="EUR",xr.EURRate,param.report.ccy="USD",xr.USDRate,param.report.ccy="GBP",xr.GBPRate)),2)),"",Round(Sum(UpsellRevenue*Switch(param.report.ccy="EUR",xr.EURRate,param.report.ccy="USD",xr.USDRate,param.report.ccy="GBP",xr.GBPRate)),2)) AS TotalRevenue
FROM ExchangeRates AS xr, Region AS reg INNER JOIN ((Country AS cty INNER JOIN (Brand AS brnd INNER JOIN (Area INNER JOIN (Property AS prop INNER JOIN Contract AS con ON prop.ContractID = con.ContractID) ON area.AreaID = prop.AreaID) ON brnd.BrandID = prop.BrandID) ON cty.CountryID = prop.CountryID) INNER JOIN Tracking AS trk ON prop.PropertyID = trk.PropertyID) ON reg.RegionID = cty.RegionID
WHERE (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1))) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99))
GROUP BY reg.RegionID, reg.Region;