Flag Last Two Hours Of Day In A New Column

Feb 4, 2014

i have an hours and a seperate minutes column

e.g.

Hour
10
Minute
15

I now need to flag the last two hours of the day in a new column both datatypes are INT

View 6 Replies


ADVERTISEMENT

Un-flag Reinitialization Flag On Trans Subscription

Nov 21, 2004

Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.

(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)



The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.

HELP !

View 3 Replies View Related

Query To Update Flag Column In Second Table Based On Adder Names

Sep 11, 2013

I want to update Flag column in second table based on the Adder names.

If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.

APpName OS Adder

App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql

App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB

OUTPUT SHOULD BE LOOK LIKE BELOW

APpName OS Adder Flag

App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null

App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE

View 5 Replies View Related

8 Hours To Add A Bit Column

Jul 1, 2004

I ran the following:

ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0


On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.

Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID

Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?

View 13 Replies View Related

Adding A BIT Column - 100+ Hours!

Jul 7, 2004

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?

View 9 Replies View Related

Transact SQL :: Hours To Show In A Column?

Jun 19, 2015

i'm trying to capture data for every hour and would like to display the hours of the day in a column.

select
case when (datepart(hh,calldate) between 0 and 7AM then 1 else 0 end as [12-7AM],
case when (datepart(hh,calldate) = 8 then 1 else 0 end as [8AM]
from table1
where cast(calldate as date) = cast(calldate as date)

My desired result should display

12-7 am
8 am
9 am
10 am

View 4 Replies View Related

Transact SQL :: To Display Days Hours Mins Format Based On Business Hours

Apr 22, 2015

I want to display Days Hours Mins Format.

I am Having two columns Like below,

Col1 (in days)    col2 (In Hours : Mins)
3days  4:5 

In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5

From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.

View 6 Replies View Related

Select Rows With Datetime Column Ealier Than 2 Hours Ago?

Apr 5, 2007

Hello.


I have a table with a datetime column.




I need to select rows with a NULL or a datetime earlier than 2 hours
ago if getdate() time is currently between 6am and 7pm.




Thanks for any help or information!

View 1 Replies View Related

Breaking Down Total Hours Worked Into Day And Evening Hours

Sep 21, 2006

I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'

View 3 Replies View Related

Converting Decimal Hours To Hours And Minutes

May 13, 2008

I have a float variable that holds a decimal number of hours.

So 1.5 equals 1 hour 30 minutes.

I need to change this to the format 1:30

Any idea how to do this?

View 10 Replies View Related

Flag

Jul 3, 2007

hi all!

i have columns.
1. Flag : stores only + / - / <NULL>
2. Number : stores float

how would i select this 2 columns to generate -ve and +ve number depend on the flag? eg :
Flag : -
Number : 4 ====> -4 and +4 if flag=+

when i try to just to combine with -(Flag) for -ve, it gave converting type error even i already CAST it..

tq in advance


~~~Focus on problem, not solution ¯(º_o)/¯ ~~~

View 10 Replies View Related

Trace Flag Help

Apr 2, 2001

Hi,
I'm trying to use trace flag 1204 to get some detailed deadlock information
In EM, I add startup parameter -T1204, and then I stop and start the server.
I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.

View 1 Replies View Related

Version Flag

Apr 29, 2008

Guys,

I have following scenario where I need to populate version flag based on the partyid, count and seq columns.

Here is it how it should populate for each new count of a partyid the max(seq) should be versioned as 1

For example

PARTYCOUNT SEQ VERSION
_________________________________________________
10011 1
10111 0
10112 1
10121 0
10122 0
10123 1
10231 1

Is there any way to populate this version flag.

Any sugggestions and inputs would help

Thanks

View 3 Replies View Related

Triggers And Flag Bit

Mar 24, 2008

I have a perl script (uses sql query) that will run if there is anyupdate in the row. Currently, I am a trigger that sets the DateModifiedfield with getdate() Then my perl script looks for certain range ofmodified dates and runs the necessary query.I am trying to come up with a different mechanism where I don't have touse the date field. I am looking into flag bits. But not sure howto use it.Should I create a trigger that will set the flag bit after any update?This way the script will just look for the updated records, regardlessof what time it was updated. Maybe I am misunderstanding flag bits, thenafter another subsequent update how would the perl/sql script know whichrecords were updated? I hope this make sense.thanks.--s

View 3 Replies View Related

Backup Flag

Dec 5, 2007



Hi there,


I'm working on a program and I want it to check to see if the database it is pointed to has had a backup done against the database in the last 10 days. I will then throw a message box indicating that the database has not been backed up and that they should consider doing so.

Is there any attibute that I can check to see when the last time the database was backed up?

THanks for your input! - Eric -

View 3 Replies View Related

Regressor Flag

Oct 10, 2006

I am trying to build a decision tree algorithm with multiple regressors. I am having trouble changing the properties for the input variables to regressors (the only multiple flag choice is "not null"). I created a mining model using the DMX query thinking I could change the code to include multiple regressors, however I get a syntax error when trying to execute the model. Also, I am wondering if you can view the dmx code for a model built using the mining wizard.

My query is as follows:

CREATE MINING MODEL BVRSlotq1052

(Machine Long Key,

[Banksize] LONG CONTINUOUS REGRESSOR ,

[Cabinet] Text Discrete,

[CUID] Long Continuous PREDICT_ONLY,

[Denom] Text Discrete,

[Gametype] Text Discrete,

[Max Coins] Text Discrete,

[Par] Long Continuous,

[Pos] Text Discrete,

[Progressive] Text Discrete,

[Type]Text Discrete)

Using Microsoft_Decision_Trees

View 3 Replies View Related

Getting Records Where Flag='Y' Or If No Y Then N

Feb 28, 2008


If you have a table with columns like Category int, Cost money, EffectiveDate DateTime, SpecialFlag (char Y or N) how would you write a query to get for each category, get the most recent, with a specialFlag of Y if a Y record exists and if not, grab the N record?

I have tried using the exists clause for the flag, but as soon as I mix in the group by to get the latest effectivedate, the max is evaluated first and I am not guaranteed to use the Y records if they exist.


To illustrate:
A 1, $10, 1/1/2007 Y
B 1, $12, 1/1/2007 N
C 2, $5, 6/7/2007 N
D 2, $4, 1/1/2007 N
E 3, $1, 1/1/2007 Y
F 3, $2, 6/7/2007 Y
G 3, $3, 2/1/2008 N

I want records A because it is flagged Y, C because it is the most recent and there is no Y record and F because it is flagged and most recent.

View 6 Replies View Related

Flag Random Record

Mar 31, 2006

I am using this select statement to radomly display a recordSelectCommand="SELECT TOP 1 *  FROM [TBL_Example] ORDER BY NEWID()I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement.  I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.Any ideas?

View 1 Replies View Related

T-SQL (SS2K8) :: Add A Flag When Certain Conditions Are Met?

Aug 27, 2015

I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.

Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.

WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS
(
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ALL
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'UNION ALL
SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)

[code].....

Current Results

PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1

Desired Results

PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE FLAG
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1 NULL
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2 NULL
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1 1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2 2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1 NULL

View 2 Replies View Related

Update Notification Flag

Apr 16, 2008

i want to make a stored procedure that will update the notification flag in the table.

If the email was succesfully sent the flag will be updated as SENT. And if it is un successful the flag will be NOT SENT in column of the table in SQL. The email will be send using .NET codes and updates of notification flag will use SQL stored procedure. Please help

View 2 Replies View Related

How To Flag Duplicate Records

Apr 25, 2012

Table Example:

ID NAME FLAG
1 Bill null
2 Bill null
3 Bill null
4 Bob null
5 Henry null
6 Henry null

I want to keep the flag empty for one record and mark the rest as duplicates so the output would be:

ID NAME FLAG
1 Bill null
2 Bill D
3 Bill D
4 Bob null
5 Henry null
6 Henry D

How can I go about doing this?

View 4 Replies View Related

Transact SQL :: Flag Yes Or No Within ID Group

May 20, 2015

I have a table with 4 columns,let say ID, WorkId , HireDate ,TerminateDate

My source date

ID   WorkId   HireDate    TerminateDate
1     123    2006-01-05   2013-01-13
1     456    2006-02-23   2013-04-13
1     789    2005-12-12   2013-06-13
2     913    2004-12-15   2012-01-13
2     406    2006-04-13   2014-04-13
3     113    2009-11-15   2011-01-13
3     106    2010-04-13  
4     513    2002-11-15  
4     168    2011-10-23  
5     342    2007-01-05   2009-01-13      
5     416    2008-02-23                   
5     159    2008-12-12   2010-06-13   
6     542    2005-01-03   2007-01-13      

Rule -1:  set flag = yes

-Whenever terminatedate is not null
-Also with in the ID group if one record terminatedate is null then set flag=yes

Rule-2: Set flag=No
- with in the ID group Whenever terminatedate is null

My output should look like this..

ID   WorkId   HireDate    TerminateDate     Flag
1     123    2006-01-05   2013-01-13        yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null)
1     456    2006-02-23   2013-04-13        yes
1     789    2005-12-12   2013-06-13        yes
 2     913    2004-12-15   2012-01-13       yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)

