Link to home
Create AccountLog in
Avatar of APD Toronto
APD TorontoFlag for Canada

asked on

Different Dates, but Same Values?

Hello Experts,

I'm trying to return a query with two sales amounts, one for June the other for July. Note that not always will be from the first to the last of the months, that is why I structured my code as I did.

Pretty much I am building each column separately then concantinate into one SQL string. The problem is that both columns are returning the exact same amounts for both columns for each agent, which is not possible. here are my strings

June
?STRCSSTSALES1
 , SUM( IIF( (fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) , IIF([fldProductID] = 'CSST1' OR 'CSST2', 1, IIF([fldProductID] = 'COMBO1' OR 'COMBO2', 0.33, 0)) * ( IIF([fldFamQty] > 0, [fldFamQty], 0 ) * IIF([fldFamRate] > 0, [fldFamRate], 0 )) + ( IIF([fldAdultQty] > 0, [fldAdultQty], 0 ) * IIF([fldAdultRate] > 0, [fldAdultRate], 0 )) + ( IIF([fldSenQty] > 0, [fldSenQty], 0 ) * IIF([fldSenRate] > 0, [fldSenRate], 0 )) + ( IIF([fldStudQty] > 0, [fldStudQty], 0 ) * IIF([fldStudRate] > 0, [fldStudRate], 0 )) + ( IIF([fldChildQty] > 0, [fldChildQty], 0 ) * IIF([fldChildRate] > 0, [fldChildRate], 0 )) + ( IIF([fldInfQty] > 0, [fldInfQty], 0 ) * IIF([fldInfRate] > 0, [fldInfRate], 0 )) , 0) ) AS CSSTSales1 

Open in new window


July
?STRCSSTSALES2
 , SUM( IIF( (fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) , IIF([fldProductID] = 'CSST1' OR 'CSST2', 1, IIF([fldProductID] = 'COMBO1' OR 'COMBO2', 0.33, 0)) * ( IIF([fldFamQty] > 0, [fldFamQty], 0 ) * IIF([fldFamRate] > 0, [fldFamRate], 0 )) + ( IIF([fldAdultQty] > 0, [fldAdultQty], 0 ) * IIF([fldAdultRate] > 0, [fldAdultRate], 0 )) + ( IIF([fldSenQty] > 0, [fldSenQty], 0 ) * IIF([fldSenRate] > 0, [fldSenRate], 0 )) + ( IIF([fldStudQty] > 0, [fldStudQty], 0 ) * IIF([fldStudRate] > 0, [fldStudRate], 0 )) + ( IIF([fldChildQty] > 0, [fldChildQty], 0 ) * IIF([fldChildRate] > 0, [fldChildRate], 0 )) + ( IIF([fldInfQty] > 0, [fldInfQty], 0 ) * IIF([fldInfRate] > 0, [fldInfRate], 0 )) , 0) ) AS CSSTSales2 

Open in new window


