Need Hep With SQL Login

Question

Post reply

Need Hep With SQL Login

shravanihnk

Newbie

Points: 7
More actions
January 8, 2017 at 9:32 am

#317345

Please see the attachment

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

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)
Intro to Tally Tables and Functions

mxy SSCarpal Tunnel Points: 4024 More actions · Answer 2

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: 99009 More actions · Answer 3

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