Transact SQL :: Identify Shift Which Is Falling To Next Day

May 17, 2015

I have the following shift table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

[code]...

I need to identify which shift is currently running on by providing the current time.I used the following query to get the result. It is giving correct result for Morning & Afternoon Shift but failed to produce result for Night Shift.

SELECT ShiftName, Shiftid
FROM WHShifts
WHERE DATEPART(HOUR, @Currenttime)>=(DATEPART(HOUR,CAST(ShiftInTime AS Datetime))) AND DATEPART(HOUR, @Currenttime)<=(DATEPART(HOUR,CAST(ShiftOutTime AS Datetime)))

Is there any way to find out the night shift from a given time.

View 15 Replies


ADVERTISEMENT

Transact SQL :: Calculating Date And Time For 3rd Shift

Jul 13, 2015

We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.

Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)

[code]...

View 3 Replies View Related

Transact SQL :: Shift Analysis - CAST Using Times

Nov 9, 2015

I am doing some analysis on shifts and what I want to do is say if the time from a timestamp field is between 00:00 and 05:59 then the figures belong to the day before.  Eg.  We have a night shift that work 22:00 - 06:00.

Here is my coding which logically looks sound to me but when I run it, it has an error:

SELECTe.ID,
CAST(e.TimestampasDate)asEventDate,
CAST(e.TimestampasTime)asEventTime,
CASE
WHENCAST(e.TimestampasTime)BETWEEN'00:00:01'and'05:59:59'
THENCAST(e.TimestampasDate)-1
ELSECAST(e.TimestampasDate)
END
FROMIntouch.Event

View 5 Replies View Related

Transact SQL :: Identify Blocking Process

May 7, 2015

Imagine I set a begin transaction on table a and updating a row and not committed and not roll backed--first connection

From second connection I am selecting same table (obviously it wait until first connection commits/rollback based on my transaction level: my isolation level is read committed).

1. How do I know second connection is to waiting to first connection to complete.
2. If I want to select rows that are not locked by update process how do i need to do(ex:row 1,2,3,4 and 1 is locked by update process(exclusive lock) and i want to leave that and i need to select 2,3,4 records).

View 6 Replies View Related

Transact SQL :: Query To Identify Overlapping Date Rows

Aug 18, 2015

I have a table with multiple rows per staff person.  Each of these rows has staff_id, start_date, and end_date.  Per staff, if any start_date comes between the start_date and end_date of a different row, or if any end_date comes between the start_date and end_date of a different row, then I have to flag these records as being identical. 

How can I do this?  I have tried doing a Cross Apply because I thought that would do Cartesian product (comparing every row), and I've also tried temp tables. But I haven't gotten either of these to work. Here is some dummy data:

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#staff_records')
) DROP TABLE #staff_records;
create table #staff_records
(
staff_id varchar(max),

[Code] ....

View 12 Replies View Related

Transact SQL :: Identify Primary And Foreign Keys In Two Table

Apr 23, 2015

I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?

CREATE TABLE StockNames
(
-- Added Primary key to [stock_symbol]
[stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY,
[stock_name] VARCHAR(150) NOT NULL,
[stock_exchange] VARCHAR(50) NOT NULL,

[code].....

View 19 Replies View Related

Transact SQL :: How To Identify When There Is A Break In Continuity Of Integer On A Column

Jul 16, 2015

I have two columns, one column has a document ID and a given document can have many pages. The second column has the pages. Now I want to find out when the page number is broken. For example, if doc ID 1 has 3 rows and each of the three has 1,2,3 and then the fourth row has document 1 but the value jumps from 3 to 7 and then goes to 8,9,10 and then jumps again and starts from 17, i want to have the ranges identified.

DocID Page Number 
1    1
1 2
1 3
1 7
1 8
1 9
1 10
1 17
1 18
1  19
1    20

The result should look like :

DocID Page Number 
1   1-3
1     7-10
1   17-20

View 4 Replies View Related

Need Help Getting The Day Shift After Midnight

Dec 2, 2006

Hello there,I am having a small problem which been challenging me for few days andneed help or advice.I am trying to calculate the day-shift for employees based on the timethey started and finish working, I will only have 2 shifts 1 or 2 .Shift one changes based on the location however any thing else is shift2. The problem I am having is when someone signed in after midnight; Ineed to report his time under shift 2 for the previous day date. So ifhe signs at 12:30 AM on 12-12-2006, I need to report that as shift 2 on12-11-2006 and that’s where my problem is. Is there a way to subtractthe date by 1. I am using SQL Server and here is a simplified tables Iam working with:Employee tableEmployeID LocationID StartTime EndTime123 555 11:00:00 AM 3:00:00 PM183 559 7:00:00 AM 11:00:00 AM…Shift tableShiftNumber LocationID StartTime EndTime1 555 7:00:00AM 2:00:00PM2 555 2:00:00PM 12:00:00AM1 559 6:00:00AM 4:00:00PM…..So I am trying something likeCASE WHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationIDAND(TR.StartTime>=StartTimeANDTR.EndTime<= EndTime)) =1 THEN 1ELSEWHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationIDAND(TR.StartTime>=StartTimeANDTR.EndTime<= EndTime)) =2 THEN 2ELSE?????????????? NEED HELP HERE WHEN EMPLOYEE SIGN AFTER MIDNIGHTEND)FROMEmployee TRINNER JOIN Shift ON LocationID = TR.LocationID*** Sent via Developersdex http://www.developersdex.com ***

View 3 Replies View Related

Items Centered Or Right Of Center Shift Far Right

Dec 27, 2007

Hello,

I have a BIDS project with 14 reports, all formatted the same way. On one report, any text box or image that is placed in the center or right of center in the form, shifts very far to the right of the tables and charts when it's rendered.



The text box placed at the top left of the form stays put. This is really wierd.

Has anyone ever heard of anything like this.

I have a couple of screen shots if you have a place I can put them.

Thanks

Dan

View 3 Replies View Related

Deciphering Employee Shift Data

Feb 11, 2008

Here is my current challenge that I realize I could do a few different ways but nothing efficient or flexible comes to mind. Hoping one of you guys can help me out with an elequent efficient T-SQL script.

Employee workstate information in our system is stored by segment. Segment 1 cooresponds to the entire shift while segments greater than 1 coorespond to subsequent breaks during the shift (Segment 1). i.e.

SegmentNumber SegmentStart SegmentEnd

1 10:00am 06:00pm
2 12:00pm 13:00pm
3 03:15pm 04:00pm


What I would like to do is generate continuous nonoverlapping records. i.e.

SegmentNumber SegmentStart SegmentEnd

1 10:00am 12:00pm
2 12:00pm 13:00pm
3 13:00pm 03:15pm
4 03:15pm 04:00pm
5 04:00pm 06:00pm


*Notice End has changed to match start of first break and so on. Also records have been added to fill gaps between breaks during the shift.

What adds to the challenge is that the segment number for a given employee/report day can be 1 (meaning no breaks) to any number (lots of breaks). The segment start and end times can be any increment. In addition Breaks can be divided into paid time and unpaid time but I imagine given a solution to the above I could apply it to another level down on my own.

Thank you in advance for your help!

View 3 Replies View Related

SHIFT + SPACE In Table Editor

Feb 6, 2007

The default behaviour for pressing SHIFT + SPACE when editing text inside a cell in the Table Editor view in Microsoft SQL Server Management Studio Express is to change focus to select all cells comprising the current row. This behaviour lends itself to the default behaviour for pressing SHIFT + {LEFT | RIGHT | UP | DOWN } in changing the focus to select adjacent cells.

However, I find this is quite annoying as the SHIFT + SPACE combination is quite a common miss-type when entering text in UpperCaps or when entering text with spaces while holding the SHIFT key.

Anyone else have this gripe with MSSMSE? Anyone know how to disable this behaviour?

Simon

View 2 Replies View Related

Making The Shift From Access SQL To 'Real' SQL ;-)

Apr 10, 2006

for the record, this is my first post and i'm not sure this is the right place to post, but it makes sence to me.

i've been spending a great deal of time with our new MS SQL server and love it but i've noticed there's a fairly large diff between the SQL i'm used to seeing in MS Access and the SQL that the server uses. as you can see by my below statement i'm starting to get the hang of this but i still run into new issues...

my question... what's wrong with my IIf statements? this is what the server spits out...

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'LIKE'.

[ACCPAC_OESHDT].[TERRITORY] is an nvarchar(6) and contains data such as '1', '2', '3', etc...

i've tried many many variants and always have the same outcom. i've tried casting as an int, rtrim to rid the value of any possible white space, expanded versions.

oh, almost forgot... my environment is... I open the SQL Management Studio then logon to SQL Database Engine and then create a New Query.  

i'm stuck, please advise. ;-)

SELECT

ACCPAC_OESHDT.YR,

