Sql – Calculate Inventory Days

I’m trying to calculate inventory day’s over time. I have an issue getting the actual amount of day’s in inventory for the last month. So as for the first row, the 9 days is correct. However the second row should show 34. Since I want to calculate the days between Inv_date and “today’s” Date. Let’s presume when I’m writing this, today’s date is ‘2021-03-25’.

So I have a table such as:

Transdate	Inv_Date	Purch_Date	InvDays
2021-02-01 00:00:00.000	20210219	Null	9
2021-03-01 00:00:00.000	20210219	Null	40

What I’m expecting is:

Transdate	Inv_Date	Purch_Date	InvDays
2021-02-01 00:00:00.000	20210219	Null	9
2021-03-01 00:00:00.000	20210219	Null	34

My Sql-script for the InvdDays calculation is:

case when left(Getdate(),8) >= left(convert(varchar,Transdate,112),8) then 
datediff(day,Inv_date,
dateadd(day,-1,dateadd(month,1,Transdate))
)
else 
isnull(ABS(DATEDIFF(day, Inv_date, isnull(Purch_Date, Getdate()))),0) end as InvDaysStandQty

Answer

Hmmm . . . I speculate that you want the number of days from the last day of transdate and inv_date. That would be something like this:

select datediff(day, inv_date,
                (case when convert(date, getdate()) < eomonth(transdate)
                      then getdate() else eomonth(transdate)
                 end)
                )

There might be an off-by-1 error, because you haven’t actually explained the logic.