IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U')) DROP TABLE [dbo].[table_Data] GO /****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
help how to ? 7 conditions for evry day check for all month
a condition "code block" for evry day in the week like CASE inside CASE and How to insert it to #Temp table all
Code Block CREATE PROC YourProc @StartDate datetime = NULL, @EndDate datetime = NULL AS SET @StartDate = COALESCE(@StartDate,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))--defaulting to todays date if not supplied SET @EndDate=COALESCE(@EndDate,DATEADD(m,1,@StartDate))--defaults to 1 month from today
WHILE @StartDate <= @EndDate BEGIN SELECT CASE WHEN DATENAME( dw,@StartDate)='Sunday'
---- for Sunday----for Sunday------------for Sunday----for Sunday------v_un
WHEN DATENAME( dw,@StartDate)='Sunday' AND empid IN (SELECT empid FROM v_un WHERE (shift =45 ) THEN 1 WHEN DATENAME( dw,@StartDate)='Sunday' AND empid IN (SELECT empid FROM v_un WHERE (shift =51 ) THEN 1 WHEN DATENAME( dw,@StartDate)='Sunday' AND empid IN (SELECT empid FROM v_un WHERE (shift =11 ) THEN 2 WHEN DATENAME( dw,@StartDate)='Sunday' AND empid IN (SELECT empid FROM v_un WHERE (shift =12) THEN 2 WHEN DATENAME( dw,@StartDate)='Sunday' AND empid IN (SELECT empid FROM v_un WHERE (shift =22 ) THEN 3 WHEN DATENAME( dw,@StartDate)='Sunday' AND empid IN (SELECT empid FROM v_un WHERE (shift =23) THEN 3 WHEN DATENAME( dw,@StartDate)='Sunday' AND empid IN (SELECT empid FROM v_un WHERE (shift =34) THEN 5 ------------------------END for Sunday----for Sunday SELECT CASE WHEN DATENAME( dw,@StartDate)='monday' ---- for monday----for monday------------for monday----for monday----FROM v_1 WHEN DATENAME( dw,@StartDate)='monday' AND empid IN (SELECT empid FROM v_1 WHERE (shift =45 ) THEN 1 WHEN DATENAME( dw,@StartDate)='monday' AND empid IN (SELECT empid FROM v_1 WHERE (shift =51 ) THEN 1 WHEN DATENAME( dw,@StartDate)='monday' AND empid IN (SELECT empid FROM v_1 WHERE (shift =11 ) THEN 2 WHEN DATENAME( dw,@StartDate)='Monday' AND empid IN (SELECT empid FROM v_1 WHERE (shift =12) THEN 2 WHEN DATENAME( dw,@StartDate)='monday' AND empid IN (SELECT empid FROM v_1 WHERE (shift =22 ) THEN 3 WHEN DATENAME( dw,@StartDate)='monday' AND empid IN (SELECT empid FROM v_1 WHERE (shift =23) THEN 3 WHEN DATENAME( dw,@StartDate)='monday' AND empid IN (SELECT empid FROM v_1 WHERE (shift =34) THEN 5 --------------------------END for monday SELECT CASE WHEN DATENAME( dw,@StartDate)='Tuesday'
---- for Tuesdayy----for Tuesday----for Tuesday----for Tuesday----from V_2
WHEN DATENAME( dw,@StartDate)='Tuesday' AND empid IN (SELECT empid FROM v_2 WHERE (shift =45 ) THEN 1 WHEN DATENAME( dw,@StartDate)='Tuesday' AND empid IN (SELECT empid FROM v_2 WHERE (shift =51 ) THEN 1 WHEN DATENAME( dw,@StartDate)='Tuesday' AND empid IN (SELECT empid FROM v_2 WHERE (shift =11 ) THEN 2 WHEN DATENAME( dw,@StartDate)='Tuesday' AND empid IN (SELECT empid FROM v_2 WHERE (shift =12) THEN 2 WHEN DATENAME( dw,@StartDate)='Tuesday' AND empid IN (SELECT empid FROM v_2 WHERE (shift =22 ) THEN 3 WHEN DATENAME( dw,@StartDate)='Tuesday' AND empid IN (SELECT empid FROM v_2 WHERE (shift =23) THEN 3 WHEN DATENAME( dw,@StartDate)='Tuesday' AND empid IN (SELECT empid FROM v_2 WHERE (shift =34) THEN 5 ................................................END for Tuesday ///////// until Saturday ..... WHEN DATENAME( dw,@StartDate)='Saturday' AND ... THEN .. END SET @StartDate=DATEADD(d,1,@StartDate) END GO
Can somebody please help me with the following problem.
I want to import data from a textfile called "Links.txt" into a SQL-server database called "LinkData". The data in this textfile is separated by pipelines. And this import should be done every 2 hours automatically. How can I make a proces or something in SQL server 7 that will fill the database with the data out of this textfile every 2 hours. Please help me. I really don't know.
I want to import data from a textfile called "Links.txt" into a SQL-server database called "LinkData". The data in this textfile is separated by pipelines. And this import should be done every 2 hours automatically. How can I make a proces or something in SQL server 7 that will fill the database with the data out of this textfile every 2 hours. Please help me. I really don't know.
I was planning on using DTS for this Only I really don't know how. I tried it with "Data driven query task" in combination with "Text file (source)" and "Microsoft OLE DB Provider for SQL Server". But when I run it, it gives an error. What am i doing wrong??? Help me please
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED") I tried the following which doesn't work: =IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"), IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
I have a job where the first step starts and checks for a condition. If its not true, I want it to reset itself and start again in 10 minutes. I'm using sp_stop_job and sp_update_jobschedule and, initially, it looks like it works. But since it's a Daily job, the 'Next Run Date' increments to the following day. Even though I'm using sp_update_jobschedule to keep the active_start_date as the same day, it still increments. I've tried updating sysjobschedules directly, but get the same results.
Any thoughts much appreciated! Here's my code: USE msdb
--This is the part that goes in the job step --and increments the next_run_time if the condition is true.
If (Select count('x') from mytable (NoLock) Where PublicationDate > getdate()) < 1 BEGIN Declare @ActiveStartDate int Declare @ActiveStartTime int
Select @ActiveStartDate = active_start_date from msdb.dbo.sysjobschedules (NoLock) Where schedule_id = 61 Select @ActiveStartTime = active_start_time from msdb.dbo.sysjobschedules (NoLock) Where schedule_id = 61
I am trying to develop a sql statement that will create a recordset of the min (or max) values in x minute increments over a period of time.
e.g. over a period of 7 days, I have data that was collected in 1 minute intervals. I need to know the min (or max) value in each 10 minute interval over that same period of time.
I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.
I also need to adjust rows, say mth and nth that meet condition , say y.
My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)
This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.
Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:
ID Int1 Type1 Ideal Rank by Type
1 1 1 1
2 1 1 2
3 2 1 3
4 3 1 4
5 5 1 5
6 8 2 1
7 13 1 6
8 21 1 7
9 34 1 8
10 55 2 2
11 89 1 9
12 144 1 10
13 233 1 11
14 377 1 12
15 610 1 13
16 987 2 3
17 1597 1 14
18 2584 1 15
19 4181 1 16
20 6765 1 17
The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...
I hope this posting is clear, albeit long.
Thanks in advance
Greg
PS The code:
USE
master
GO
CREATE DATABASE CertainRowsToChange
GO
USE CertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
hey all, i need to find the ratio of difference in 2 datetime variables and the difference of another 2 datetime vars. I figured the best way to do it is to convert the difference in both numerator and denominator to number of minutes.
I am runing from .NET application an SQL Query it normally return the rows in 10 seconds but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)
I have a result that comes out in number of seconds, but need to see it converted to minutes and hours and seconds. Is there a convert function that would do this?
Consider a table that holds Internet browsing history for users/machines,date/timed to the minute. The object is to tag all times that are separatedby previous and subsequent times by x number of minutes or less (it couldvary, and wouldn't necessarily be a convenient round number). This willenable reporting "active time" for users (a dubious inference, but hey).There are a lot of derivative ways of seeing this information that might begood to get to. What's the fist and last of these sets of times? Whatpercentage of a given period is spanned by active times, and not? What isthe average duration of such periods? What is the average interval betweenweb hits during such periods? During other times?Blah, blah. The basic problem is my principal problem. I don't have muchexperience with cursors, but from what I understand it would be very goodindeed to spare them, given the number of records I anticipate workingwith.I'd be glad of any pointers.--Scott
Hi, the SQL DB freezes and no one can access the DB for 5 or 10 minutes. Even select queries don t execute, nothing is displayed.
Until we kill the process id that s blocking in the sql activity monitor, only then the DB is released and people can work again. What does it mean that no query executes until we kill the processes ID? what could it be?
Also, recently we created indexes and ran tuning adviser, is it possible that the creation of indexes cause the freeze of a DB? is that possible?
I have SQL 2005 full version installed a remote server and when I start my computer I can connect to the database from SQL Managment Studio and a program I'm making. After about 15 minutes I find that I cannot connect using both programs, but if I enter the servers IP address using Managment Studio I can connect (this does not happen with the program I'm making). I have to log of my computer before I can connect again.
I want to create a Trigger that will run every 5 minutes. This trigger would actually run a Stored Procedure named say "SP_SetRooms". This SP will in turn run and andupdate the rooms table. Would anyone know how to help me get started on creating a Trigger with the info I've provided? Thank you, Dharmendra parihar
I've the following simple code bound to a button on a Web page
string time = (string)Cache["KEY"]; if (time == null) {
SqlConnection sqlConnection = new SqlConnection(@"Server=BIZYUSUFSQL2005;Database=Deneme;User Id=sa;Password=;"); SqlCommand command = new SqlCommand(@"select KOLON1 from dbo.CACHE", sqlConnection); sqlConnection.Open();
SqlCacheDependency dependency = new SqlCacheDependency(command); time = System.DateTime.Now.ToString(); Cache.Insert("KEY", time, dependency); command.ExecuteNonQuery(); sqlConnection.Close(); } return time; This code has to return the time value from cache. And when a record is inserted into the CACHE table, the cache item has to be invalidated and the new time value has to be returned. The code works properly for 2-3 minutes. But when there is no activity for 5 minutes, the cache invalidation does not work anymore.
The company I work for has no experience with replication, and neither have I. Now I recieved a functional specifications document and apparently what they want is use replication to maintain a copy of a production database on the data warehouse server. The database is 70GB, and they want to do it every 15 minutes (so their reports will contain up-to-date data).
As I said, I know nothing about replication, but to me, this sounds like madness. I fear that this will will (at least) have a negative impact on the performance of the production database.
So is it possible? Does replication have a big impact on the database or is it hardly noticable? I expect about 500 new records every 15 minutes; production database and data warehouse are on different servers; both are SQL2005.
I have two field and I need to find the sum I guess of them. One is called the clm_dout (process date) and the other one is clm_rcvd (received Date). These are both date and time fields and it looks like this 2006-03-17 00:00:00.000. I need to create a formula that wiil i guess give me the difference and create it into hour and minutes because I an trying to create a turn around report. this is what I had...
TimeValue({clm_doubt}-{clm_rcvd})
This is not working for me. Can someone give me a suggestion please.
