Thursday, August 22, 2013

[T-SQL] Search Value

[T-SQL] Search Value

Search Value

Posted: 21 Aug 2013 08:24 PM PDT

Hi,I want to search a word in all stored procedure and create the list of stored procedures where that word exists. I am done with that but one more scenario added to that is that only search those stored procedure where that word is present in an uncommented lines only (exclude those stored procedures where that word present in commented lines).Is this possible?

Using SQL Last in an Aggregate Query

Posted: 21 Aug 2013 05:28 AM PDT

I have a query that uses the SUM aggregate function to return results for over 200 columns. Based on the @DateFrequency selected it groups the results by ('Daily', 'Weekly', 'Monthly') for a selected start and end date timeframe. The issue is now for several of the columns they want to just have the last record result for that timeframe. So if weekly or monthly is selected it will only display the last record available for that column by the most recent date. I know you can use the (select top 1... order by date desc) to get this result traditionally but the issue I am running into is the query uses all aggregates. So any calculation I make since it is not an aggregate it wants me to add that to the group by which then skews my results that right now group correctly based on the @DateFrequency selected. I tried creating a UDF that uses the TOP 1 logic but it still wants me to include the column name in the group by unless there is something I am doing incorrectly. Is it possible with the given conditions I have described to get a last record result or do I need to rework the query? I have attached a condensed version of my query. Any feedback would be appreciated.

Argument data type datetime is invalid for argument 1 of substring function

Posted: 21 Aug 2013 12:26 PM PDT

Hi,I have this query that works fine.[code="sql"];WITH cte AS (select * from (SELECT distinct pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'February' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as [UltiMonth],rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code],pehIsVoided AS [IS Voided],pehIsVoidingRecord AS [Voiding Record],pehIsOvertime AS [Is Overtime]FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2) t right outer join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]where pehPerControl > '201301011' AND [EARN CODE] = '0002'AND [IS Voided] <> 'Y'AND [Voiding Record] <> 'Y' AND [Is Overtime] = 'Y' AND [org level 2] like '%ZSW'--AND [ULTIMONTH] = 'FEBRUARY'--ORDER BY pehPerControl)SELECT * ,SUM([Current Amount]) OVER (PARTITION BY [Emp No],[UltiMonth]) AS [Monthly Amount]FROM cte[/code]Turns out I used the wrong field (pehPerControl) which was formatted like - 201301111I need to now use 'pehPaydate' (because there can be more than one) which is formatted like - 2013-01-13 00:00:00.000So, If I change the all columns in the above query to use 'pehPaydate' now I get an error:Argument data type datetime is invalid for argument 1 of substring function.Pretty sure I need to use a cast or convert in my substring - but can't get the syntax right.

Fetch numeric values from string using SSIS

Posted: 21 Aug 2013 09:25 PM PDT

I need to implement below scenario in SSIS 2008I am getting values like below.EN 60320.V.1EN 61000-4-1EN 294EN ISO 3506-1EN 61400-25-6Need output as6032061000294350661400

Result that matches all the values from a list

Posted: 21 Aug 2013 05:45 PM PDT

I have two tables which are listed below.[code="sql"]TableAStudentID StudentName1 A2 B3 C4 D5 E[/code][code="sql"]TableBStudentID ClassID SectionID1 2 53 2 7[/code]Now I am trying to retrive those students for which all items in a input list matches.For example, If I pass the input list(ClassID & SectionID) as (2, 5), it should return StudentID : 1 If I pass the input list as (2, 5 | 1, 1) it should not return the StudentID : 1[code="sql"]DECLARE @tblData AS TABLE( [ClassID] INT ,[SectionID] INT)INSERT INTO @tblData VALUES (2, 5)INSERT INTO @tblData VALUES (2, 1)SELECT A.[StudentID] ,A.[StudentName] ,B.[ClassID] ,B.[SectionID]FROM [AAAAAA] AS A INNER JOIN [BBBBBB] AS B ON A.[StudentID] = B.[StudentID] INNER JOIN @tblData AS C On B.[ClassID] = C.[ClassID] AND B.[SectionID] = C.[SectionID][/code]But above mentioned query does not returning the expected value.Can you please help me?Regards,P. Paul

BCP Help

Posted: 08 Aug 2013 09:17 PM PDT

Hi all I am tasked with importing large fixed width text file to SQL, previously I used a library called filehelpers to assist through my code. Now the files are growing I keep getting out of memory errors so decided to try BCP.My command bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmtand the result SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: tt open a connection to SQL Server [2].SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Server Native Client 10.0]A network-related or specific error has occurred while establishing a connection to SQL Server is not found or not accessible. Check if instance name is correct and Server is configured to allow remote connections. For more information server Books Online.SQLState = S1T00, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expiredThis is a localhost server with windows authPlease help me learn why, I have tried with the -t switch and same result

