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
ADVERTISEMENT
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
View Related
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
Aug 3, 2015
SQL 2014...In SSDT, as I'm developing my tabular model I often add new columns to the underlying table/view that feeds my model. When I go into Table/Table Properties, select the new column I've added then hit OK it spins and spins then times out. My model has just over 1 million records. The only way I can get it to work is to delete the table and then add it in again.
View 2 Replies
View Related
Aug 6, 2015
I have a cube with some dimensions (dim1,dim2,dim3).I want to add another dimension (dim4) based on dim1 but more restricted.Eg. Dim1 is a product dimension, and I will create Dim4 from a subset of Dim1 (only few and predetermined products).
I will create a view (or a named query) with the appropriate where clause, create a dimension on this view/namedQ, then add on the existant cube and link it on dimension usage. Great. But in this way in the cube I haven't all fact data but only data for the product of Dim4! (Dim1 is still present).
eg: in fact data I have a total of 100$ of sales for all the products, if I link the Dim4 then the sales amount is 40$, that is the value for the product in Dim4 not for all product (I can't set the filter for Dim1/Dim4 in excel). I haven't found any way for get the 100$.
I try with two Date dimension: the first from 01/01/2010 to 31/12/2015 and the second from 01/01/2015 to 31/12/2015. If I link both I see only data for the second period (only 2015).
it seems that the more restrictive dimension trumps.What's wrong? There are other ways to get what I want? I've tried the property "DependOnDimension" but without results.
I won't publish the attribute used in the WHERE clause on the dimension.
BIDS 2008 and SQL Server 2008R2
View 2 Replies
View Related
Apr 14, 2015
I am recently encountering proble with SSAS cube,In a day cube is going to offline for several time and unable to browse it and after some time automatically cube is getting online.I am unable to figure it out what is happening.
FYIP..For every 15 Min cube will be Proccessed Full.
View 8 Replies
View Related
Apr 14, 2015
How do you cast 0 as DateTime? Is this possible to do.I am using UNION to combine two tables. I get an error when doing so...
Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with date
SELECT TOP (100) PERCENT
Opportunity_Name
,Status
,Closed_Status
[code]....
I understand what is causing the error. In the top select statement both Closed_Status AND Expected_Close_Date are datetime values. But in the bottom statement they are int. Â
I understand what is causing the error. In the top select statement both Closed_Status AND Expected_Close_Date are datetime values. In the bottom statement they are int.  So I tried CASTing them as datetime, that didn’t work and I get this error.<o:p></o:p> Conversion failed when converting the varchar value 'Open' to data type int.Is there a way to CAST 0 as a DateTime
View 6 Replies
View Related
Jul 22, 2015
Casting 0 to SMALLDATETIME or DATETIME is allowed in T-SQL which produces to 1900-01-01. However, you can't cast 0 to DATE or DATETIME2. Why is that?
View 15 Replies
View Related
Aug 10, 2015
I am trying to automate a load of insert cmd but some of them contain date times so i am trying to insert the following but keep getting
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
What is wrong with the cmd??
INSERT INTO client VALUES (100, NULL, NULL, NULL, NULL, 1, NULL, 0, 0, 0, 0, 0, NULL,Cast( '2013-03-11 10:54:46.529' as datetime))
View 11 Replies
View Related
Nov 6, 2015
I have a server on SQL Server 2008 R2.
I have the following code:
SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROMÂ PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID
However when I run it I get a message:
View 7 Replies
View Related
May 26, 2015
Following SQL statement attempts to convert a bigint to dateÂ
SELECT CAST (DATEADD(SECOND, SEM_AGENT.LAST_UPDATE_TIME /1000 + 8*60*60, '19700101') AS VARCHAR(50)) as Last_Checkin ........
But no matter what I try, the output isÂ
May 27 2015 Â 1:18AM
and I wish the output to be
May 27 2015
I also tried
SELECTÂ CAST (DATEADD(SECOND, SEM_AGENT.LAST_UPDATE_TIME /1000 + 8*60*60, 'Jan 1, 1970') AS VARCHAR(50)) as Last_Checkin ....
but same result
View 6 Replies
View Related
Aug 27, 2015
I found this to work:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
It Returns:
uri         Â
evFieldUri         Â
evFieldVal         Â
(No column name)
224016Â 3267Â Â Â Â Â Â
+000089243829 89243829
224019Â 2717Â Â Â Â Â Â
+000089243825 89243825
224472Â 3333Â Â Â Â Â Â
+000000000000000000000017Â Â Â Â 17
225052Â 3267Â Â Â Â Â Â
+000089243829 89243829
225055Â 2717Â Â Â Â Â Â
+000089243825 89243825
So, then I went back to:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
And it returns this error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
So, I tried again, and this worked…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT),
ISNUMERIC(evFieldVal)
FROM TSEXFIELDV WHERE URI
> 0 AND evFieldUri
IN ( SELECT URI
FROM TSEXFIELD WHERE exFieldFormat
IN (1,11))
I logged out and came back and tried again, and it still worked. So then I tried…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
WHERE URI
> 0
[Code] ...
And it fails.
View 5 Replies
View Related
Nov 9, 2015
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and  if the column has a string "d" then replace with "D" . I can  write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz
after the update: BcDxyz
Following is the sample data
declare @t1 table
(
  c1 varchar(5),
  c2 varchar(10),
  c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')
[Code] ....
View 5 Replies
View Related
May 13, 2015
I have created approximately 200lines of multiline table function. getting table variable values as what I need satisfying all our business rules.
my question : is it okay in performance wise using loop i executed this function 100000 times and it took around 16mins.
View 10 Replies
View Related
Sep 4, 2015
What is a more efficient way of doing the following such that DATEDIFF() does not have to calculated numerous times?
CASE
 WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 0 THEN '<= 0D'
 WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 0 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 7 THEN '> 0D AND <= 7D'
 WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 7 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 30 THEN '> 7D AND <= 30D'
Â
[code]....
View 5 Replies
View Related
Jun 12, 2015
I want  the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in jobÂ
Â
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
 SELECT  Â
--DISTINCT Â
p.SPID,
p.LogiName,
p.HostName, Â Â
[Code] ....
View 8 Replies
View Related
May 28, 2015
I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?
UPDATE xmlTable
SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue)
FROM xmlTable
JOIN newData
ON xmlTable.ID = newData.fkXmlTableID
View 2 Replies
View Related
Jul 10, 2015
How can I write it in SQL? I have a table that column  A   has two values of  Product  and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4 more rows, and add column b with values from 1 to 5 for each rows ( counter).
View 3 Replies
View Related
Oct 16, 2015
I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.
View 2 Replies
View Related
Jun 22, 2015
How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.
Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50
View 8 Replies
View Related
Aug 11, 2015
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.Â
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.Â
View 2 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT Â
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
  [PSDCode]
, PV.ValuationEffectiveDate
  [EffectiveDate]
, PV.PropertyListAlterationDate
  [ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple timesÂ
View 11 Replies
View Related
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
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
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
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
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
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
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
Nov 27, 2015
I have a table that has a DATE field named. AccountingDate that is in the format YYYY-MM-DD. It's not a VARCHAR field. I simply want to convert this date field into the format MM/DD/YYYY and call it New_Accounting_Date.
I've played with various combinations of CAST & CONVERT but haven't been able to get it to work.
Below is my latest effort which returns the error:
Incorrect syntax near the keyword 'as'
What code would work to return a MM/DD/YYYY value for New_Accounting_Date?
Select GLBATCH.AccountingDate,
convert(GLBATCH.AccountingDate as date),101) AS New_Accounting_Date
from GLBATCH
View 11 Replies
View Related
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
Mar 5, 2015
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
View 10 Replies
View Related
May 13, 2014
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
View 2 Replies
View Related
