Select Statement with two extra column headers

Question

Select Statement with two extra column headers

SSC Enthusiast

Points: 172

August 18, 2022 at 11:03 pm

I have the following Select statement that exports data from a Horizon View SQL DB. It works fine. That statement is part of a script that automates reports. As part of the export, I need two additional column headers: 60Days and 90Days. These columns will be empty data (will be manipulated through powershell at a later state. I just need the column headers as part of the export. How can I do that?

$LastLoginQry = "SELECT `
Distinct SUBSTRING([ModuleAndEventText], charindex('\', [ModuleAndEventText])+1, charindex(' has logged in', [ModuleAndEventText]) - charindex('\', [ModuleAndEventText])-1) `
AS 'All Users', convert(varchar, MAX([Time]), 110) AS 'Last Login' `
FROM `
[Hzv73events].[dbo].[EVTevent_historical] `
WHERE `
[EventType]='BROKER_USERLOGGEDIN' `
GROUP BY SUBSTRING([ModuleAndEventText], charindex('\', [ModuleAndEventText])+1, charindex(' has logged in', [ModuleAndEventText]) `
- charindex('\', [ModuleAndEventText])-1)"

August 24, 2022 at 6:22 am

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

You just add the additional columns to the statement, the following adds the columns as empty strings:

$LastLoginQry = "SELECT `
Distinct SUBSTRING([ModuleAndEventText], charindex('\', [ModuleAndEventText])+1, charindex(' has logged in', [ModuleAndEventText]) - charindex('\', [ModuleAndEventText])-1) `
AS 'All Users', convert(varchar, MAX([Time]), 110) AS 'Last Login', '' As 60Days, '' As 90Days `
FROM `
[Hzv73events].[dbo].[EVTevent_historical] `
WHERE `
[EventType]='BROKER_USERLOGGEDIN' `
GROUP BY SUBSTRING([ModuleAndEventText], charindex('\', [ModuleAndEventText])+1, charindex(' has logged in', [ModuleAndEventText]) `
- charindex('\', [ModuleAndEventText])-1)"

The following adds the columns as Null values:

$LastLoginQry = "SELECT `
Distinct SUBSTRING([ModuleAndEventText], charindex('\', [ModuleAndEventText])+1, charindex(' has logged in', [ModuleAndEventText]) - charindex('\', [ModuleAndEventText])-1) `
AS 'All Users', convert(varchar, MAX([Time]), 110) AS 'Last Login', Null As 60Days, Null As 90Days `
FROM `
[Hzv73events].[dbo].[EVTevent_historical] `
WHERE `
[EventType]='BROKER_USERLOGGEDIN' `
GROUP BY SUBSTRING([ModuleAndEventText], charindex('\', [ModuleAndEventText])+1, charindex(' has logged in', [ModuleAndEventText]) `
- charindex('\', [ModuleAndEventText])-1)"

Further note: you never need both DISTINCT and GROUP BY in the same query. GROUP BY - by definition - will be distinct for the group defined. Also - you should always define the length of your data types and not rely on the 'default' size when converting. Instead of 'convert(varchar' you should have 'convert(varchar(nn))'.

Final Note: don't convert your date/time columns to a string. Once you do that you lose the ability to utilize any date functions in the calling language. Powershell creates an object from the query - and defines each column based on the metadata and would know that column is a datetime data type except that you have now converted it to a string so you no longer have the ability to manipulate that columns data as a date and time (and you have a space in the column name - which just makes it harder to code).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Lynn8968 Newbie Points: 8 More actions · Answer 3

Lynn8968

Newbie

Points: 8

August 25, 2022 at 6:08 am

#4080361

Thanks so much, your suggestion worked for me.