ACCPAC_OESHDT.PERIOD,

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory'))) AS TERRITORY,

SUM(CAST([SAMTSALES] AS MONEY)) AS Sales

FROM ACCPAC_OESHDT

GROUP BY

ACCPAC_OESHDT.YR,

ACCPAC_OESHDT.PERIOD,

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory')))

HAVING ((SUM(CAST([SAMTSALES] AS MONEY)))>0);

View 7 Replies View Related

Grouping By Custom Datepart For Shift Patterns

Apr 27, 2006

My application has a log table with a timestamp field which represents the time when the record was inserted.

We would like query the the table and group the results into date units based on the value of the timestamp.

The grouping specification is held in another table but only specified for a single week.

For example

WeekDay Start End

1 06:00:00 14:00:00

2 08:10:00 17:00:00

What this specification means is that when the log table is queried records with a timestamp that fall on a Monday between 06:00:00 and 14:00:00 should be grouped together, on Tuesday the group is records between 08:10:00 and 17:00:00 and so on...

The only way I can think of doing this is to generate a temporary table when the log table is queried that has a unique record for each time period for the span of the query and then attempt to join this table to the log table using the timestamp and then group based on the temporary table unique ID.

I'm not sure how to generate such a temporary table from the specification table so any help would be appreciated.



Thanks in advance.

View 5 Replies View Related

How To Display Unicode Data Stored In MSSQL As Shift-jis In ASP

Nov 22, 2006

 We are experiencing problems in presenting Unicode data stored in MSSQL as Shift-jis format in ASP application.
In MSSQL we are storing kanji text in database with a field type as nchar. Other application loads these tables using codepage 932. In MSSQL data is stored in Kanji correctly.
In ASP application, when we are trying to read data from these tables, Kanji characters are coming as '?????'. We are using shift-jis character set in ASP.
Templates for field and column header uses shift-jis, but a record set coming from MSSql is Unicode.
Is there a  way to convert from Unicode to Shift-jis in MSSql or in ASP .
Please help!!

View 1 Replies View Related

Report Objects Shift To Right On Very Wide Report - Why?

Mar 13, 2007

Greetings,

I have a very wide report of more than 20 inches. I've placed several parameter values in the report header section so that the user can see what filters have been applied to the data. The testboxes shift their position several inches to the right when the report is run from the Report Manager.

Is there a way to make sure that a textbox is displayed at an absolute position? I thought maybe there would be a property on the report or body object that controls this but I don't see one.

Thanks for your help,

BCB

View 7 Replies View Related

How To Identify A Dll

Apr 17, 2008



Hi,

How would I know that a dll component I built is in .NET framework 1.0 or 1.1??

annej

View 4 Replies View Related

Identify Neg And Pos Values

May 7, 2003

How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit

l've written it in layman's terms .Is it wise to use substring or they is a better method......

select
case
when amount is (negative) then 'C'
when amount is (positive) then 'D'
else
end

from table_Tran

View 2 Replies View Related

SHOWCONTIG - Need A Bit Of Help To Identify If

Oct 11, 2007

Greetings.

I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched.

However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) )

Tipps, suggestions, help, all is very wellcome! :-)



DBCC SHOWCONTIG scanning 'TInsurance' table...
Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 1275
- Extents Scanned..............................: 225
- Extent Switches..............................: 224
- Avg. Pages per Extent........................: 5.7
- Scan Density [Best Count:Actual Count].......: 71.11% [160:225]
- Extent Scan Fragmentation ...................: 74.67%
- Avg. Bytes Free per Page.....................: 520.0
- Avg. Page Density (full).....................: 93.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

View 9 Replies View Related

How To Identify An Array

Nov 20, 2006

I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.

View 1 Replies View Related

How To Identify Hot Tables

Jul 20, 2005

Does anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.

View 2 Replies View Related

How To Identify The Fixpack Level?

Jul 21, 2004

hi,
for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server?
is there any command, or any gui tool to identify the level?

tnks

View 3 Replies View Related

Identify Value In A Column Nullified

Jan 20, 2006

hi,

I have a huge db with many services ,users and applications hitting the db.
Suddenly one of our column is nullified , we are not able track who /how it is done,

Can any one tell be whatz the best way to identify this????
trace(what events to select ), trigger or what????



Thanks,
srini

View 2 Replies View Related

How To Identify SP Used By Front End Application

Sep 24, 2012

there is a Crystal Report run from the Front end Application. The DB used here is SQL Server 2008. I need to know the Stored Procedure used by the report that is been run from the front end. . How shall I do it?

