Replicate process from excel to sql

• Radders

  Old Hand

  Points: 384

  More actions

  July 2, 2017 at 7:37 pm

  #331662

  Hi All,
  I have a script that outputs data to a txt file which is then manipulated in excel to get yet another output. eg.
  The first table is the txt file output. Based on the 'Status' column I want to change the Name, as in the second table
  Where Status = 'Out' .... change Name to 'Out'

  

  The following vb code loops through all the rows and makes the changes:

  

  So my quandary is, I want to eliminate the above code and perform the change in a tsql query, to get rid of the excel report and use SSRS. I don't want to make a permanent change to the data as it is just for reporting purposes.

  Any help would be appreciated

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 2, 2017 at 8:09 pm

  #1949091

  what you want is a case statement to pick one column or the other base don the value.
  here's a complete working example based on what you posted. see how the Case statement selects one column or the other...it could pick a static value, or a column in your example.
  IF OBJECT_ID('tempdb.[dbo].[#MySampleTable]') IS NOT NULL
DROP TABLE [dbo].[#MySampleTable]
CREATE TABLE #MySampleTable (
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] VARCHAR(30),
[Status] VARCHAR(5) )
INSERT INTO #MySampleTable([Name],[Status])
SELECT 'John','In' UNION ALL SELECT 'Pete','Out' UNION ALL
SELECT 'Sue','In' UNION ALL SELECT 'Mary','Out' UNION ALL
SELECT 'George','In'

  SELECT
  ID,
  CASE
    WHEN [Status] = 'Out'
    THEN [Status]
    ELSE [Name]
  END AS [Name],
[Status]
FROM #MySampleTable

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Radders

  Old Hand

  Points: 384

  More actions

  July 10, 2017 at 4:47 am

  #1950136

  Thanks Lowell
  that works a treat however I need to replicate that across all the columns, to hide all the details in the record. Do I just add the same code to each field in the query. For instance if I had several columns, I take it this would be the same:

  SELECT
    ID,
    CASE
      WHEN [Status] = 'Out'
      THEN [Status]
      ELSE [Name]
    END AS [Name],
      CASE
      WHEN [Status] = 'Out'
      THEN [Status]
      ELSE [Tel]
    END AS [Tel],
      CASE
      WHEN [Status] = 'Out'
      THEN [Status]
      ELSE [Workstation]
    END AS [Workstation],
      CASE
      WHEN [Status] = 'Out'
      THEN [Status]
      ELSE [Building]
    END AS [Building],
  [Status]
  FROM #MySampleTable

  

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 10, 2017 at 5:14 am

  #1950137

  Yes, you'll end up needing  bunch of case statements, one for each column.
  This could also be done int he presentation layer, so for example SSRS could use an expression with the same logic, but the syntax looks different.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Radders

  Old Hand

  Points: 384

  More actions

  July 10, 2017 at 7:43 am

  #1950168

  On the same issue I am adding more columns but, I have a field which is a DateTime and will not let me substitute the field with 'Out'.

  CASE
      WHEN [Status] = 'Out'
   then [Status] 
      ELSE [MyTable.DateField]
    END AS [MyTable.DateField]

  I am getting errors converting the field!

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 10, 2017 at 7:47 am

  #1950170

  CONVERT the date field to varchar() for display purposes as part of the query.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Radders

  Old Hand

  Points: 384

  More actions

  July 10, 2017 at 7:57 am

  #1950175

  Hi sgmunson,
  the Case statement will not accept the code, I get
  
  unless I'm doing it wrong
  CASE
  WHEN [Status] = 'Out'
  then convert(varchar(10),MyTable.DateField,101) [Status]
      ELSE [MyTable.DateField]
  END AS [MyTable.DateField]

• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 10, 2017 at 8:10 am

  #1950179

  sgmunson - Monday, July 10, 2017 7:47 AM

  CONVERT the date field to varchar() for display purposes as part of the query.

  At this point, I'm not sure that's the right idea. This could cause problems on your front end as the data type is no longer a date type.  Take for example:
  CREATE TABLE #Dates (DateValue date, Status varchar(3));
GO
INSERT INTO #Dates
VALUES ('20170201','In'),('20170205','In'),('20170110','Out'),('20170315','In'),('20170122','In');
GO
SELECT DateValue, Status
FROM #Dates
ORDER BY DateValue;
GO
SELECT CASE Status WHEN 'Out' THEN 'Out' ELSE CONVERT(varchar(10), DateValue, 103) END AS NewDateValue
FROM #Dates
ORDER BY NewDateValue;
GO
DROP TABLE #Dates
GO
  In the Second statement the order of the dates is:
  01/02/2017
05/02/2017
15/03/2017
22/01/2017
Out --this was the Date 10/01/2017
  Yes, you could order by DateValue in the ORDER BY, but if you're not ordering in the SP and instead in your front end, then you're going to be ordering on NewDateValue, not the one in the query. Personally I would go what Lowell suggests, and use your presentation layer to change the value based on the status field.

  Thom~

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

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  July 10, 2017 at 11:30 am

  #1950216

  Radders - Monday, July 10, 2017 7:57 AM

  Hi sgmunson,
  the Case statement will not accept the code, I get
  
  unless I'm doing it wrong
  CASE
  WHEN [Status] = 'Out'
  then convert(varchar(10),MyTable.DateField,101) [Status]
      ELSE [MyTable.DateField]
  END AS [MyTable.DateField]

  remove the word "[Status]" (the bold one) and that should fix the syntax error.

  But as Thom points out, this may break whatever application is trying to read the data.  If it is expecting a datetime and it gets a varchar(10), it may have undesired effects.
  Depending on the presentation layer (C#, SSRS, etc), you may have better results changing it at that end as both Thom and Lowell point out.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 10, 2017 at 12:10 pm

  #1950226

  Radders - Monday, July 10, 2017 7:57 AM

  Hi sgmunson,
  the Case statement will not accept the code, I get
  
  unless I'm doing it wrong
  CASE
  WHEN [Status] = 'Out'
  then convert(varchar(10),MyTable.DateField,101) [Status]
      ELSE [MyTable.DateField]
  END AS [MyTable.DateField]

  Try it this way:
  CASE
    WHEN [Status] = 'Out' THEN [Status]
  ELSE CONVERT(varchar(10), MyTable.DateField, 101)
END AS DateField

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Radders

  Old Hand

  Points: 384

  More actions

  July 11, 2017 at 3:25 am

  #1950315

  Hi all,
  Thanks for the responses.
  Tom, SSCertifiable .. The query is being used in a SS Report so no issue about the DateTime breaking the app.
  Steve(sgmunson) that worked a treat, thank you.

  I can now get rid of that damn excel spreadsheet 🙂🙂🙂🙂