Require complex data format

Question

Require complex data format

avdhut.k

SSCrazy

Points: 2834
More actions
August 9, 2017 at 3:56 am

#344063

Hi,
I need the report.Format and base data is attached in excel file.
Kindly help me with desired output.

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

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

You should already know this, but can you please post proper sample data and DDL (see the link in my signature), and the expected output. The floats are really malformed when copying and pasting to and from Excel. Plus, most, won't open an Excel Document from a stranger. Also, some logic to how you get from your original data to the original would be great.

Provided data we can use makes it much more likely someone is going to give you an answer to your question.

Thom~

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

avdhut.k SSCrazy Points: 2834 More actions · Answer 2

CREATE TABLE [dbo].[test](
    [LeadId] [nvarchar](255) NULL,
    [PgNm] [nvarchar](255) NULL,
    [Start_Date] [datetime] NULL,
    [End_Date] [datetime] NULL,
    [UsrNm] [nvarchar](255) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[test]
    ([LeadId]
    ,[PgNm]
    ,[Start_Date]
    ,[End_Date]
    ,[UsrNm])
VALUES
    (1 ,'Start','7/1/2017 1:17:31 PM','7/2/2017 1:17:31 PM', 'John'),
          (1 ,'Scan','7/3/2017 1:17:31 PM','7/5/2017 1:17:31 PM', 'bell'),
             (1 ,'Quick Data Entry','7/6/2017 1:17:31 PM','7/10/2017 1:17:31 PM', 'delli'),
             (1 ,'Quick Data Entry','7/11/2017 1:17:31 PM','7/12/2017 1:17:31 PM', 'money'),
             (1 ,'RCU Approval','7/13/2017 1:17:31 PM','7/15/2017 1:17:31 PM', 'jimmy'),
              (1 ,'end','7/15/2017 1:17:31 PM','7/16/2017 1:17:31 PM', 'ferry')

alter table [test]
alter column gap varchar(50)

update [test]
set gap =
convert(varchar(10), (DateDiff(s, [Start_Date], End_Date)/86400)) + ':' +
convert(varchar(10), ((DateDiff(s, [Start_Date], End_Date)%86400)/3600)) + ':'+
convert(varchar(10), (((DateDiff(s, [Start_Date], End_Date)%86400)%3600)/60)) + ':'+
convert(varchar(10), (((DateDiff(s, [Start_Date], End_Date)%86400)%3600)%60))

/***** Desired Output ****/

LeadId	Start_UsrNm	Start	Scan_UsrNm	Scan	Quick Data En try_UsrNm	Quick Data Entry	RCU Approval_UsrNm	RCU Approval	end_UsrNm	end
1	John	1:0:0:0	bell	2:0:0:0	delli	5:0:0:0	jimmy	2:0:0:0	ferry	1:0:0:0

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

This is easy using cross tabs. Here's a snippet on how to do it, you'll need to complete it.
SELECT LeadId, MAX(CASE WHEN PgNm = 'Start' THEN [UsrNm] END) AS Start_UsrNm, MAX(CASE WHEN PgNm = 'Start' THEN gap END) AS Startd, MAX(CASE WHEN PgNm = 'Scan' THEN [UsrNm] END) AS Scan_UsrNm, MAX(CASE WHEN PgNm = 'Scan' THEN gap END) AS Scan FROM test GROUP BY LeadId;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2