the entire SQL
?STRSQL
SELECT [tblLocs].[fldLocation]  AS HOTEL  , [tblAgents].[fldLName] & ', ' & [tblAgents].[fldFName] AS AGENT , SUM(IIF(fldProductID IN ('CSST1', 'CSST2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) ), IIf([fldFamQty]>0,[fldFamQty],0) * 4 +IIf([fldAdultQty]>0,[fldAdultQty],0) +IIf([fldSenQty]>0,[fldSenQty],0) +IIf([fldStudQty]>0,[fldStudQty],0) +IIf([fldChildQty]>0,[fldChildQty],0) , 0) ) AS CSSTPass1  , SUM( IIF( (fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) , IIF([fldProductID] = 'CSST1' OR 'CSST2', 1, IIF([fldProductID] = 'COMBO1' OR 'COMBO2', 0.33, 0)) * ( IIF([fldFamQty] > 0, [fldFamQty], 0 ) * IIF([fldFamRate] > 0, [fldFamRate], 0 )) + ( IIF([fldAdultQty] > 0, [fldAdultQty], 0 ) * IIF([fldAdultRate] > 0, [fldAdultRate], 0 )) + ( IIF([fldSenQty] > 0, [fldSenQty], 0 ) * IIF([fldSenRate] > 0, [fldSenRate], 0 )) + ( IIF([fldStudQty] > 0, [fldStudQty], 0 ) * IIF([fldStudRate] > 0, [fldStudRate], 0 )) + ( IIF([fldChildQty] > 0, [fldChildQty], 0 ) * IIF([fldChildRate
] > 0, [fldChildRate], 0 )) + ( IIF([fldInfQty] > 0, [fldInfQty], 0 ) * IIF([fldInfRate] > 0, [fldInfRate], 0 )) , 0) ) AS CSSTSales1 , SUM(IIF(fldProductID IN ('CSSN1', 'CSSN2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) ), IIf([fldFamQty]>0,[fldFamQty],0) * 4 +IIf([fldAdultQty]>0,[fldAdultQty],0) +IIf([fldSenQty]>0,[fldSenQty],0) +IIf([fldStudQty]>0,[fldStudQty],0) +IIf([fldChildQty]>0,[fldChildQty],0) , 0) ) AS CSSNPass1  , SUM( IIF( (fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) , IIF([fldProductID] = 'CSSN1' OR 'CSSN2', 1, IIF([fldProductID] = 'COMBO1' OR 'COMBO2', 0.67, 0)) * ( IIF([fldFamQty] > 0, [fldFamQty], 0 ) * IIF([fldFamRate] > 0, [fldFamRate], 0 )) + ( IIF([fldAdultQty] > 0, [fldAdultQty], 0 ) * IIF([fldAdultRate] > 0, [fldAdultRate], 0 )) + ( IIF([fldSenQty] > 0, [fldSenQty], 0 ) * IIF([fldSenRate] > 0, [fldSenRate], 0 )) + ( IIF([fldStudQty] > 0, [fldStudQty], 0 ) * IIF([fldStudRate] > 0, [fldStudRate], 0 )) + ( IIF([fldChildQty] > 0, [fldChildQty
], 0 ) * IIF([fldChildRate] > 0, [fldChildRate], 0 )) + ( IIF([fldInfQty] > 0, [fldInfQty], 0 ) * IIF([fldInfRate] > 0, [fldInfRate], 0 )) , 0) ) AS CSSNSales1 , SUM(IIF(fldProductID IN ('CSST1', 'CSST2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) ), IIf([fldFamQty]>0,[fldFamQty],0) * 4 +IIf([fldAdultQty]>0,[fldAdultQty],0) +IIf([fldSenQty]>0,[fldSenQty],0) +IIf([fldStudQty]>0,[fldStudQty],0) +IIf([fldChildQty]>0,[fldChildQty],0) , 0) ) AS CSSTPass2  , SUM( IIF( (fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) , IIF([fldProductID] = 'CSST1' OR 'CSST2', 1, IIF([fldProductID] = 'COMBO1' OR 'COMBO2', 0.33, 0)) * ( IIF([fldFamQty] > 0, [fldFamQty], 0 ) * IIF([fldFamRate] > 0, [fldFamRate], 0 )) + ( IIF([fldAdultQty] > 0, [fldAdultQty], 0 ) * IIF([fldAdultRate] > 0, [fldAdultRate], 0 )) + ( IIF([fldSenQty] > 0, [fldSenQty], 0 ) * IIF([fldSenRate] > 0, [fldSenRate], 0 )) + ( IIF([fldStudQty] > 0, [fldStudQty], 0 ) * IIF([fldStudRate] > 0, [fldStudRate], 0 )) + ( IIF([fldC
hildQty] > 0, [fldChildQty], 0 ) * IIF([fldChildRate] > 0, [fldChildRate], 0 )) + ( IIF([fldInfQty] > 0, [fldInfQty], 0 ) * IIF([fldInfRate] > 0, [fldInfRate], 0 )) , 0) ) AS CSSTSales2 , SUM(IIF(fldProductID IN ('CSSN1', 'CSSN2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) ), IIf([fldFamQty]>0,[fldFamQty],0) * 4 +IIf([fldAdultQty]>0,[fldAdultQty],0) +IIf([fldSenQty]>0,[fldSenQty],0) +IIf([fldStudQty]>0,[fldStudQty],0) +IIf([fldChildQty]>0,[fldChildQty],0) , 0) ) AS CSSNPass2  , SUM( IIF( (fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) , IIF([fldProductID] = 'CSSN1' OR 'CSSN2', 1, IIF([fldProductID] = 'COMBO1' OR 'COMBO2', 0.67, 0)) * ( IIF([fldFamQty] > 0, [fldFamQty], 0 ) * IIF([fldFamRate] > 0, [fldFamRate], 0 )) + ( IIF([fldAdultQty] > 0, [fldAdultQty], 0 ) * IIF([fldAdultRate] > 0, [fldAdultRate], 0 )) + ( IIF([fldSenQty] > 0, [fldSenQty], 0 ) * IIF([fldSenRate] > 0, [fldSenRate], 0 )) + ( IIF([fldStudQty] > 0, [fldStudQty], 0 ) * IIF([fldStudRate] > 0, [fldStu
dRate], 0 )) + ( IIF([fldChildQty] > 0, [fldChildQty], 0 ) * IIF([fldChildRate] > 0, [fldChildRate], 0 )) + ( IIF([fldInfQty] > 0, [fldInfQty], 0 ) * IIF([fldInfRate] > 0, [fldInfRate], 0 )) , 0) ) AS CSSNSales2 FROM [tblReservations] AS tblRes, [tblLocations] AS tblLocs, tblAgents  WHERE ([tblRes].fldArchived = False) AND (fldTripType <> 'E')  AND fldAgent IN (3, 107, 1)  AND ([tblRes].fldBookLoc = [tblLocs].fldLocID)  AND ([tblRes].fldAgent = [tblAgents].fldAgentID) GROUP BY [tblLocs].[fldLocation], [tblAgents].[fldLName] & ', ' & [tblAgents].[fldFName] 

