Tuesday, October 1, 2013

[T-SQL] get a specific time window across the night between two days

[T-SQL] get a specific time window across the night between two days

get a specific time window across the night between two days

Posted: 30 Sep 2013 10:05 AM PDT

Hi allI need some help me to get me in the right direction.I have collected some sql stats into a tables across a week.I want to see those stats at the time a nightly job run. so from day1 at 20pm to day2 at 11am.day2 at 20pm to day3 at 11amday3 at 20pm to day 4 at 11amand so on...so of the entire week i am only intrested to see that time window. What SQL query allow me to do that? I can use between day1 for time more than 20pm and day7 until 11am? this way i get all other timewindows i don't want.many thanks

Displaying nvarchar datatype column having a date value

Posted: 30 Sep 2013 02:58 PM PDT

Please help me in displaying a nvarchar datatype column called 'txtValue' having date value in the format '15 Jul 2013' in my SSRS report

SQL Agent Question

Posted: 30 Sep 2013 01:33 PM PDT

Hi Guys,I have Virtual Machine that I installed SQL Dev Edition. Here I need SQL Agent So I can Schedule my SSIS Packages.Please correct me if I am wrong. FYI I am talking VM (Virtual Machine) not a Server. If my VM on sleep mode or I log off, my scheduleis not gonna run or If I loged off or VM is in Sleep Mood Agent is running find as per Schedule time, is it true?If not, then how i can use this VM for what mention above? any thought or advise would be really appreciated. Thanks.

trying to write a case statement for datetime stamp - some trouble

Posted: 30 Sep 2013 09:29 AM PDT

Hi. I'm working on creating a histogram using time, as the set of 6 buckets that data can fall into. ie. something can happen between Midnight and 4am, 4am and 8am, 8am and 12pm, and so on.The column I have to break into these buckets is a datetime column.First, I checked on how to extract the time from the datetimeconvert(char(5), GETDATE(), 108)Returns this format. 15:22<--GoodNext I am trying to create the case statement. See below. SQL doesn't like the Between 12:00 and 04:00. select convert(char(5), GETDATE(), 108),case convert(char(5), GETDATE(), 108) between 12:01 and 04:00 then MidnightToFourelse restOfTimeend as timeframeError message:Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'between'.How do I frame the condition so that Times falling between midnight and 4am are assigned "MidnighttoFour" value in the TimeFrame column?

Compare delimited data - same data but in a different order

Posted: 30 Sep 2013 02:26 AM PDT

Hello All, I am currently working on comparing two versions of data for a comparison report. I have a delimited string of characters and I am trying to figure out if the data is the same but in a different order, I should not mark the record as different. I eliminated all data with different lengths. Edited...with data creation scriptsThe approach I am using is to split the values using a cross apply to a "Split by delimiter function" into a new table and doing left outer join on part number between the CURRENT AND PREVIOUS record sets and looking for where records are missing in the second table/record set. For example: -- Original DataCREATE TABLE #Table_Orig (PartNumber int, FILEVERSION varchar(100), VALUE varchar(100))INSERT INTO #Table_Orig VALUES (1234, 'CURRENT', 'A B')INSERT INTO #Table_Orig VALUES (1234, 'PREVIOUS', 'B A')INSERT INTO #Table_Orig VALUES (456, 'CURRENT', 'A B')INSERT INTO #Table_Orig VALUES (456, 'PREVIOUS', 'A D')-- Split Table - Once run through a splitter functionCREATE TABLE #Table_A (PartNumber int, FILEVERSION varchar(100), SplitVALUE varchar(100))INSERT INTO #Table_A VALUES (1234, 'CURRENT', 'A')INSERT INTO #Table_A VALUES (1234, 'CURRENT', 'B')INSERT INTO #Table_A VALUES (1234, 'PREVIOUS', 'B')INSERT INTO #Table_A VALUES (1234, 'PREVIOUS', 'A')INSERT INTO #Table_A VALUES (456, 'CURRENT', 'A')INSERT INTO #Table_A VALUES (456, 'CURRENT', 'B')INSERT INTO #Table_A VALUES (456, 'PREVIOUS', 'A')INSERT INTO #Table_A VALUES (456, 'PREVIOUS', 'D')-- Queryselect *from #Table_A prevLEFT OUTER JOIN #Table_A curr on prev.PartNumber = curr.PartNumber and curr.FileVersion = 'CURRENT' AND prev.SplitValue = curr.SplitValueWHERE prev.FileVersion = 'PREVIOUS'AND curr.PartNumber IS NULL-- Expected OutputPartNumber: 456SplitValue: DThis seems to work fine, but is a bit cumbersome. I did some searching on Stackoverflow and here with no real matches. Does anyone have any other "better" suggestions? Thanks, Anton

