Waht do the numbers mean in this line of code 2

[Cpreston]

Hi

we have a view which as this line of code in (sorry not to experienced with coding), could someone explain what the 4 and 36,4 means please

CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36 , 4))

Thanks

 

[Cpreston]

Also just to add to this, I have found the problem area of the following code which I also would like some advice please.

This currently does not bring any rows back. However if I take the line below out then it brings in information.

AND(dbo.WorksOrderHeader.DateTimeCreated = CONVERT(varchar(19), GETDATE()-1, 6))

I cannot work out what the line is actually trying to do other than covnert the date format, use todays date -1 day. I am not sure what the 6 is for.
I have tried to edit the row but still get no data back. could someone advise how to fix the row so it works with the rest of the code. Thanks


SELECT dbo.WorksOrderHeader.WorksOrderNumber, dbo.WorksOrderHeader.DateTimeCreated AS [WO Created],
dbo.WorksOrderHeader.TotalVolumeIn, dbo.WorksOrderHeader.TotalVolumeOut,
CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36, 4)) AS Difference
FROM dbo.WorksOrderHeader INNER JOIN
dbo.Users ON dbo.WorksOrderHeader.CreatedByID = dbo.Users.UserID
WHERE (dbo.Users.UserID = 125)AND(CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36, 4)) > 0.1 OR
CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36, 4)) < - 0.1
)AND(dbo.WorksOrderHeader.DateTimeCreated = CONVERT(varchar(19), GETDATE()-1, 6))
ORDER BY dbo.WorksOrderHeader.DateTimeCreated

 

[fredericofonseca]

you really need to read the manual of TSQL. These are all basic questions that could easily be cleared by reading them.

cast - you cast something as a datatype - some datatypes will have "parameters" and that is the case for the 36,4 bit.
round - that should be self explanatory but think about it - when you round something what do you round it to? or how do you round it?

for the AND bit - this is related to how the datetimecreated is stored - is it a datetime? if so does it have the time period? and if it is a date how will year 2015 compare to string 15? as your convert is converting dates to a string in format "dd mmm yy" - what do you think sql server will do if it tries to compare a date to a string in this format? will it convert the date to a string and compare? or will it convert the string to a date and then do the compare?
if datetimecreated is a string is it stored as format "dd mmm yy"? if not then this will be the reason for not meeting the criteria.
so to tell you what you should do here you need to look at some of the records you are retrieving and post their format and datatype here so we can advise.


for readability i have rewriten the query above using alias - and then rewrote it again to move the 3x calculatioin to a outer apply - may or not perform better. intention here is to show you other ways of doing the same thing.

SELECT woh.WorksOrderNumber
     , woh.DateTimeCreated AS [WO Created]
     , woh.TotalVolumeIn
     , woh.TotalVolumeOut
     , CAST(ROUND(woh.TotalVolumeIn - woh.TotalVolumeOut, 4) AS numeric(36, 4)) AS Difference
FROM dbo.WorksOrderHeader woh
INNER JOIN dbo.Users users
 ON woh.CreatedByID = dbo.Users.UserID
WHERE (users.UserID = 125)
  AND (CAST(ROUND(woh.TotalVolumeIn - woh.TotalVolumeOut, 4) AS numeric(36, 4)) > 0.1
    OR CAST(ROUND(woh.TotalVolumeIn - woh.TotalVolumeOut, 4) AS numeric(36, 4)) < -0.1
      )
  AND (woh.DateTimeCreated = CONVERT(varchar(19), GETDATE() - 1, 6))
ORDER BY woh.DateTimeCreated 


SELECT woh.WorksOrderNumber
     , woh.DateTimeCreated AS [WO Created]
     , woh.TotalVolumeIn
     , woh.TotalVolumeOut
     , diff.Difference
FROM dbo.WorksOrderHeader woh
INNER JOIN dbo.Users users
 ON woh.CreatedByID = dbo.Users.UserID
outder apply (select CAST(ROUND(woh.TotalVolumeIn - woh.TotalVolumeOut, 4) AS numeric(36, 4)) AS Difference ) diff
WHERE (users.UserID = 125)
  AND (diff.Difference > 0.1
    OR diff.Difference < -0.1
      )
  AND (woh.DateTimeCreated = CONVERT(varchar(19), GETDATE() - 1, 6))
ORDER BY woh.DateTimeCreated

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Thank you for the reply, which I mostly understood.

I have ran the query using this

SELECT dbo.WorksOrderHeader.WorksOrderNumber, dbo.WorksOrderHeader.DateTimeCreated AS [WO Created],
dbo.WorksOrderHeader.TotalVolumeIn, dbo.WorksOrderHeader.TotalVolumeOut,
CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36, 4)) AS Difference
FROM dbo.WorksOrderHeader INNER JOIN
dbo.Users ON dbo.WorksOrderHeader.CreatedByID = dbo.Users.UserID
WHERE (dbo.Users.UserID = 125)AND(CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36, 4)) > 0.1 OR
CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36, 4)) < - 0.1
)and WorksOrderHeader.DateTimeCreated Between '2015-12-01 00:00:00' and '2015-12-31 00:00:00' --and WorksOrderNumber = 199690
ORDER BY dbo.WorksOrderHeader.DateTimeCreated