[Code] .....

View 3 Replies View Related

Setting Trace Flag

Jun 24, 2007

In order to use Microsoft Dynamics NAV with the SQL Server 2005, I need to set Trace Flag 4616, and I know it's a setting somwhere, where I can add this flag no. -but I can't remember where? Please help?

View 1 Replies View Related

Trace Flag (-T1222)

Sep 13, 2007



Hi,

Do i need to restart SQL Database Engine Service once I set trace flag (-T1222).


Regards
Sufian

View 1 Replies View Related

Setting A Failure Flag

Mar 13, 2008



I created a package wide OnError event handler with the following script:


Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

'

Dts.Variables("FailureFlag").Value = True

'

Dts.TaskResult = Dts.Results.Success

End Sub

End Class



I ran the package with an error and set a break on error condition. When I looked at the value of "FailureFlag" it was False. Any ideas why it is not being set?



I have a post execute event that is supposed to rollback from a snapshot on failure and it is not doing so on failure so I'm trying to figure out what's wrong.

View 7 Replies View Related

Waiting On Flag To Activate Jobs ?

Feb 17, 2005

is there a way to automate a job to wait on a flag in a directory and to start my maintenance plans ?

if so how would i code is there external functions plz help.

View 8 Replies View Related

Zusammengesetzter Primary Key Mit Deleted-Flag