Omit Data Length for Numeric Values

Posted: 21 Aug 2013 06:01 AM PDT

Hello EveryoneI am merely playing around with an idea today. It is slow today, so I needed to teach myself something new. But as with some things, I have ran into a snag. I could do this manually, but that never any fun.I am executing a select query that returns the column names and the data type for a single table. I am using a system table in the MSDB for sample purpose, since we all have an MSDB database to use.[code="sql"]SELECT c.name AS ColumnName, t.name+'('+CAST(c.max_length AS varchar(3))+')' AS ColumnDataTypeFROM sys.all_columns cJOIN sys.types t ON c.user_type_id = t.user_type_idWHERE object_id = ( SELECT object_id FROM sys.tables WHERE name = 'backupset')ORDER BY c.column_id ASC[/code]That will return this resultset:[quote]ColumnName ColumnDataTypebackup_set_id int(4)backup_set_uuid uniqueidentifier(16)media_set_id int(4)first_family_number tinyint(1)first_media_number smallint(2)last_family_number tinyint(1)last_media_number smallint(2)catalog_family_number tinyint(1)catalog_media_number smallint(2)position int(4)expiration_date datetime(8)software_vendor_id int(4)name nvarchar(256)description nvarchar(510)user_name nvarchar(256)software_major_version tinyint(1)software_minor_version tinyint(1)software_build_version smallint(2)time_zone smallint(2)mtf_minor_version tinyint(1)first_lsn numeric(13)last_lsn numeric(13)checkpoint_lsn numeric(13)database_backup_lsn numeric(13)database_creation_date datetime(8)backup_start_date datetime(8)backup_finish_date datetime(8)[/quote]I have shortened the resultset by a few rows. I would like to be able to not show the max_length of the data type for all non-character data types.So for example:[quote][u]ColumnName[/u] [u]ColumnDataType[/u]backup_set_id intbackup_set_uuid uniqueidentifiermedia_set_id intdatabase_name nvarchar(256)server_name nvarchar(256)machine_name nvarchar(256)last_media_number smallintcatalog_family_number tinyintcatalog_media_number smallintposition intexpiration_date datetimesoftware_vendor_id intname nvarchar(256)description nvarchar(510)user_name nvarchar(256)[/quote]I have tried a CASE statement in the JOIN clause, that only limits the rows returned.Does anyone have an idea as to how to correctly make this work?Thank you in advance for all your assistance, suggestions and commentsAndrew SQLDBA

Had to change query to use date field

Posted: 21 Aug 2013 06:52 AM PDT

Hi,I needed to change part of my query to use a date field now I can't get it to work:This is the original (works)[code="sql"]select left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) as name from pearhist[/code]This one no work:[code="sql"]select left(substring(cast (pehpaydate as varchar)(pehpaydate,5,len(pehpaydate)),2) as name from pearhist[/code]

Query to get immediate next date row into end_ts column in Tsql/db2 sql

Posted: 21 Aug 2013 05:58 AM PDT

I copied the query result set to TEST.xls and required result set into result.xls.I have a table now where we have addressline1, city,state, zip, county ,file_no and envelope_date. But for some of the records we have more than one address for particular file_no for different timestamp, you can see in the test.xls for example.what I have to do is, we have a huge table where they have all the information, so I m pulling all the above mentioned fields from this huge table and loading into a process table, then planning to load them in to new dimension table and then get the dimension key and load into FACT Table, but I got into this issue where I m seeing more than one address for one file_no, so what I m planning to do is add a end_ts column to my process table, and then select address between start and enddates. Start date is same as envelope_date and end_ts is nothing but next row in the envelope_date column.Now I m not quite sure how can I pull the next row from same column and update end_ts.Sorry if this explanation is confusingThanks in advance

Executing a stored proc on a linked server

Posted: 21 Aug 2013 03:46 AM PDT

We have a situation were we want to get the results of a stored procedure from a DB2 database in SQL. I would think we would need a linked server set up pointing to DB2. Does anyone have any link or source materials I could use to determine how to do this?Thanks!

Grouping data...

Posted: 21 Aug 2013 01:37 AM PDT

Hello, I´m new in this forum. Maybe someone can help me with my problem:My data looks like this:STRBEZ STRNR10. Oktober-Straße 13110. Oktober-Straße 13218. November-Platz 14128. Mai-Platz 1318. Mai-Platz 132Abstimmungsstraße 1411Abstimmungsstraße 1412Ackerweg 711As a result I want to get this:10. Oktober-Straße 131,13218. November-Platz 14128. Mai-Platz 131,132an so on...Is there a "simple" SQL-Statement for this problem? I have resolved such a problemby using a function - but maybe there exists an easier way...Thanks vor helping
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog