Need Hep With SQL Login

• shravanihnk

  Newbie

  Points: 7

  More actions

  January 8, 2017 at 9:32 am

  #317345

  Please see the attachment

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 8, 2017 at 9:58 am

  #1921266

  shravanihnk (1/8/2017)

  ---

  Please see the attachment

  Please read and heed the first link under "Helpful Links" in my signature line below. It'll help you get better answers more quickly especially since a lot of people won't open a Word/Etc attachment.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  January 8, 2017 at 11:16 am

  #1921270

  As jeff said make sure you have post DDL in consumable format next time. below is the code let me know.

  IF OBJECT_ID('tempdb..#temp') IS NOT NULL

  DROP TABLE #temp

  GO

  CREATE TABLE #temp (

  DoctorName VARCHAR(150)

  ,Modifieddate DATETIME

  ,ColumnChanged VARCHAR(550)

  ,FieldbeforeValue VARCHAR(550)

  ,FieldAfterValue VARCHAR(550)

  )

  INSERT INTO #temp

  SELECT 'John'

  ,'01/03/2017'

  ,'Phno'

  ,'7477474747'

  ,'8477474748'

  UNION

  SELECT 'john'

  ,'01/03/2017'

  ,'mobileno'

  ,'334348484'

  ,'444433333'

  UNION

  SELECT 'john'

  ,'01/05/2017'

  ,'Address'

  ,'123MainStreet'

  ,' 404LinconnHwy'

  UNION

  SELECT 'john'

  ,'01/05/2017'

  ,'mobileno'

  ,'444433333'

  ,'666666666'

  UNION

  SELECT 'John'

  ,'01/07/2017'

  ,'Address'

  ,'404LinconnHwy'

  ,'999GregorySquare'

  --SELECT *

  --FROM #temp

  SELECT TAB.DoctorName

  ,TAB.Modifieddate

  ,MAX(TAB.OLD_Phno) AS OLD_Phno

  ,MAX(TAB.NEW_Phno) AS NEW_Phno

  ,MAX(TAB.OLD_mobileno) AS OLD_mobileno

  ,MAX(TAB.NEW_mobileno) AS NEW_mobileno

  ,MAX(TAB.OLD_Address) AS OLD_Address

  ,MAX(TAB.NEW_Address) AS NEW_Address

  FROM (

  SELECT AT.DoctorName

  ,AT.Modifieddate

  ,AT.ColumnChanged

  ,CASE AT.ColumnChanged

  WHEN 'Phno'

  THEN AT.FieldbeforeValue

  END AS OLD_Phno

  ,CASE AT.ColumnChanged

  WHEN 'Phno'

  THEN AT.FieldAfterValue

  END AS NEW_Phno

  ,CASE AT.ColumnChanged

  WHEN 'mobileno'

  THEN AT.FieldbeforeValue

  END AS OLD_mobileno

  ,CASE AT.ColumnChanged

  WHEN 'mobileno'

  THEN AT.FieldAfterValue

  END AS NEW_mobileno

  ,CASE AT.ColumnChanged

  WHEN 'Address'

  THEN AT.FieldbeforeValue

  END AS OLD_Address

  ,CASE AT.ColumnChanged

  WHEN 'Address'

  THEN AT.FieldAfterValue

  END AS NEW_Address

  FROM #TEMP AT

  ) TAB

  GROUP BY TAB.DoctorName

  ,TAB.Modifieddate

  GO

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 8, 2017 at 11:37 am

  #1921271

  I wasn't brave enough to open that file on a Windows PCm, however, on my Linux PC was a different story (Gotta love the lack of malware out there, and that any Macros in the file wouldn't of worked 🙂 ).

  Contents of the file is as follows:

  shravanihnk's docx attachment

  ---

  DoctorName Address Phno mobileno ModifiedDate

  John 123MainStreet 7477474747 334348484 01/01/2017

  John 123MainStreet 8477474748 444433333 01/03/2017

  John 404LinconnHwy 8477474748 666666666 01/05/2017

  John 999GregorySquare 8477474748 666666666 01/07/2017

  I have a Doctor History table, each time doctor update his information it inserts in record in History table

  My requirement is: I need to create a report “what are the columns changed and Value before and after for that field “

  Please see the Final Output report below:

  DoctorName Modified date Column Changed Field before Value Field After Value

  John 01/03/2017 Phno 7477474747 8477474748

  john 01/03/2017 mobileno 334348484 444433333

  john 01/05/2017 Address 123MainStreet 404LinconnHwy

  john 01/05/2017 mobileno 444433333 666666666

  John 01/07/2017 Address 404LinconnHwy 999GregorySquare

  I, also, reiterate what Jeff said. Put your question and all the relevant information in your post. Don't tell us to see the attached docuement, you're only going to invite people to think that you're trying to provide malicious files.

  Some files will be fine, but only if they're relevant to the question; for example sqlplan files when asking about slow performing queries, rdl files when you can't work out why your Report won't work, etc.

  Edit: What does this have to do with logins? Are we missing something?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk