April 15, 2016 at 6:01 am
How can we get the changing value with column name in a table ex:
[Engineer no PostalCodeFTEFieldWorkEfficiency AllowCMDistrictNameAllowPM
100234 BE 0 1 1 District Wallony0
100234 BE 0 1 1 District Wallony1
100234 BE1082 0.5 0.5 1 District Wallony1
100234 BE1082 0.5 1 1 District Wallony0
100234 BE1082 0.5 1 1 District Wallony1
Ineed data like:
Engineer noChange in column Value changing with
100234 AllowPM 0
100234 AllowPM 1
100234 FTEFieldWork 0
100234 FTEFieldWork 0.5
100234 PostalCode BE
100234 PostalCode BE1082
I need it very urgently in sql any one please help me.
HI all,
I think I should be more clear on the question
here is the situation
EngineerPostalCodeFTEFieldWorkEfficiencyAllowCMDistrictNameAllowPMSCENARIOid
100234BE0.01.01District Wallony 0123
100234BE10820.50.51District Wallony 1124
100235BE10820.01.01District Wallony 0123
100235BE10820.51.01District Wallony 1124
SO here you can see we have scenario(123,124) and I need a comparison between two scenario if value of any field is changing in below format:
Engineer column?Name oldScenrio newScenrio
100234 PostalCode BE BE1082
100234 FTEFieldWork 0.0 0.5
100234 Efficiency 1.0 0.5
100234 AllowPM 0 1
100235 FTEFieldWork 0.0 0.5
100235 AllowPM 0 1
FYI-I have a composite key in this table with engineer and Scenario.
Please let me know in case you need more clarification. and I need it urgent basis so request you guy's to Please help me out.
Thanks in Advance.
And feel sorry If I hurt anyone with confusing question.
April 15, 2016 at 7:42 am
You have several issues here.
1) This requires an order and your data has no obvious way to specify that order.
2) You are combining columns with different data types into a single column, so you'll need to convert all of those values to a single common data type.
You're essentially creating an EAV model of your data, and T-SQL doesn't handle an EAV model very well. What's the business need for doing this?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 15, 2016 at 8:01 am
As Drew mentioned this is generally not a good idea. If you tell us what problem you are really trying to solve we might be able to suggest an alternate solution.
April 15, 2016 at 11:15 am
Hi Drew,
In my table approx 53 column is there I need to find out the trend value is changing where, So the main problame is I need the name on column in which value is changing and the value which is changing
Like if Engineer no is 10026 then I need the name of column in which value is changing like
column name value_changing
Allow Pm 0.5
AllowPM 1
April 18, 2016 at 7:37 am
saroj9958 (4/15/2016)
Hi Drew,In my table approx 53 column is there I need to find out the trend value is changing where, So the main problame is I need the name on column in which value is changing and the value which is changing
Like if Engineer no is 10026 then I need the name of column in which value is changing like
column name value_changing
Allow Pm 0.5
AllowPM 1
Does the table not include a datetime column?
April 19, 2016 at 12:47 am
No there is not any date-time column. only numerice value or string value is changing.:-)
April 19, 2016 at 7:36 am
saroj9958 (4/19/2016)
No there is not any date-time column. only numerice value or string value is changing.:-)
Well that appears to be a fundamental flaw with your design. How would you ever know what the most recent value is supposed to be? When did THIS value change from THIS to THAT? It seems like your table is a simple log table. However, without an order of events (datetime data) this model does not make sense.
April 20, 2016 at 1:22 am
Is there any other way or tool by which I can do this?
April 20, 2016 at 6:19 am
are you looking to write changes made in a table to an "audit" table.....
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 20, 2016 at 8:54 am
Yes..I am looking for the same could you please send me an example how to do that?
April 20, 2016 at 9:06 am
Use a trigger. Build it for alters, and when a column value changes, write to a new table, with the date/time.
Note that there's no order to the rows in a table. The order in which they are inserted is no guarantee they'll be retrievable in that order. You need an ORDER BY, which means you'll need some sort of column with datetime if you want changes in chronological order.
April 20, 2016 at 2:20 pm
saroj9958 (4/20/2016)
Yes..I am looking for the same could you please send me an example how to do that?
here is an old thread
http://www.sqlservercentral.com/Forums/Topic1544629-146-1.aspx
please be sure to read it all and take on board all the comments 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 21, 2016 at 1:52 pm
Here is an example of the kind of stuff you can do if you with minimal changes to your existing table. I am by no means saying this is the best design or method. I'm just trying to demonstrate that if you were at least logging the date when new records were added it opens up more options.
IF OBJECT_ID('tempdb.dbo.#LogTable', 'U') IS NOT NULL
DROP TABLE #LogTable
CREATE TABLE #LogTable (Engineer INT, PostalCode VARCHAR(6), FTEFieldWork NUMERIC(2,1), Efficiency NUMERIC(2,1), AllowCM BIT, DistrictName VARCHAR(100), AllowPM BIT)
--ADD values to table without a datetime field to simulate your current table BEFORE
INSERT INTO #LogTable (Engineer, PostalCode, FTEFieldWork, Efficiency, AllowCM, DistrictName, AllowPM)
VALUES
(100234, 'BE', 0, 1, 1, 'District Wallony', 0),
(100234, 'BE', 0, 1, 1, 'District Wallony', 1)
--(100234, 'BE1082', 0.5, 0.5, 1, 'District Wallony', 1),
--(100234, 'BE1082', 0.5, 1, 1, 'District Wallony', 0),
--(100234, 'BE1082', 0.5, 1, 1, 'District Wallony', 1)
--Show what the table looks like at first
SELECT * FROM #LogTable
--Now add a column that automatically adds a date and time when a new row is entered
ALTER TABLE #LogTable ADD LogDate DATETIME CONSTRAINT DF_Date DEFAULT GETDATE()
--Insert the Data at slightly different times
INSERT INTO #LogTable (Engineer, PostalCode, FTEFieldWork, Efficiency, AllowCM, DistrictName, AllowPM)
VALUES (100234, 'BE1082', 0.5, 0.5, 1, 'District Wallony', 1)
WAITFOR DELAY '00:00:02'
INSERT INTO #LogTable (Engineer, PostalCode, FTEFieldWork, Efficiency, AllowCM, DistrictName, AllowPM)
VALUES (100234, 'BE1082', 0.5, 1, 1, 'District Wallony', 0)
WAITFOR DELAY '00:00:02'
INSERT INTO #LogTable (Engineer, PostalCode, FTEFieldWork, Efficiency, AllowCM, DistrictName, AllowPM)
VALUES (100234, 'BE1082', 0.5, 1, 1, 'District Wallony', 1)
--Show what the table could look like AFTER
SELECT * FROM #LogTable
--Now you can do something like this to show you what the latest values are.
SELECT
l.*
FROM
#LogTable l
JOIN (SELECT Engineer, MAX(LogDate) AS MaxLogDate FROM #LogTable GROUP BY Engineer) x ON x.Engineer = l.Engineer AND x.MaxLogDate = l.LogDate
--**************************************************************************************************************
--Now we get a little funky with some dynamic SQL
IF OBJECT_ID('tempdb.dbo.#ChangeTable', 'U') IS NOT NULL
DROP TABLE #ChangeTable
--This table will hold any changes made for all columns of your choice
CREATE TABLE #ChangeTable (Engineer VARCHAR(100), [Column] VARCHAR(256), OldValue VARCHAR(100), NewValue VARCHAR(100), DateChanged DATETIME)
DECLARE @stmt VARCHAR(MAX)
--These are the columns we want to check for differences. You don't need the Engineer or LogDate columns.
;WITH MyColumns (c) AS (
SELECT * FROM (VALUES('PostalCode'), ('FTEFieldWork'), ('Efficiency'), ('AllowCM'), ('DistrictName'), ('AllowPM')) AS m(x)
)
--Generate a insert statement for each column name above.
SELECT @stmt = (
(SELECT
'INSERT INTO #ChangeTable (Engineer, [Column], OldValue, NewValue, DateChanged)
SELECT x.Engineer, x.FieldName, x.OldValue, x.NewValue, x.DateChanged FROM (
SELECT
Engineer,
' + '''' + c + '''' + ' AS FieldName,
CAST(LAG(l.' + c + ') OVER (PARTITION BY l.Engineer ORDER BY LogDate) AS VARCHAR(100)) AS OldValue,
CAST(l.' + c + ' AS VARCHAR(100)) AS NewValue,
l.LogDate AS DateChanged,
CASE WHEN l.' + c + ' <> LAG(l.' + c + ') OVER (PARTITION BY l.Engineer ORDER BY LogDate) THEN 1 ELSE 0 END AS HasChanged
FROM
#LogTable l
) x
WHERE x.HasChanged = 1 ' + CHAR(13) + CHAR(13)
FROM MyColumns FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)')
)
--PRINT @stmt
EXEC (@stmt)
SELECT * FROM #ChangeTable ORDER BY DateChanged
Cheers,
April 22, 2016 at 2:21 pm
HI all,
I think I should be more clear on the question
here is the situation
EngineerPostalCodeFTEFieldWorkEfficiencyAllowCMDistrictNameAllowPMSCENARIOid
100234BE0.01.01 District Wallony 0123
100234BE10820.50.51 District Wallony 1124
100235BE10820.01.01 District Wallony 0123
100235BE10820.51.01 District Wallony 1124
SO here you can see we have scenario(123,124) and I need a comparison between two scenario if value of any field is changing in below format:
Engineer column?Name oldScenrio newScenrio
100234 PostalCode BE BE1082
100234 FTEFieldWork 0.0 0.5
100234 Efficiency 1.0 0.5
100234 AllowPM 0 1
100235 FTEFieldWork 0.0 0.5
100235 AllowPM 0 1
FYI-I have a composite key in this table with engineer and Scenario.
Please let me know in case you need more clarification. and I need it urgent basis so request you guy's to Please help me out.
Thanks in Advance.
And feel sorry If I hurt anyone with confusing question.
April 22, 2016 at 2:26 pm
suggest you read this article....will help us help you get to a solution
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply