Incorrect prefix error (select count statement)

Question

Incorrect prefix error (select count statement)

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

SQLPain Hall of Fame Points: 3522 More actions · Answer 1

Thanks Michael, I really appreciate your help. I had figured it out right after I posted yesterday what was missing, I normally don't do in line statements. this was a code that was written by someone a beginner but somehow working. the idea of doing count on the joins was a new concept for me too.

currently I am working on ApprovedLastyear_Count, ConditionedDecline_Count etc. Hopefully I would not get stuck further 🙂 fingers crossed.

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

Also for future reference, whenever I am doing a count, the following way of doing it with JOINS is it the best way and should be used no matter what?

Michael L John One Orange Chip Points: 27079 More actions · Answer 3

Same technique, these can be a series of sub-queries.

Is this the fastest execution? Probably not. Not very elegant either.

But when it come to reports, there is one constant. Someone else will need to look at this, and the requirements will change. I tend to write a bunch of little pieces and put them together in these cases.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SQLPain Hall of Fame Points: 3522 More actions · Answer 4

For the this year Funded, approved, etc the following where statement should work correct?

WHERE c2.DateContractFunded >= dateadd(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0))

Michael L John One Orange Chip Points: 27079 More actions · Answer 5

SQLPain (10/8/2015)
Also for future reference, whenever I am doing a count, the following way of doing it with JOINS is it the best way and should be used no matter what?

It depends!

In this case, there is a one to many relationship. You can directly join these tables, and group by all of the other fields, but the subquery method is probably faster.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27079 More actions · Answer 6

SQLPain (10/8/2015)
For the this year Funded, approved, etc the following where statement should work correct?
WHERE c2.DateContractFunded >= dateadd(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0))

Yes. Look at your code for to get last year. It's the same thing, except you are not adding -1 to the year.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SQLPain Hall of Fame Points: 3522 More actions · Answer 7

SQLPain

Hall of Fame

Points: 3522

October 8, 2015 at 10:41 am

#1832363

You have been great help Michael

Michael L John One Orange Chip Points: 27079 More actions · Answer 8

Michael L John (10/8/2015)
SQLPain (10/8/2015)
For the this year Funded, approved, etc the following where statement should work correct?
WHERE c2.DateContractFunded >= dateadd(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0))
Yes. Look at your code for to get last year. It's the same thing, except you are not adding -1 to the year.

Break each of the parts down and learn what each does.

SELECT getdate() = Returns the current data and time.

SELECT DATEDIFF(yy, 0, getdate()) = Returns the number of years between 0 (which translates to 1900-01-01 00:00:00.000) and now. 115

SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) = Adds 115 to 1900-01-01 00:00:00.000, which gives you 01-01-2015 00:00:000

SELECT dateadd(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) = Adds nothing to the above answer. Not really needed for this.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SQLPain Hall of Fame Points: 3522 More actions · Answer 9

SQLPain

Hall of Fame

Points: 3522

October 8, 2015 at 1:32 pm

#1832400

Thanks again