Open in new window


Again, my question is why do CSSTSales1 and CSSTSales2 return the same values, when their date ranges are different?

Thank you
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

This part of your expression doesn't make sense and you have variations of it throughout:
IIF([fldFamQty] > 0, [fldFamQty], 0 )

If these quantities are never less than zero, you could just use [fldFamQty] instead to replace the whole expression above.
Or where you trying to account for Null values for your quantities?  If so, you would just use the Nz function like so:
Nz([fldFamQty],0) *[fldFamRate]I'm assuming that the rates would always have a value.
The following are incorrect:
IIF([fldProductID] = 'CSST1' OR 'CSST2'...
IIF([fldProductID] = 'COMBO1' OR 'COMBO2'...

The correct way for the first one  would be:
IIF([fldProductID] = 'CSST1' OR [fldProductID] = 'CSST2'...
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
At what point did these "mega" expressions not return the correct result?
Are you asking us to troubleshoot both entire expressions?

Anytime your expression gets that complex, consider using a function instead.
With a function you will have:
Error handling
Comments
Portability
Debugging
...etc

And *Keep It Simple* and build and verify each IIF (or IF or SWITCH) separately then add it to the main expression one segment at a time.
...*then* worry about concatenation...

This way your question is:
"Hey Guys, ..This worked___________, but as soon as I did this__________, ...it failed"

This way we are only focusing in on the part that failed, and not trying to troubleshoot the entire mega expression.
Sound fair?

;-)

JeffCoachman
Avatar of APD Toronto

ASKER

Irog,

I'm still getting results mismatched with my desk check, but does the following look correct?

?strcsstpass1
, SUM(IIF(fldProductID IN ('CSST1', 'CSST2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) ), Nz([fldFamQty],0) * 4 +Nz([fldAdultQty],0) +Nz([fldSenQty],0) +Nz([fldStudQty],0) +Nz([fldChildQty],0) , 0) ) AS CSSTPass1 

?strsql
SELECT [tblLocs].[fldLocation]  AS HOTEL  , [tblAgents].[fldLName] & ', ' & [tblAgents].[fldFName] AS AGENT , SUM(IIF(fldProductID IN ('CSST1', 'CSST2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) ), Nz([fldFamQty],0) * 4 +Nz([fldAdultQty],0) +Nz([fldSenQty],0) +Nz([fldStudQty],0) +Nz([fldChildQty],0) , 0) ) AS CSSTPass1  , SUM( IIF( (fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) , IIF([fldProductID] = 'CSST1' OR [fldProductID] = 'CSST2', 1, IIF([fldProductID] = 'COMBO1' OR [fldProductID] = 'COMBO2', 0.33, 0)) * ( Nz([fldFamQty], 0 ) * Nz([fldFamRate], 0 )) + ( Nz([fldAdultQty], 0 ) * Nz([fldAdultRate], 0 )) + ( Nz([fldSenQty], 0 ) * Nz([fldSenRate], 0 )) + ( Nz([fldStudQty], 0 ) * Nz([fldStudRate], 0 )) + ( Nz([fldChildQty], 0 ) * Nz([fldChildRate], 0 )) + ( Nz([fldInfQty], 0 ) * Nz([fldInfRate], 0 )) , 0) ) AS CSSTSales1 , SUM(IIF(fldProductID IN ('CSSN1', 'CSSN2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) ), Nz([fldFamQty],0
) * 4 +Nz([fldAdultQty],0) +Nz([fldSenQty],0) +Nz([fldStudQty],0) +Nz([fldChildQty],0) , 0) ) AS CSSNPass1  , SUM( IIF( (fldPickupDate BETWEEN #2012-06-01# AND #2012-06-30# ) , IIF([fldProductID] = 'CSSN1' OR [fldProductID] = 'CSSN2', 1, IIF([fldProductID] = 'COMBO1' OR [fldProductID] = 'COMBO2', 0.67, 0)) * ( Nz([fldFamQty], 0 ) * Nz([fldFamRate], 0 )) + ( Nz([fldAdultQty], 0 ) * Nz([fldAdultRate], 0 )) + ( Nz([fldSenQty], 0 ) * Nz([fldSenRate], 0 )) + ( Nz([fldStudQty], 0 ) * Nz([fldStudRate], 0 )) + ( Nz([fldChildQty], 0 ) * Nz([fldChildRate], 0 )) + ( Nz([fldInfQty], 0 ) * Nz([fldInfRate], 0 )) , 0) ) AS CSSNSales1 , SUM(IIF(fldProductID IN ('CSST1', 'CSST2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) ), Nz([fldFamQty],0) * 4 +Nz([fldAdultQty],0) +Nz([fldSenQty],0) +Nz([fldStudQty],0) +Nz([fldChildQty],0) , 0) ) AS CSSTPass2  , SUM( IIF( (fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) , IIF([fldProductID] = 'CSST1' OR [fldProductID] = 'CSST2', 1, IIF([fldProduc
tID] = 'COMBO1' OR [fldProductID] = 'COMBO2', 0.33, 0)) * ( Nz([fldFamQty], 0 ) * Nz([fldFamRate], 0 )) + ( Nz([fldAdultQty], 0 ) * Nz([fldAdultRate], 0 )) + ( Nz([fldSenQty], 0 ) * Nz([fldSenRate], 0 )) + ( Nz([fldStudQty], 0 ) * Nz([fldStudRate], 0 )) + ( Nz([fldChildQty], 0 ) * Nz([fldChildRate], 0 )) + ( Nz([fldInfQty], 0 ) * Nz([fldInfRate], 0 )) , 0) ) AS CSSTSales2 , SUM(IIF(fldProductID IN ('CSSN1', 'CSSN2', 'COMBO1', 'COMBO2') AND ((fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) ), Nz([fldFamQty],0) * 4 +Nz([fldAdultQty],0) +Nz([fldSenQty],0) +Nz([fldStudQty],0) +Nz([fldChildQty],0) , 0) ) AS CSSNPass2  , SUM( IIF( (fldPickupDate BETWEEN #2012-07-01# AND #2012-07-31# ) , IIF([fldProductID] = 'CSSN1' OR [fldProductID] = 'CSSN2', 1, IIF([fldProductID] = 'COMBO1' OR [fldProductID] = 'COMBO2', 0.67, 0)) * ( Nz([fldFamQty], 0 ) * Nz([fldFamRate], 0 )) + ( Nz([fldAdultQty], 0 ) * Nz([fldAdultRate], 0 )) + ( Nz([fldSenQty], 0 ) * Nz([fldSenRate], 0 )) + ( Nz([fldStudQty], 0 ) * Nz([fldStudRate], 0 )
) + ( Nz([fldChildQty], 0 ) * Nz([fldChildRate], 0 )) + ( Nz([fldInfQty], 0 ) * Nz([fldInfRate], 0 )) , 0) ) AS CSSNSales2 FROM [tblReservations] AS tblRes, [tblLocations] AS tblLocs, tblAgents  WHERE ([tblRes].fldArchived = False) AND (fldTripType <> 'E')  AND ([tblRes].fldBookLoc = [tblLocs].fldLocID)  AND ([tblRes].fldAgent = [tblAgents].fldAgentID) GROUP BY [tblLocs].[fldLocation], [tblAgents].[fldLName] & ', ' & [tblAgents].[fldFName] 

Open in new window

I'm wondering why you aren't just using a query instead of building one with a sql string?  This would be much clearer in a query along with a function to return the sale amount.
isn't that basically the same thing?
Using Query By Example (QBE) is easier to read and work with when using complex SQL strings such as what you have.
User generated imageIn addition, a function call such as below is much more readable plus it's easier to find problems inside the function code.
GetCSSTSale([fldFamQty], [fldAdultQty], fldSenQty], [fldStudQty], [fldChildQty], [fldInfQty]) AS CSSTSales1