Maybe I am not explaining my needs well.

If that's the case, I apologize.

I am a seasoned .net developer and that's what I will be using once I am done with...

I know and I agree with you but that is the least of my concerns.

My issue is how to incorporate that table with my new design so that when they...

Thank you great helpers for your insightful feedbacks.

There is just one piece of the puzzle, perhaps, the most important that I really need your expertise on.

I am attaching another screenshot....

Just another question, in other to verify that the user lives at the address s/he says s/he lives, it is better to user CustomerAcctNo or address?

CustomerAcctNo is actually Water Account...

What exactly do you mean?

Thank you very much for your insights Jeff.

I am actually not too shabby with DB design but there are certain aspects that give me some pause.

For instance, my biggest concern...

CustomerAcctNo  DECIMAL(9, 2)  ???  1234567.02 ?

This is fine.

GPF   DECIMAL(9, 2)  ?? you expect big tanks ? 9999999.99 GPF

This is way too high. Should be more like (3,2) or perhaps even...

For ZIP code, I strongly recommend NOT using VARCHAR.  Use CHAR instead.

Ok, will make the change.

My first observation is to ask if there's a 1:1 relationship between Customer # and...

OwnerEmail VARCHAR(5),    Do you mean varchar(50) ?  Or longer ?

My bad, sorry about that. this is a draft copy.

State    VARCHAR(50)  What values are allowed ? Is that better...

YEA!

You did it!!

Drew was right, this was no easy solution but you are obviously up to the task.

Here is the result, precisely what I am after:

Year_2019

60.0000

120.0000

120.0000

60.0000

80.0000

120.0000

I am assuming that when...

Hi DesNorton,

It's almost working; at least it is appending Year_ to EventYear.

However, I am getting the following error:

Msg 207, Level 16, State 1, Line 30

Invalid column name 'Year_2019'.

Do I need...

Drew, you are absolutely correct in explaining the static vs the dynamic part, thank you very much.

You said I am using the 'Year_'... incorrectly.

Where should I use it, on the...

Michael,

You said formatting is better in actual report and I agree but it was as a result of trying to use the values in report that created the issue that...

Hey Drew,

Which part of the query is static, I am curious?

If you referring to the last query I posted, that's the printed version after I did PRINT @query.

I don't see...

• This reply was modified 5 years, 3 months ago by  ciss1.

No, the query is dynamic for a reason.

This way, it will pick up and display current year.

The reason I used 2020 is because that will be the next year after...

• This reply was modified 5 years, 3 months ago by  ciss1.