Using Dynamic SQL to build temp table...doesn't work?

Posted: 30 Sep 2013 03:24 AM PDT

I am trying to pass a column name as a parameter to build a #temp table combining data from two tables. When I exec the dynamic query it seems to build the temp table because I am receiving a message saying 2048 rows affected; however, I can't select from it, I am receiving an error saying that #TempLevels is an invalid object. Is this possible to do? What am I missing? Thank you in advance![code="sql"]declare @ColumnLevel as varchar(10);declare @ReportLevel as varchar(10);Set @ReportLevel = '2' set @ColumnLevel = case when @ReportLevel = '2' then 'level2' when @ReportLevel = '3' then 'level3' when @ReportLevel = '4' then 'level4' end print @ColumnLevel declare @sql as varchar(1000)set @sql = 'select * into #TempLevels from tblLevels as a join tblReports as b on a.'+ColumnLevel+' = b.ReportLevel' exec(@sql)select * from #TempLevelsdrop table #TempLevels[/code]

T-SQL and BCP to qureyout to a file on database premissions

Posted: 30 Sep 2013 04:55 AM PDT

Ok I at an end, I'm close but no grasping at straws. In this set of statement I collect instance and database permissions, put them into temp files, the merge the two files into one select. I'm now trying to dump that select into an output file. The command shell is opened and then closed, there are print statements that will show you results at each state, and there are also two different attempts to dispatch the query to a file. Can some please look and tell me why when the T-SQL is executed I get the bcp help menu back.-- OPEN the command SHELL EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 1GORECONFIGUREGO EXEC sp_configure 'show advanced options', 0GORECONFIGUREGO--Declare Variable needDECLARE @OutputFile NVARCHAR(100);DECLARE @FilePath NVARCHAR(100);DECLARE @bcpCommand NVARCHAR(1000);--Build Temp tablesCREATE TABLE #InstanceLevel( in_name nvarchar(128) not null, in_type_desc nvarchar(60), is_disabled int, create_date datetime, modify_date datetime, Default_database_name nvarchar(128), sysadmin int, securityadmin int, serveradmin int, setupadmin int, processadmin int, diskadmin int, dbcreator int, bulkadmin int);-- Table for database level premissionsCREATE TABLE #DBPremissions( DBName nvarchar(128) NOT NULL, MemberName nvarchar(128) NOT NULL, RoleName nvarchar(128) NOT NULL, DefaultSchema nvarchar (128), ServerLogin nvarchar(128) NOT NULL);-- Populate temp tablesINSERT INTO [#InstanceLevel] SELECT sp.name ,sp.type_desc ,is_disabled ,sp.create_date ,sp.modify_date ,sp.Default_database_name ,sl.sysadmin ,sl.securityadmin ,sl.serveradmin ,sl.setupadmin ,sl.processadmin ,sl.diskadmin ,sl.dbcreator ,sl.bulkadmin FROM sys.server_principals sp JOIN sys.syslogins sl ON sp.sid = sl.sid where sp.type in ('S','G','U');--populate database premissions tableINSERT INTO [#DBPremissions]EXEC sp_MSforeachdb 'use ? SELECT DB_NAME() as [Database name] ,MEM.name AS MemberName ,RL.name AS RoleName ,MEM.default_schema_name AS DefaultSchema ,SP.name AS ServerLogin FROM sys.database_role_members AS DRM INNER JOIN sys.database_principals AS RL ON DRM.role_principal_id = RL.principal_id INNER JOIN sys.database_principals AS MEM ON DRM.member_principal_id = MEM.principal_id INNER JOIN sys.server_principals AS SP ON MEM.[sid] = SP.[sid]';--- Test the REsults of the Insert/*SELECT * FROM #InstanceLevel order by in_name;SELECT * FROM #DBPremissions --where MemberName = 'A70ADOM\LS_WMSOPS' order by MemberName;SELECT il.* ,dbp.* FROM #InstanceLevel as il JOIN #DBPremissions as dbp ON il.in_name = dbp.MemberName --WHERE dbp.MemberName ='A70ADOM\LS_WMSOPS' order by il.in_name;*/--Try1/*SET @bcpCommand = 'bcp "SELECT il.* ,dbp.* FROM #InstanceLevel as il JOIN #DBPremissions as dbp ON il.in_name = dbp.MemberName order by il.in_name" queryout D:\test_it.txt -c -T -t' */--Try2SET @bcpCommand = 'bcp "SELECT il.In_name,il.in_type_desc,il.is_disabled,il.create_date,il.modify_date,il.Default_database_name,il.sysadmin,il.securityadmin,il.serveradmin,il.setupadmin,il.processadmin,il.diskadmin,il.dbcreator,il.bulkadmin,dbp.DBName,dbp.MemberName,dbp.RoleName,dbp.DefaultSchema,dbp.ServerLogin FROM #InstanceLevel as il JOIN #DBPremissions as dbp ON il.in_name = dbp.MemberName order by il.in_name" queryout D:\test_it.txt -c -t' PRINT 'The actual command ' ++ @bcpCommand;SET @FilePath = 'D:\'PRINT @FILEPATH;SET @OutputFile = 'test_it.txt'PRINT @OutputFile;--SET @bcpCommand = @bcpCommand + @OutputFile + '" -c -t","'PRINT ' The excuable comand ' ++ @BcpCommand;EXEC master..xp_cmdshell @bcpCommand--Clean up-- Temp Table Clean upDROP TABLE #InstanceLevel;DROP TABLE #DBPremissions;-- CLOSE the command SHELL EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 0GORECONFIGUREGO EXEC sp_configure 'show advanced options', 0GORECONFIGUREGO--------------------------------------------------------------------------------

