Query to retrieve values in a particular format

Question

Query to retrieve values in a particular format

Right there with Babe

Points: 765

February 26, 2018 at 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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

IQ1 Right there with Babe Points: 765 More actions · Answer 2

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 SSC Guru Points: 246993 More actions · Answer 3

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sgmunson SSC Guru Points: 110640 More actions · Answer 4

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)