SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to retrieve values in a particular format


Query to retrieve values in a particular format

Author
Message
IQ1
IQ1
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 70
Hello,
I am using the following Table to get data from
I would like to use a function or code on the last column Cal value such that it matches the values shown in the column Final value
How can I do this using SQL queries please suggest ?

Thanks
IQ

The code for the table is as follows,
Table :
USE [StarDatabase]
GO

/****** Object: Table [dbo].[test] Script Date: 02/26/2018 12:26:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test](
[AccountNum] [nvarchar](20) NULL,
[MTUID] [int] NOT NULL,
[ReadingTime] [datetime] NOT NULL,
[data1] [varbinary](100) NULL,
[New_Value] [varbinary](20) NULL,
[val] [int] NULL,
[Cal value] [varchar](8000) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO



Values in the table are as follows: (Book1.xls file attached as well)
AccountNum MTUID ReadingTime data1 New_Value Val Cal Value Final_value
251250 47235827 12/31/2016 23:35 0x5C3020 0x0020305C 2109532 2109.532 21095.320
254373 47165826 12/31/2016 18:29 0xA433 0x000033A4 13220 13.220 1322.000
254786 47369729 12/31/2016 2:10 0x0D 0x0000000D 13 0.013 0.013
292465 47177804 12/31/2016 12:26 0x333733 0x00333733 3356467 3356.467 33564.67
310122 47242709 12/31/2016 14:08 0x80380C 0x000C3880 800896 800.896 800.896

Attachments
Book1.xlsx (13 views, 13.00 KB)
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)

Group: General Forum Members
Points: 194915 Visits: 23765
IQ1 - Monday, February 26, 2018 10:38 AM
Hello,
I am using the following Table to get data from
I would like to use a function or code on the last column Cal value such that it matches the values shown in the column Final value
How can I do this using SQL queries please suggest ?

Thanks
IQ

The code for the table is as follows,
Table :
USE [StarDatabase]
GO

/****** Object: Table [dbo].[test] Script Date: 02/26/2018 12:26:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test](
[AccountNum] [nvarchar](20) NULL,
[MTUID] [int] NOT NULL,
[ReadingTime] [datetime] NOT NULL,
[data1] [varbinary](100) NULL,
[New_Value] [varbinary](20) NULL,
[val] [int] NULL,
[Cal value] [varchar](8000) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO



Values in the table are as follows: (Book1.xls file attached as well)
AccountNum MTUID ReadingTime data1 New_Value Val Cal Value Final_value
251250 47235827 12/31/2016 23:35 0x5C3020 0x0020305C 2109532 2109.532 21095.320
254373 47165826 12/31/2016 18:29 0xA433 0x000033A4 13220 13.220 1322.000
254786 47369729 12/31/2016 2:10 0x0D 0x0000000D 13 0.013 0.013
292465 47177804 12/31/2016 12:26 0x333733 0x00333733 3356467 3356.467 33564.67
310122 47242709 12/31/2016 14:08 0x80380C 0x000C3880 800896 800.896 800.896

So ... 2109.532 becomes 21095.320 (multiply by 10), yet 13.220 becomes 1322.00 (multiply by 100) and 0.013 stays unchanged.

What is the (bizarre) calculation logic?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
IQ1
IQ1
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 70
I am not sure but looks like depending on the number of digits in cal value the final value is getting calculated, can someone let me know how to do this ?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)

Group: General Forum Members
Points: 194915 Visits: 23765
IQ1 - Monday, February 26, 2018 12:15 PM
I am not sure but looks like depending on the number of digits in cal value the final value is getting calculated, can someone let me know how to do this ?

That's not the way things work here. You tell us the logic you require and we will help you implement it.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75356 Visits: 6361
IQ1 - Monday, February 26, 2018 12:15 PM
I am not sure but looks like depending on the number of digits in cal value the final value is getting calculated, can someone let me know how to do this ?

Until you can get your business users (or your BA person or IT department) to tell you EXACTLY how that is supposed to work, we're no better off than you are. What I see is totally inconsistent, which makes it utterly impossible to write code for,


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search