So forced the date and it gives me this result for the Worksorderheader.Datetimecreated

2015-12-02 11:32:00

This was just taking the dbo.WorksOrderHeader.DateTimeCreated = CONVERT(varchar(103), GETDATE()-1, 6)) line out, which I assume is incorrect code womhoe.

Is this the information you require to advise ? Thanks (Also I have read up on these things on Google where I could find information but getting something close to what it is doing and not just basic information is hard to find)

many thanks

 

[gmmastros]

CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(36 , 4))

The first 4 represents the number of digits to round to. Ex:

Select Round(1.2345678, 4)

When you run the code above, the output is: 1.2346000. Notice that there are extra zero's on the end. The number is rounded to 4 digits.

The 36,4 is for the data type. 36 represents the total number of digits. The 4 represents the number of digits after the decimal point. Instead of 36,4; consider a data type of 5,2 (because it's easier to use for examples). Numeric(5,2) can only accommodate numbers in the range of -999.99 to 999.99. Notice that it's 5 total digits and 2 after the decimal point.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

Ok understand that part now 36 seems abit extreme in this case.

So the only issue I now have is why when using this line it does not bring any information through

dbo.WorksOrderHeader.DateTimeCreated = CONVERT(varchar(103), GETDATE()-1, 6))


GETDATE()-1 I believe is todays date, minus one day is this correct
varchar (103) is the date format

Not sure what the 6 signifies.

Any ideas please? I know some data should have shown on the 5th January for definite.

Many thanks

 

[fredericofonseca]

can you give us the output of EXEC sp_help 'dbo.WorksOrderHeader'
this is for confirmation.

how lets assume that DateTimeCreated is indeed a datetime field

two ways to extract a particular date of that table based on your requirement to extract the previous days worth of data
1 - and dbo.WorksOrderHeader.DateTimeCreated >= convert(date, getdate() - 1) and convert(date, getdate()) -- if DateTimeCreated has an index on it this is the probably the best way

2 - and convert(date, dbo.WorksOrderHeader.DateTimeCreated) = convert(date, getdate() - 1)

note that in both cases I converted getdate() (which is a datetime value) to a date as this way we are dealing only with the date part which is what you are after.
on the first example we are ignoring the fact that column DateTimeCreated has a time portion as any value will be within the period we are stating which is start of today - 1 day and todays date.

On the second example we are explicitly converting DateTimeCreated to a date and then we can do a exact compare to our desired date

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

regarding manuals - read

https://technet.microsoft.com/en-us/library/bb510741(v=sql.105).aspx,

more specifically go to the transact-SQL Reference part.

Also regarding your example above
dbo.WorksOrderHeader.DateTimeCreated = CONVERT(varchar(103), GETDATE()-1, 6))


GETDATE()-1 I believe is todays date, minus one day is this correct
varchar (103) is the date format

what you are doing here is stating - convert getdate() -1 to a varchar of size 103, with date format 6 (dd mon yyyy) - hence your need to read the manuals above

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

I ran EXEC sp_help 'dbo.WorksOrderHeader' and for the datetimecreated I got this result

DateTimeCreated smalldatetime no 4 no (n/a) (n/a) NULL

Is this the only information you needed?

Of course I understand I need to read up on things and will check out the link you sent.

Thanks for the help

 

[fredericofonseca]

yes that is the information needed - it id a date type field so the code I gave you should work fine.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Great got it working now and for information in case someone else uses this as reference the final code is below. I had to add in an order status Where clause also, but that's specific to us here.

Many thanks for the help and patience.

SELECT dbo.WorksOrderHeader.WorksOrderNumber, dbo.WorksOrderHeader.DateTimeCreated AS [WO Created],
dbo.WorksOrderHeader.TotalVolumeIn, dbo.WorksOrderHeader.TotalVolumeOut,
CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(10, 4)) AS Difference
FROM dbo.WorksOrderHeader INNER JOIN
dbo.Users ON dbo.WorksOrderHeader.CreatedByID = dbo.Users.UserID
WHERE (dbo.Users.UserID = 125)AND(CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(10, 4)) > 0.1 OR
CAST(ROUND(dbo.WorksOrderHeader.TotalVolumeIn - dbo.WorksOrderHeader.TotalVolumeOut, 4) AS numeric(10, 4)) < - 0.1
)AND convert(date, dbo.WorksOrderHeader.DateTimeCreated) = convert(date, getdate() - 1)AND WorksOrderHeader.WorksOrderStatus = 5
ORDER BY dbo.WorksOrderHeader.DateTimeCreated