View 6 Replies View Related

Statement To Identify Certain Fields

Sep 19, 2013

I am extract data from one of our Sql servers with the code below

Code:
Select SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr

From Prdct

INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode

where supersed =''
And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%'
And LISTPR1>'0' And STANCOST>'0'

Which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can't just add

Code:
'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End

For it will mark the A000 as invalid, is there a way of getting around this...

View 6 Replies View Related

Identify Backup Files

Apr 4, 2008

How to identify the backup files, whether the backup files were taken using SQL 2000 or SQL 2005?


Advance thanks,
Ravi

View 1 Replies View Related

Identify Potential Duplicate

Apr 22, 2015

There is one report to identify potential duplicate in a table and it is performing poor.I'm now tuning the existing SP and got struck in modifiying it. rewrite the query in a best way. I just pasted below an example of query which is now in a report.The report will be run every week currently the table has 10 million records, and every week there will 5k to 10k will be added up so with that 5k to 10 k we have to check all the 10 miilion rows that if it is duplciated the logic is (surname = surmane or forename = forename or DOB =DOB )

Create table #employee
(
ID int,
empid varchar(100),
surname varchar(100),
forename varchar(100),
DOB datetime,
empregistereddate datetime,
Createdate datetime

[code]...

View 7 Replies View Related

Identify What Tables Are Being Access In SQL

Jul 20, 2005

The database I'm currently working with is very old and some of thetables, SP, and views are not being used. I'm looking for a way toidentify what items are no longer in uses, or what items arecurrently in use.

View 4 Replies View Related

SQL To Identify The Primary Key In A Table?

Oct 24, 2007

Hi

I have been looking for a way to identify the primary key defined in a
table using a SQL Sentence...how can i do it?

Thanx for your support!!!

Diego Bayona

View 5 Replies View Related

How To Identify What Consumes Cpu In Sql Server

Jun 1, 2007

Hi all
I use 64 bit 2005 server with 8cpu and 8G of memory.
This server is accessed by large number of intensive or not so intensive programs.
I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good.
But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it.
If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good.
Task manager shows that all of it 75% attributed to sql server.
So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.

Thank you, Gene.

View 3 Replies View Related

Need To Identify Column In Resultset

May 10, 2007

Okay, I now have some dynamic SQL working. This is the SQL statement I have for a report in Reporting Services:



DECLARE @SQL nvarchar(4000)

SET @SQL=(SELECT AdHocSQL

FROM RptValueTypeMap

WHERE RptValueTypeMap.SectionCd in ('ITEM0010'))

EXECUTE (@SQL)



We have a table set up that actually holds different SQL statements based on the report items. This is reading the SQL statement from AdHocSQL for the Report item #0010 and it is returning the results. However, it does return the correct value, but under (No Column Name). I have tried to incorporate an "AS", but I get errors when I try this.



I am familiar, but new to SQL statements and I would like this to return a field so I can use this value in the report. What do I need to do?



Thanks for the information.

View 1 Replies View Related

How To Identify A Dialog As Encrypted?

Oct 3, 2006

I am trying to lookup a dialog from conversation_endpoints, however if a dialog was created with the encryption setting to ON and thereis no master Key in the database then the record put in the conversation_endpoints is the same as one without encryption.

How can I distinguish between the one requested with no ecryption and requested with encryption but setup with none due to the lack of a key?

View 2 Replies View Related

Identify Queue That Activated Sp ?

Sep 10, 2006

Hi there

I have multiple queues with the same activated stored procedure (for various reasons we are trying this scenario).

My biggest obsticle is i cannot figure out a way to determine with the activated sp which queue caused it to activate.

Basically i need to make the sp dynamic, so that no matter which queue activated the sp the sp can determine the queue name and use that dynamically to do the receive command from the right queue.

I am sure it is possible since sys.dm_broker_activated_tasks shows how many sp's are activated by each queue, however the sp name is the same for all queues so that does not help me.

How do i determine within an activated sp which queue caused it to activate?

Thanx

View 1 Replies View Related

Union All - Identify Input

Jul 11, 2007

Hello,

I have a Union All component with 7 inputs (and it will grow to 13 inputs shortly). Is there any way to correlate each of the €œUnion All Input X€? with actual inputs?
When I get an incompatible datatype message for one of the input, how do I know which input stream to pick from?

Is this a silly question or it€™s a feature request?

Kapil Kelaiya

View 3 Replies View Related







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