|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi Guys, I am trying to calculate ageing on transactions, but whilst it produces the correct total it seems not to work correctly displaying some columns as zero when I know that there are entries. I have tried using BETWEEN and DATEDIFF and cannot get either to work correctly. I am passing in the end of day for the @CheckDate: Code:
parameter = command.CreateParameter();
parameter.ParameterName = "@LinkTypeId";
parameter.Value = (int)ListType.Customers;
parameter.DbType = DbType.Int32;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@CheckDate";
parameter.Value = DateUtils.GetEndOfDay(checkDate);
parameter.DbType = DbType.DateTime;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@SalesInvoiceTransactionTypeId";
parameter.Value = (int)TransactionType.SalesInvoice;
parameter.DbType = DbType.Int32;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@SalesCreditTransactionTypeId";
parameter.Value = (int)TransactionType.SalesCredit;
parameter.DbType = DbType.Int32;
command.Parameters.Add(parameter);
command.CommandText = @"
SELECT AccountName, CurrentPeriod, ThirtyDays, SixtyDays, NinetyDays, Older, Total FROM
(SELECT Customers.[AccountName],
SUM(CASE WHEN DocHeader.[DueDate] > @CheckDate THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)) ELSE 0 END) AS CurrentPeriod,
SUM(CASE WHEN DocHeader.[DueDate] BETWEEN @CheckDate AND @CheckDate-30 THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS ThirtyDays,
SUM(CASE WHEN DocHeader.[DueDate] BETWEEN @CheckDate-31 AND @CheckDate-60 THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS SixtyDays,
SUM(CASE WHEN DocHeader.[DueDate] BETWEEN @CheckDate-61 AND @CheckDate-90 THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS NinetyDays,
SUM(CASE WHEN DocHeader.[DueDate] < @CheckDate-90 THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS Older,
SUM(ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) -Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - (GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated]) ) AS Total
FROM GeneralJournal
LEFT JOIN Customers ON GeneralJournal.[LinkId] = Customers.[CustomerId]
LEFT JOIN DocHeader ON GeneralJournal.TransactionId = DocHeader.TransactionId
WHERE GeneralJournal.[MasterRecord] AND GeneralJournal.[LinkTypeId] = @LinkTypeId
AND (GeneralJournal.[TransactionTypeId] = @SalesInvoiceTransactionTypeId
OR GeneralJournal.[TransactionTypeId] = @SalesCreditTransactionTypeId)
AND
ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) - Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - (GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated]) != 0
GROUP BY Customers.AccountName)
ORDER BY AccountName;
";
Can anyone suggest where I am going wrong and how I can make this work consistently?
|
|
|
|
Rank: VistaDB Staff
Groups: Administration, VistaDB Staff
Joined: 8/13/2006
Posts: 3,387
Points: 3,946
Location: Mount Dora, Florida
|
In general you have to flatten the datetimes to include a 00:00:00 for the time. targetdatetime >= '2009-01-01 00:00:00' and < '2009-01-02 00:00:00' That will give everything for a specific day. You can't compare the datetime with something like getdate.add some number because it will include the time component. And if all your fields have a time component, you would either have to flatten them first, or change your compares to work with the entire time range. Flattening each value in a row is a lot more expensive (in general). This is a pretty common use case with SQL Server for needing date range tests. When you get to grouping on dates and want them all to flatten to just by date and ignore the time it gets more complicated. I think that was why Microsoft finally implemented date and time as column types, to make these types of things easier. Searching for SQL Server date range comparison should give some examples. Jason Short
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
Chris, We do something similar except we use a StartOfDay and EndOfDay methods to return '2009-01-01 00:00:00' and '2009-01-02 23:59:59' Code:private static DateTime EndOfDay(DateTime dateTime)
{
return StartOfDay(dateTime).AddDays(1).AddMilliseconds(-1);
}
private static DateTime StartOfDay(DateTime dateTime)
{
return new DateTime(dateTime.Year, dateTime.Month, dateTime.Day);
} then do a BETWEEN using these two values.
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi David and Jason, Umm.... Is it me? I am really sorry if I continue to be unclear, but I thought that I had covered it in the initial question when I said: Quote:I am passing in the end of day for the @CheckDate: As indicated in the original question the columns are DateTime, and I am comparing with dd/mm/yyyy 23:59:59 - is this not what you are both suggesting? Are you suggesting something else that I am not understanding?
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
Chris, Can I clarify, are you seeing info in the total column for which there is no (or incorrect) data in aged columns? If yes, remove the aged columns that should be zero (for testing) and simpolify the CASE condition (you should now have someshing like Code:SUM(( CASE WHEN DocHeader.[DueDate] BETWEEN @CheckDate AND @CheckDate-30 THEN
COALESCE(CurrencyDebitAmount, 0)
-Coalesce(CurrencyCreditAmount,0)
-GeneralJournal.[CurrencyAmountPaid]
-GeneralJournal.[CurrencyAmountAllocated]
END
)) AS ThirtyDays then check the values. NOTE if "GeneralJournal.[CurrencyAmountPaid]" or "GeneralJournal.[CurrencyAmountAllocated]" is null the result will be null. Also I have removed the brackets surrounding these fields and adjusted the arithmetic symbols as appropriate. One small criticism, the inner SQL groups on "Customers.[AccountName]" if this column is not the primary key, nor a unique index on the Customers table, then tere is a possiblity of duplicate records (Companys with the same name) giving incorrect results. I don't think this is the reason for your errors, but it is open to abuse.
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi David, I sometimes get an answer, and sometimes not... In the first few items I have a total, but no entries in any of the ageing columns which is obviously wrong. In others I have a figure in some columns. If I change the check date to 31/12/2009 then the initial test entry appears in the first column. If I set it at 31/01/10 then it does not appear in any columns. I used End Of Day with Between because it is inclusive, intending that this would give the correct dates. Perhaps I have messed up on the algorithm but as far as I can see it ought to work! I just wondered if this was the problem, given that changing the date brings it into current but it does not appear in 30 days. In relation to the other issues you raise: • Removing the other columns makes no difference. • AmountPaid and AmountAllocated are non-nullable columns, hence my not bothering to colaesce them. • Customers.[AccountName] is indexed uniquely, hopefully removing the possibility of duplicates. If you wish I can email you sample data and schema.
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi David, In case it helps, swapping to your case condition comes back with an empty column, whereas mine comes back with zero...
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
Chris, email the data needed to replicate the problem along with any paramter values needed
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
Chris, It looks like the BETWEEN function, the SQL Code:SELECT AccountName, CurrentPeriod, ThirtyDays, SixtyDays, NinetyDays, Older, Total FROM
(SELECT Customers.[AccountName],
SUM(CASE WHEN DocHeader.[DueDate] > @CHECKDATE THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)) ELSE 0 END) AS CurrentPeriod,
SUM(CASE WHEN DocHeader.[DueDate]>(@CHECKDATE-30) AND duedate<@CHECKDATE THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS ThirtyDays,
SUM(CASE WHEN DocHeader.[DueDate]>(@CHECKDATE-31) AND duedate<(@CHECKDATE-60) THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS SixtyDays,
SUM(CASE WHEN DocHeader.[DueDate]>(@CHECKDATE-61) AND duedate<(@CHECKDATE-120) THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS NinetyDays,
SUM(CASE WHEN DocHeader.[DueDate] < (@CHECKDATE-90) THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS Older,
SUM(ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) -Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - (GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated]) ) AS Total
FROM GeneralJournal
LEFT JOIN Customers ON GeneralJournal.[LinkId] = Customers.[CustomerId]
LEFT JOIN DocHeader ON GeneralJournal.TransactionId = DocHeader.TransactionId
WHERE GeneralJournal.[MasterRecord] AND GeneralJournal.[LinkTypeId] = 9
AND (GeneralJournal.[TransactionTypeId] = 13
OR GeneralJournal.[TransactionTypeId] = 14)
AND
ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) - Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - (GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated]) != 0
GROUP BY Customers.AccountName)
ORDER BY AccountName; seems to work OK, tho' it need checked for accuracy. I would raise a ticket for this, sending the data you sent me.
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi David, Ticket raised, as you suggested. BETWEEN is inclusive, so the query needed a little work to get the right results - basically the 31 and 61 days were no longer needed and an equality operator had to be added. As far as I can tell the following now works correctly: Code:SELECT AccountName, CurrentPeriod, ThirtyDays, SixtyDays, NinetyDays, Older, Total FROM
(SELECT Customers.[AccountName],
SUM(CASE WHEN DocHeader.[DueDate] > @CHECKDATE THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)) ELSE 0 END) AS CurrentPeriod,
SUM(CASE WHEN DocHeader.[DueDate] >(@CHECKDATE-30) AND DocHeader.[DueDate] <= @CHECKDATE THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS ThirtyDays,
SUM(CASE WHEN DocHeader.[DueDate] >(@CHECKDATE-60) AND DocHeader.[DueDate] <= (@CHECKDATE-30) THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS SixtyDays,
SUM(CASE WHEN DocHeader.[DueDate] >(@CHECKDATE-90) AND DocHeader.[DueDate] <= (@CHECKDATE-60) THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS NinetyDays,
SUM(CASE WHEN DocHeader.[DueDate] <= (@CHECKDATE-90) THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-(GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated])) ELSE 0 END) AS Older,
SUM(ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) -Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - (GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated]) ) AS Total
FROM GeneralJournal
LEFT JOIN Customers ON GeneralJournal.[LinkId] = Customers.[CustomerId]
LEFT JOIN DocHeader ON GeneralJournal.TransactionId = DocHeader.TransactionId
WHERE GeneralJournal.[MasterRecord] AND GeneralJournal.[LinkTypeId] = 9
AND (GeneralJournal.[TransactionTypeId] = 13
OR GeneralJournal.[TransactionTypeId] = 14)
AND
ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) - Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - (GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated]) != 0
GROUP BY Customers.AccountName)
ORDER BY AccountName; Naturally there will be more testing to do, but all tests so far have been positive. Once that is done I can use the same pattern to update all the other queries of the same type in the application and we should be home and dry. Thanks again for your help.
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
Chris, One isty bitsy thing, just the perfectionist in me. Some o you calculations consist of A-B-(C+D), I think it would be more efficient to use A-B-C-D Also if the total column is the sum of the previous column, it would be better to calculate it outside the sub-select, so the final SQL statement would be Code:SELECT AccountName,
CurrentPeriod,
ThirtyDays,
SixtyDays,
NinetyDays,
Older,
(CurrentPeriod+ThirtyDays+SixtyDays+NinetyDays+Older) AS Total FROM
( SELECT Customers.[AccountName],
SUM( CASE WHEN DocHeader.[DueDate] > @CHECKDATE THEN
(COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0))
ELSE
0
END
) AS CurrentPeriod,
SUM( CASE WHEN DocHeader.[DueDate]>(@CHECKDATE-30) AND duedate<@CHECKDATE THEN
(COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-GeneralJournal.[CurrencyAmountPaid]-GeneralJournal.[CurrencyAmountAllocated])
ELSE
0
END
) AS ThirtyDays,
SUM( CASE WHEN DocHeader.[DueDate]>(@CHECKDATE-31) AND duedate<(@CHECKDATE-60) THEN
(COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-GeneralJournal.[CurrencyAmountPaid]-GeneralJournal.[CurrencyAmountAllocated])
ELSE
0
END
) AS SixtyDays,
SUM( CASE WHEN DocHeader.[DueDate]>(@CHECKDATE-61) AND duedate<(@CHECKDATE-120) THEN
(COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-GeneralJournal.[CurrencyAmountPaid]-GeneralJournal.[CurrencyAmountAllocated])
ELSE
0
END
) AS NinetyDays,
SUM( CASE WHEN DocHeader.[DueDate] < (@CHECKDATE-90) THEN
(COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0)-GeneralJournal.[CurrencyAmountPaid]-GeneralJournal.[CurrencyAmountAllocated])
ELSE
0
END
) AS Older,
SUM(ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) -Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - GeneralJournal.[CurrencyAmountPaid]-GeneralJournal.[CurrencyAmountAllocated] ) AS Total
FROM GeneralJournal
LEFT JOIN Customers ON GeneralJournal.[LinkId] = Customers.[CustomerId]
LEFT JOIN DocHeader ON GeneralJournal.TransactionId = DocHeader.TransactionId
WHERE GeneralJournal.[MasterRecord] AND GeneralJournal.[LinkTypeId] = 9
AND (GeneralJournal.[TransactionTypeId] = 13 OR GeneralJournal.[TransactionTypeId] = 14)
AND ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) - Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - (GeneralJournal.[CurrencyAmountPaid]+GeneralJournal.[CurrencyAmountAllocated]) != 0
GROUP BY Customers.AccountName
)
ORDER BY AccountName;
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi David, Thanks for the extra suggestions. I note in the code you have provided the equality check is missing and the total is being calculated both inside and outside the sub select - I take it these are typos rather than deliberate changes to the code?
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi David, I see what happened - you used a previous one as your starting point. OK, I will revamp the working one I posted along your lines and see what happens. Thanks again.
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Hi David, In reviewing the code you posted I noticed that I had missed the deduction of payments and credits on the CurrentPeriod which was a major drop-off and is now fixed. The Total should be the total of the columns, but I left it inside the sub select to allow me to do a comparison between the total calculated from the data and the total calculated from the columns. I will consider totalling the columns instead once I am totally confident of the results. I have taken on board replacing the second part of the calculation with all negatives as you suggested - thanks for that. New code so far, which seems to work well: Code:DECLARE @CHECKDATE DateTime;
SET @CHECKDATE = '31/01/2010 23:59:59';
SELECT AccountName, CurrentPeriod, ThirtyDays, SixtyDays, NinetyDays, Older, Total FROM
(SELECT Customers.[AccountName],
SUM(CASE WHEN DocHeader.[DueDate] > @CHECKDATE THEN (COALESCE(CurrencyDebitAmount, 0) - Coalesce(CurrencyCreditAmount,0) - GeneralJournal.[CurrencyAmountPaid] - GeneralJournal.[CurrencyAmountAllocated]) ELSE 0 END) AS CurrentPeriod,
SUM(CASE WHEN DocHeader.[DueDate] >(@CHECKDATE-30) AND DocHeader.[DueDate] <= @CHECKDATE THEN (COALESCE(CurrencyDebitAmount, 0) - Coalesce(CurrencyCreditAmount,0) - GeneralJournal.[CurrencyAmountPaid] - GeneralJournal.[CurrencyAmountAllocated]) ELSE 0 END) AS ThirtyDays,
SUM(CASE WHEN DocHeader.[DueDate] >(@CHECKDATE-60) AND DocHeader.[DueDate] <= (@CHECKDATE-30) THEN (COALESCE(CurrencyDebitAmount, 0)- Coalesce(CurrencyCreditAmount,0) - GeneralJournal.[CurrencyAmountPaid] - GeneralJournal.[CurrencyAmountAllocated]) ELSE 0 END) AS SixtyDays,
SUM(CASE WHEN DocHeader.[DueDate] >(@CHECKDATE-90) AND DocHeader.[DueDate] <= (@CHECKDATE-60) THEN (COALESCE(CurrencyDebitAmount, 0)- Coalesce(CurrencyCreditAmount,0) - GeneralJournal.[CurrencyAmountPaid] - GeneralJournal.[CurrencyAmountAllocated]) ELSE 0 END) AS NinetyDays,
SUM(CASE WHEN DocHeader.[DueDate] <= (@CHECKDATE-90) THEN (COALESCE(CurrencyDebitAmount, 0)-Coalesce(CurrencyCreditAmount,0) - GeneralJournal.[CurrencyAmountPaid] - GeneralJournal.[CurrencyAmountAllocated]) ELSE 0 END) AS Older,
SUM(ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) - Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - GeneralJournal.[CurrencyAmountPaid]-GeneralJournal.[CurrencyAmountAllocated]) AS Total
FROM GeneralJournal
LEFT JOIN Customers ON GeneralJournal.[LinkId] = Customers.[CustomerId]
LEFT JOIN DocHeader ON GeneralJournal.TransactionId = DocHeader.TransactionId
WHERE GeneralJournal.[MasterRecord] AND GeneralJournal.[LinkTypeId] = 9
AND (GeneralJournal.[TransactionTypeId] = 13
OR GeneralJournal.[TransactionTypeId] = 14)
AND
ABS(COALESCE(GeneralJournal.[CurrencyDebitAmount], 0) - Coalesce(GeneralJournal.[CurrencyCreditAmount], 0)) - GeneralJournal.[CurrencyAmountPaid]-GeneralJournal.[CurrencyAmountAllocated] != 0
GROUP BY Customers.AccountName)
ORDER BY AccountName;
|
|
|
|
Rank: VistaDB Staff
Groups: Administration, VistaDB Staff
Joined: 7/5/2008
Posts: 273
Points: 1,752
Location: Mount Dora, Florida
|
Note: I am posting the follow up the between issue here so anybody who searches this issue will know the proper answer. Also you can find more information on the issues and caveats of between through these stack overflow articles. http://stackoverflow.com/questi...owest-and-highes/1022005http://stackoverflow.com/questi.../sql-query-by-date-rangeChris, I checked your query and the reason the betweens are not giving you the results you want is because the ranges are backwards. BETWEEN validates a low range AND a high range. Your query of DocHeader.[DueDate] BETWEEN @CheckDate AND (@CheckDate-30) was a high range and a low range. Thus between would never validate. This is the same behavior SQL server has, and even access. Reversing the low and high values fixes this problem.
Michael Swain
VistaDB Software
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 357
Points: 528
Location: Seaford, East Sussex, UK
|
Thanks Mike for the information - obviously something of which all contributors were unaware, and something to remember for the future. As always, great support from VistaDB.
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
|
|
|
|
Guest
|