Query Help

Posted: 30 Sep 2013 02:35 AM PDT

Hello.I have one table that looks something like this..3022224 G 4980 65 33022224 U 4980 596 23022224 G 4980 67 3And another that looks something like this..28077 1 302222428078 1 302222428079 1 3022224I need insert the following into another table based on the two tables above. The long number (3022224) is what I use to join the two original tables.28077 1 G 4980 65 328077 1 U 4980 596 228077 1 G 4980 67 328078 1 G 4980 65 328078 1 U 4980 596 228078 1 G 4980 67 328079 1 G 4980 65 328079 1 U 4980 596 228079 1 G 4980 67 3Any ideas would be much appreciated.Thank you,Keith

diffrence between nolock with braces and without braces

Posted: 03 Mar 2011 03:51 AM PST

Can someone explain me about the difference between nolock with braces and without bracesnolock (nolock)

FILEPROPERTY 'SpaceUsed' what it returns

Posted: 30 Sep 2013 01:17 AM PDT

Can anyone explain the behavior of the query below. I was trying to write a query that can be run on a SQL server that will return the database name, filegroup, logical filename and physical filename for every file on the SQL server with the size of each file and the space available within the file. the total space in for each file is returned correctly no matter which database the query is run from. The SpaceUsed values are only correct for the specific database that the query is run in.Is there a different way [different table] to get the "SpaceUsed" value from?Or does someone have a better way to get this group of information?Do you spot a mistake in my joins?Thank you for your help.SELECT d.name as DatabaseName, fg.groupname as FileGroupName, df.name as LogicalFileName,CASE df.type WHEN 0 then 'Data' ELSE 'Log' END AS UsageType,df.size/128.0 as SpaceInMB,df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,(df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/1024 as AvailSpaceInGB,df.physical_nameFROM ((sys.sysdatabases d join sys.master_files df on d.dbid = df.database_id ) join sys.sysfiles f on df.file_id = f.fileid ) left outer join sys.sysfilegroups fg on f.groupid = fg.groupid ORDER by d.name, fg.groupname, df.file_id;
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog