I see what you are doing, I think I tried to do that but then I was not able to figure out how to group it by item 1, item...

I tried it again:

SELECT

[Customer No_]

,SUM(CASE YEAR(cle.[Posting Date])

WHEN YEAR(dateadd(yy, datediff(yy, 0, getdate()) - 1, 0))

THEN [Sales (LCY)]

ELSE 0

END) AS low_yr

,SUM(CASE YEAR(cle.[Posting Date])

WHEN YEAR(DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0))

THEN [Sales (LCY)]

ELSE 0

END) AS...

Is there a way that the year part of the case expression in the select can be done without using hard coded values for previous year, and current year?

,SUM(CASE YEAR(cle.[Posting...

Thank you.

I attached one more time the insert data script.

I made sure I ran it in my development environment and for only a few customers:

('10002', '10003', '10006', '10014', '10019', '10020', '10022',...

Here is some output I verified for some customers.

Customer NumberAverage Days To Pay

1000234

100031

1000639

1001447

1001994

1002012

1002223

1002325

10024-2

1002654

1002717

100310

1003939

1004122

1004254

I hope this is good to get help for solution.

I truly appreciate your willingness to help.

I fixed the data.

Hi Sean, I trimmed the number of rows to 500 just to make sure there is enough data to work the 3 methods that calculate the DaysToPay value.

The output...

please see the image attached.

it only shows for one item number, i want to do it for each item that is

X15816

X16061

X16085

X16092

X16093

X16128

X16129

thank you

I would want to obtain the prior quote number, prior item number, prior quote date for all items that are part of quote number QUO-10566-0

no duplicates, different quote number and quote date

this is my script, create and insert.

i want to point out that if i run this script:

select * from [dbo].[salesquotestest] WHERE prioriquotedate IN (SELECT MAX(prioriquotedate) FROM [dbo].[salesquotestest] )

i get...

will do. thank you

Hi Sean,

This works but the thing is that I can have multiple ItemNumbers and each can have their own prior quote number

example

item number prior quote number

X15816...

script and output

thank you.