Please Help with Date Month to Date and Year to Date

[TJVFree]

Hi,

I apologize for asking a question that I'm sure most of you feel is a pretty easy question, but I wasn’t able to find it on the web either.

I'm trying to calculate the Inventory$ for each day, month to date and year to date. is there a easy way for me to run this in a sql

/code
SELECT
Customers@.CLIENTNUMBER AS 'ClientNumber',
COLLCLT@.CLIENTNAME AS 'ClientName',
COUNT(Customers@.CLIENTDEBTORNUMBER) AS 'NumberAccts',
SUM(Customers@.PRINCIPALBALANCE) AS 'Inventory$',
COLLCLT@.LASTBUSDATE AS 'LastBusDate',
COLLCLT@.LASTBUSDATE AS 'LastBusDate1'
FROM Customers@
INNER JOIN COLLDEBT@ ON Customers@.MASTERACCOUNT = COLLDEBT@.RECNUM
INNER JOIN COLLCLT@ ON COLLCLT@.RECNUM = Customers@.CLIENTNUMBER
LEFT JOIN U_CLIENTLINKFIELDS@ ON U_CLIENTLINKFIELDS@.CLIENTLINK = COLLCLT@.RECNUM
WHERE

AND LastBusDate as Current > CURRENT_DATE(-1)
AND LastBusDate1 as MTD< CURRENT_DATE(-30)
AND LastBusDate1 as YTD< CURRENT_DATE(-365)
GROUP BY
ClientNumber
ORDER BY
ClientName]
code/


Thanks you - TCB

TCB

 

[dhookom]

I always thought that inventory was a point in time value. How would you calculate "month to date and year to date"?

You SQL statement doesn't really describe your tables, fields, and data. I'm not sure what version of SQL you posted.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Inventory is like a bank balance. You have PUT and TAKE transactions (and possibly moves between accounts or locations but even these can be structured as puts and takes)

So you might say that year to date you have a put amount and a take amount which indicates a level of volitivity, in order to count inventory turns.

 

[TJVFree]

Could we make the data below into columns or is it my sql that you dont understand

Date()-1 Month()-1 Year() - 1

AND LastBusDate as Current > CURRENT_DATE(-1)
AND LastBusDate1 as MTD< CURRENT_DATE(-30)
AND LastBusDate1 as YTD< CURRENT_DATE(-365)

-------------------------------------------
yesterday or Friday / MTD / YTD
------------------------------------------

TCB

 

[dhookom]

I would need some sample records with table and field names as well as the desired output.

Duane
Hook'D on Access
MS Access MVP