just test code tags

Select FSA3.CustAccount,CT1.WIKA_DUNBRAD, Sum(FSA3.LineAmount)AS Last12MonthsSalesAmt,FSA3.ITEMID,x.FirstSaleDateinLast12Months,'NewCustomer' AS Status

From (

Select Cast (getdate() AS DATE) AS SnapShotDateCustaccount,FSA1.CUSTACCOUNT,sum(FSA1.lineamount) AS AmtLocalCurrency,sum(FSA1.lineamount) AS AmtUSD,min(FSA1.invoicedate) AS FirstSaleDateinLast12Months,DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0)...

just a test of the tags

code=SQL

Select FSA3.CustAccount,CT1.WIKA_DUNBRAD, Sum(FSA3.LineAmount)AS Last12MonthsSalesAmt,FSA3.ITEMID,x.FirstSaleDateinLast12Months,'NewCustomer' AS Status

From (

Select Cast (getdate() AS DATE) AS SnapShotDateCustaccount,FSA1.CUSTACCOUNT,sum(FSA1.lineamount) AS AmtLocalCurrency,sum(FSA1.lineamount) AS AmtUSD,min(FSA1.invoicedate) AS FirstSaleDateinLast12Months,DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) -...

got it thanks, I will post here to practice, Again many thanks

so yes that worked, and as you noted so much simpler than my solution. I will "reverse engineer' you SQL statement to understand how it is working.

Not...

A couple things.

1) The posted script for creating the data won't parse (a typo in "integer" and no commas separating the VALUES for the first INSERT).

2) Both the total...

this is the full code. it is now working, but for existingvehicles it brings back multiple dates and I only want the earliest date (like new vehicles) In...

ok so I understand how to do this better. I have two tables. vehiclemaintenance and vehicle table

this is the data for vehiclemaintenance

Create Table VehicleMaintenance(

Unitid interger not null

,servicedate datetime not...

actually I am going to repost the data again. because I did not do it correctly the first person to help just used consecutive dates. that will not...

yes thank you for your help, I now understand how to post a question in the correct format so someone can create the necessary tables quickly and easily and then...

I understand, I will test it, thank you.

Ok Yes, your table create and insert statements are exactly correct.

sorry missed the comment about the vehicle table.

CREATE TABLE VehicleTable( unitid INTEGER NOT NULL ,vehiclename VARCHAR(13) NOT NULL...

ok, good points let me try this

I know my code is complex, this is probably the most complex task I have tried. Basically I wrote the code using a...

I attached a word document with my SQL code and an excel file with table data and expected results? When I say the pieces run, I mean if I...

worked perfectly thank you. got 351 individual rows instead of 1 single row. I will compare to my original code, see your changes, and learn something new :-)...

not sure I am technical enough to explain it correctly, but as it was explained to me, the ISNULL answer takes on the attributes of the original field. In...