Jul 20, 2005

Knifflige Frage zu Datenbanken:Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und GMID.Soweit ok.Nehmen wir an, aus dieser Tabelle sollen keine Datensätze physikalischgelöscht werden, sondern der Löschstatus durch ein Datenfeld GELOESCHTangegeben werden. Hat das Feld den Wert 0, dann ist der Datensatz nichtgelöscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Löschung drin.Dann funktioniert der PK nicht mehr, denn es können ja durchaus mehrereDatensätze mit gleicher MAID und GMID vorhanden sein. Aufnehmen vonGELOESCHT in den PK geht theoretisch, ist aber gefährlich (daFließkommafeld, und wer weiß schon, ob 0 immer gleich 0 ist...).Hat jemand eine Idee?SWE@KP

View 11 Replies View Related

Setting The Auto Shrink Flag

Jul 13, 2007

Hi,



I recieved an alert that says 'The Auto Shrink flag for database is not set according to best practice.' Can anyone tell me where I can find this flag and what it shoul dbe set to?



Thanks



Af.

View 4 Replies View Related

Use Trace Flag 1400 During Startup

Apr 2, 2008




Hi all

i am using Sql Server 2005 SP2, I am trying to configure Mirroring. The issue is ,After configuring mirroring ,Whem i am starting START MIRRORING button .I am getting the error

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)


I used BDCC TRACEON (1400) and I altered the Startup parameter in configuration manager of SQL SERVER ;-T1400


But still I am getting the above error which is indicated in red

Please help me to fix the issue

View 19 Replies View Related

DB Engine :: What Does Trace Flag 9520 Do

Sep 16, 2015

I found trace flag 9520 is in the startup parameters, but I cannot find what the trace flag does from internet.

View 6 Replies View Related

Is Trace Flag 4199 Needed After Hotfix

Sep 13, 2012

I know that several hotfixes need you to turn on trace flag 4199 to activate them. But after reading an article on that trace flag, and the way it was worded, I couldn't tell if you still needed to use the trace flag, once that hotfix was incorporated into a service pack.

View 1 Replies View Related

Transact SQL :: Current Flag On Merge Statement

Nov 12, 2014

I was using Type 2 for one of our Fact table.... and need to put a flag to know which one is the Current record... I couldn't able to figure how to implement logic in the merge statement... This is an example Query ....I was using like this for my fact table...

Basically I need to track CustomerName and City... So I need a Currentflag (Y) for latest record....

MERGE INTO [dbo].[TargetCustomer] AS TRG
USING [dbo].[MyCustomers] AS SRC
ON TRG.[CustomerID] = SRC.[CustomerID]
AND TRG.[CustomerName]=SRC.[CustomerName]
AND TRG.[City]=SRC.[City]

[Code] .....

View 7 Replies View Related

Save Me From A CURSOR. Flag Values That Won't Convert.

Dec 10, 2007

Hey all:

Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.

Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.

I have another table that houses the primary key and the field of the record that cannot convert.

Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.

I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated.

--Thanks--

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved