Friday, August 23, 2013

[T-SQL] smallint to hh:mm

[T-SQL] smallint to hh:mm

smallint to hh:mm

Posted: 23 Aug 2013 12:53 AM PDT

Hey,I have a smallint column that is used to store total 'units' worked on a specific thing. Let's take 177 units as an example, which is a sum of a say 15 rows. 1 unit is 6 minutes, so 10 units is 1 hour.I've casted the smallint column to real so it will divide by 10 to give me 17.70 hours. Well 70 minutes doesn't exist, so it needs to show as 18.10.Never done this before!Thanks

How to use"Union ALL" to join sql queries each containing order by clause

Posted: 03 Mar 2010 12:16 PM PST

I have 2 sql statements and each sql statement is having orderby clause when i am use" UNION ALL " to join them i am getting an error.

Distinct Select

Posted: 22 Aug 2013 09:15 PM PDT

hi guys,here i have a little need [code]DECLARE @project AS TABLE (Projectid INT, ProjectName VARCHAR(100))DECLARE @Manager AS TABLE (Projectid INT, Managerid VARCHAR(100))DECLARE @Developer AS TABLE (Projectid INT, Developerid VARCHAR(100))DECLARE @User AS TABLE (Userid INT ,UserName VARCHAR(100))INSERT INTO @project SELECT 1,'Test' INSERT INTO @User SELECT dp.principal_id, dp.name FROM sys.database_principals dp WHERE dp.[type] ='s'INSERT INTO @ManagerVALUES (1,1),(1,2)INSERT INTO @DeveloperVALUES (1,1),(1,3),(1,4)SELECT p.ProjectName as PName,man.UserName AS Manager,dev.UserName AS Developer FROM @project p INNER JOIN @Manager m ON m.Projectid = p.ProjectidINNER JOIN @Developer d ON d.Projectid = p.Projectid INNER JOIN @User dev ON d.Developerid = dev.UseridINNER JOIN @User man ON m.Managerid = man.Userid [/code]The result is[code]PName Manager DeveloperTest dbo dboTest dbo INFORMATION_SCHEMATest dbo sysTest guest dboTest guest INFORMATION_SCHEMATest guest sys[/code]but expected is [code]PName Manager DeveloperTest dbo dbo guest INFORMATION_SCHEMA sys[/code]with a minimal scan count and read count

Delete not working

Posted: 22 Aug 2013 08:57 PM PDT

HiI'm really stumped, on a client's machine I can see a problem which I cannot reproduce.Have a table that should be deleted and then repopulated the delete is not working.A 3rd party application imports data from a file into a table in 2005 DB. I cannot see the code.table: is dbo.tblDataImporterUser: DImporter (SQL Authentication) default schema is dbo. User has rights to delete/insert/select on tableUsing profiler I can see:[code="other"]1. CREATE table tblDataImporter(filename varchar(255) null, [/code]..... Batch started and completedNext it does somethng weird[code="other"]2. SELECT * FROM CREATE table tblDataImporter(filename varchar(255) null, .....[/code]Again Batch started and completed[code="other"]3. DELETE FROM tblDataImporter [/code] Batch started and completed[code="other"]4. INSERT INTO tblDataImporter (FILENAME, ......) values ('myFile.txt',...)[/code]The delete does not delete. The inserts do however.If I log in using Enterprise manager as user DImporter and run the Delete it works.I know both steps 1 and 2 FAIL silently (w.r.t the application). 1. becuase DImporter does not have create table rights and 2. because its just nonsensical query. I have checked there is no table called DImporter.tblDataImporter. There are no triggers on the table. If batch completed then it cannot be query timeout?Any ideas what I should investigate next time I'm connected to this machine. Trying to build a list of things to check when I next can access this machine. Thanks

NULL issue

Posted: 22 Aug 2013 09:24 PM PDT

[code="sql"]declare @t table (id int identity, name varchar(10))insert into @tselect 'Bhuv'unionselect nullunionselect 'Check'select * from @t where name <> 'Check'[/code]Why select not ginving NULL related reocrd , i am expecting two records here

Help with Max function

Posted: 22 Aug 2013 02:32 AM PDT

I want to get the 10.1 and 10.2 in my output. How do i get that?Table A has the columns change_idChange_id-----------1.11.22.12.23.13.210.110.2

Help with T-SQL Coding Issue, data on different rows

Posted: 22 Aug 2013 07:07 AM PDT

HelloI'm hoping someone can help me , I need to put together a script that will look at different rows in the data I having problems creating a case statement that will look if a course is a A2 course and then put the enrollment figures from the previous years AS course in it.The course data has a unique identify that identifies the course, the year it was run and if it is a AS or A2 course. and is stored like 09ASCOMP10A2COMP (for example)The data is being grouped by the course so the data is stored on different rows.I hoping someone can give me some ideas with SQL inbuilt functions to use or that might help.I'm using SQL 2012 and SSRS 2008.

Distinct in CTE?

Posted: 22 Aug 2013 05:25 AM PDT

Hi,I have this query that is close to what I need, but not close enough..[code="sql"]--Query Changed to include new codes;WITH cte AS (select * from (SELECT distinct pehPErcontrol ,DATENAME(MONTH, pehpaydate) 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],pehpaydate AS [Pay Date]FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID right outer 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 [Pay Date] >= '2013-01-01' AND [EARN CODE] IN ('0002', '0022','0025','0066','0104','A102','N002','N104', '002D', '0071','0096','0104','0121', '0123', '0151', '0200', '0201', '0202', '0205', 'A102', 'CREW', 'N002', 'N104', 'TXWP' )AND [IS Voided] <> 'Y'AND [Voiding Record] <> 'Y' AND [Is Overtime] = 'Y' AND [org level 2] like '%ZPR'AND [ULTIMONTH] = 'JANUARY'--ORDER BY pehpaydate--AND [NAME] = 'Moskowitz, Joshua' )SELECT * ,SUM([Current Amount]) OVER (PARTITION BY [Emp No],[UltiMonth]) AS [Monthly Amount] FROM cte[/code]This returns the [Monthly Amount] for every employee, listed for every record. When I am already getting the sum for the month. The sum for the month is correct, it just lists it for every record. So, when I total in my dashboard program it's incorrect. I think I need to add an additional column(s) so that I can add the totals correctlySo monthly amount looks like:JANUARY EMPLOYEE #1 $190.03JANUARY EMPLOYEE #1 $190.03JANUARY EMPLOYEE #2 $240.28JANUARY EMPLOYEE #2 $240.28JANUARY EMPLOYEE #3 $164.69JANUARY EMPLOYEE #3 $164.69Where I really need it to look like:JANUARY EMPLOYEE #1 $190.03JANUARY EMPLOYEE #2 $240.28JANUARY EMPLOYEE #3 $164.69Been working on this for two days now and not making much progress...

patindex alternative in SSIS

Posted: 22 Aug 2013 04:23 AM PDT

i need to implement the below code in ssis.declare @value nvarchar(200);set @value='EN 60325';WHILE @Value LIKE '%[^0-9]%'SET @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '')select @valueoutput-> 60325Mainly i need alternative to patindex.Thanks in advance!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog