1

Topic: At date change swears

Hello.
Prompt please in what there can be a reason. The request works, all correctly displays, but at change of date in the filter starts to produce
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to float.
Warning: Null value is eliminated by an aggregate or other SET operation.
I can not understand in what the reason

on [tab2]. [HOLEID] = [DRILLINGDETAIL]. [HOLEID]
where [DRILLINGDETAIL]. [PROJECTCODE] = ' uz'
and cast ([DRILLINGDETAIL]. [DRILLINGDATE] as date)
=' 27-oct-2017'

as soon as instead of October, 27th I put for example on July, 15th, produces the code about an error

2

Re: At date change swears

katish444;
At first, initially column [DRILLINGDETAIL]. [DRILLINGDATE] what data type has?
Secondly, string literals with dates are better for writing down in an ANSI-format: YYYY-MM-dd
Thirdly, it is better to do any functional conversions with constants and external variables, instead of with columns of tables not to discourage the server to work with indexes on the given fields...

3

Re: At date change swears

  - Eh wrote:

katish444;
At first, initially column [DRILLINGDETAIL]. [DRILLINGDATE] what data type has?
Secondly, string literals with dates are better for writing down in an ANSI-format: YYYY-MM-dd
Thirdly, it is better to do any functional conversions with constants and external variables, instead of with columns of tables not to discourage the server to work with indexes on the given fields...

Thanks, I do not know what exactly, but something helped

4

Re: At date change swears

  - Eh wrote:

katish444;
At first, initially column [DRILLINGDETAIL]. [DRILLINGDATE] what data type has?
Secondly, string literals with dates are better for writing down in an ANSI-format: YYYY-MM-dd
Thirdly, it is better to do any functional conversions with constants and external variables, instead of with columns of tables not to discourage the server to work with indexes on the given fields...

Prompt please what request it is possible to create a column which would count how many slits "MA", and how many "UZ"
Well  while x_project did not change, he would consider and on the contrary wrote down

5

Re: At date change swears

Kind  - Eh;

COUNT (x_project over (partition by h.projectcode order by h.holeID) end as count_project

I here so try, but something I do not correctly

6

Re: At date change swears

katish444;
And how in total sampling this column should look?

7

Re: At date change swears

  - Eh wrote:

katish444;
And how in total sampling this column should look?

I think in the first cell as well as x_project, near an amount of slits, and at me it enumerates them, and the total, does not give

8

Re: At date change swears

katish444;
Values in x_project show "a change point" slit type?
If NULL, the same type, what the first NOT NULL above?
If to do as you you want, at first it is necessary to fill all NULL-values with the necessary type.
There is a suspicion that earlier you on the contrary made them step NULL of normal values. Return on this step and  with calculation x_project make calculation of an amount of lines with the given type.

9

Re: At date change swears

  - Eh wrote:

katish444;
Values in x_project show "a change point" slit type?
If NULL, the same type, what the first NOT NULL above?
If to do as you you want, at first it is necessary to fill all NULL-values with the necessary type.
There is a suspicion that earlier you on the contrary made them step NULL of normal values. Return on this step and  with calculation x_project make calculation of an amount of lines with the given type.

, precisely, I also made their zero, it is necessary to write down before it my line of count or a subquery to make
Reached me! Thanks big!!!