Transact SQL :: DATEDIFF Does Not Have To Calculated Numerous Times?
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
ADVERTISEMENT
May 9, 2015
I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user only if he makes the 3 incorrect entries within 30 minutes.
I created a field named "FirstEntryTime" of type datetime that saves the date of the first incorrect entry. I tried to make an if statement:
if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30)
Begin
Update myTable set ...
End
View 6 Replies
View Related
Sep 12, 2015
When running a query such as this on a database view:
select count(*) from WWALMDB.dbo.v_AlarmEventHistory2
SQL throws the following error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Oddly on a Table the count(*) function works.
Is there a limit on views or the count(*) function that I am not aware of?
View 3 Replies
View Related
Sep 21, 2015
I am trying to JOIN table on datetime column truncated to full minutes with other table, where I store time intervals (in order to have all minutes in result even when there was no event in main data table).
SELECT
dateadd(minute, datediff(minute, 0, StartTime), 0) as StartTimeMinute
,ApplicationName
,COUNT(*) as Requests
FROM dbo.Profiler as p INNER JOIN dbo.MinuteIntervals as i ON i.TIMEVALUE = p.StartTimeMinute
WHERE EventClass IN (10,12)
GROUP BY dateadd(minute, datediff(minute, 0, StartTime), 0), ApplicationName
ORDER BY StartTimeMinute
How to join on p.StartTimeMinute? I do understand why this doesn't work, as p.StartTimeMinute is calculated in my query, so JOIN cannot find it in original p table.
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
Jul 18, 2015
use Northwind
Go
select
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date
, dbo.Customers.CustomerID
, dbo.Customers.CompanyName
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
View 9 Replies
View Related
Jun 14, 2015
I am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events).I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
View 4 Replies
View Related
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
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
Nov 4, 2015
Setup:
Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit
SQL Server 2014 Express - 32 Bit
Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.
I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.
My PO table
CREATE TABLE [dbo].[PO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Quote_Number] [varchar](max) NULL,
[Customer] [varchar](max) NULL,
[CustomerPO] [varchar](max) NULL,
[PO_Received_Date] [datetime] NULL,
[Total_PO_Value] [decimal](18, 2) NULL,
[Code] ....
View 10 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
Jan 5, 2002
My Web Host does not provide administrative privilages to the SQL server I have access to. I would like to delete tens of thousands of records from two of my tables without writing to the Transaction Log. Is what I'm trying to do is delete these records quickly without utilizing any of the alotted space my web host has set aside for my transaction log (they give me 50 mb and I go way over that when I run a DELETE statement)
What is the best way to do this?
View 3 Replies
View Related
Jan 11, 2008
Is there a way to determine what application or who might be creating numerous tables in the tempdb. I have one that has grown yet the sp_spaceused command shows the majority of the database empty.
I need to determine where they are coming from and how I can delete them short of stopping and starting the SQL services before it uses up all of my HD.
View 3 Replies
View Related
Feb 20, 2007
Hello all
Im sure this is something minor that I am overlooking but for some reason when I go to debug my ssis package all these windows keep opening( callstack, watch1, watch2,watch3,breakpoints, etc) . I cant seem to find out how to disable these -- any suggestions? I dont remember actually setting these to run
thanks
kam
View 4 Replies
View Related
May 25, 2006
Hi,
Every 10 minutes, I get and information application event log of type
ID:17137
Info: Starting up database 'ReportServer$SQLExpressTempDB'
This is a brand installation of the Express SP1 advanced package and the reporting service has not been yet used.
Thanks
View 16 Replies
View Related
Sep 7, 2006
Hi,
Can anyone point me in the direction of a script that incorporates sp_addlogin, which allows for adding 200 sql authentication logins from an excel spreadsheet, or a temporary sql table with the id info, containing the username, password, and def db?
I'm trying to avoid adding each new login one by one. EXEC sp_addlogin 'username', 'password', 'default database'Thanks, BPH
View 3 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
Nov 2, 2006
Hello, Everyone: Greetings!
I am new to Sql Server [Express version] and am not even sure I'm making the right choice. So here I am, seeking advice.
My database needs are nothing sophisticated. They just involve:
(a) create tens of thousands of separate data files each under a unique file name of up to 8 characters, with file names read in from a pre-determined file name list.
(b) store/insert VOLUMINOUS numerical data into each of the data files, with the data indexed by date&time, plus maybe one or two additional character or string fields.
(c) for each data file, retrieve a subset of its data, perform extensive numerical calculations, and then store the results in one or more separate corresponding files, e.g. if a file name in (b) is F12345, (c) creates F12345R1, F12345R2, F12345R3, etc. which stores different sets of calculated results.
Thus, this is purely a console application, doing a batch job, and requiring no graphical user interface. Both automation and speed are important here, due to the large number of data files that must be created and/or updated, and the very extensive numerical calculations on the data.
The goal is to automate the daily or weekly creation of each of the tens of thousands of Sql Server database files, insert fresh data (read in from a fresh ASCII file) into each file, numerically process the data and then store the results in one or more separate, corresponding result data files, with all the steps automated and without need for GUI. Once coding is done, the entire data processing session is expected to run for many hours, or even days, in an automated manner, and without human intervention.
What would be the most efficient way of doing this under Visual Basic Express (which is what I'm learning to use) by directly calling Sql Server Express without having to go through GUI to create database files? What is the proper interface utility or library to use to enable direct database function calls without the need to learn SQL language? Is Visual Basic and/or Sql Server even good choices for what I want to do? I want to be able to call the basic, simple database functions directly and simply from program code in a non-GUI, non-interactive manner for the tens of thousands of separate data files that will be used.
I really miss the good old days when one can do a straightforward batch job via a console application, with simple, direct calls to create new data files, insert and index fresh data, retrieve any subset of data to do extensive calculations, create new files to store the results, etc. all under automated program control and iterating through unlimited number of data files, until the job is finished, all without human intervention during processing.
Or am I missing something because all this can still be done simply and easily under VB and Sql Server? I've several books here about Visual Basic 2005 and Visual Basic 2005 Express, all showing how to create a database via a GUI utility. That's fine if one needs to create just one or two databases, but not hundreds, or even tens of thousands (as in my case) of them on the fly.
So, I am looking for the simplest and most direct database interface that will allow me to do the above under VB program code alone, and easily. For something as simple as I have described above, I don't think I should have to learn the SQL language or manually create each database file.
As you can see, I just want to get some heavy duty numerical processing job done over tens of thousands of data files as simply and efficiently as possible, and with as little fanciful detour as possible. So, ironically, I am trying to use Visual Basic without being cluttered by having to learn its "Visual" aspects, yet GUI is what most VB books devote to or emphasize heavily. Similarly, I would much rather use simple, "lean and mean", direct database function calls than having to learn a new vocabulary of "English-like" SQL language.
Yes, I'm not used to this tedious detour of learning the GUI aspect of VB, or learning the Structured Query Language of Sql Server, just to try to do something simple that I need to do in batch mode via a console application.
Are there any good books or other helpful URLs that will help a guy like me? Am I even using the wrong language and the wrong database to do what I want to do? What are the better alternatives, if any? Any advice, experience and pointers on any of the above issues raised would be very much appreciated. Thank you!
Regards,
ConsoleApp
View 1 Replies
View Related
Apr 15, 2015
SQL 2008
I have a table that has company id, attachment file name, folderexists columns.
First what I need to do is create a series of folder or directories on a networked server using the company id as the folder name where the folder name does not already exist.
Second I need to move files based on attachment file name and company id to the proper folder.
For those who want to know, this is a remediation project because of a bug in our application.
The application is supposed to created the folder based on company id and then put the attachment in that folder.
View 9 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
Dec 5, 2005
If I wanted to get everyone DOB who is over 18 how would I do that? I am currently trying something like this, but no luck...
Declare @todays_date datetime
Select from person
CASE
WHEN dateadd(year, datediff (year, Date_Of_Birth, @Todays_Date), Date_Of_Birth) > @Todays_Date -- Date of Birth check
THEN datediff (year, Date_Of_Birth, @Todays_Date) - 1
ELSE datediff (year, Date_Of_Birth, @Todays_Date)
END
>= 18
View 3 Replies
View Related
Jul 27, 2004
I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that????
tia,
e3witt
View 4 Replies
View Related
Mar 9, 2004
Hello,
My string is:
SELECT weight FROM progress WHERE dateInput = (SELECT MAX(dateInput) FROM progress) AND memberID = 1
The problem is that the MAX dateInput doesn't belongs to memberID 1. It belongs to memberID 2. What I want is that I wanna choose the MAX Date of memberID 1. I thought of maybe using datadiff function. But I don't know how to make the datediff statement. Maybe I can write the datediff statement whereby the least datediff between the dateInput and getdate() will be the row I want. I really appreciate the person that helps me this problem... Thanks 1st of all!
View 2 Replies
View Related
Apr 15, 2004
I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.
I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.
View 7 Replies
View Related
Apr 25, 2008
I need to get a listing of all persons who are atleast 18 years of age. A date of birth field in the database is in this format:
4/25/2008 12:00:00 AM
My solution would be where the difference between the current date and the dob is >= 18.
I tried...
select * from table where datediff(yy,dob,getdate)) >= 18.
But this only seems to subtract the years and ignore the days/months, which I need. Could anyone provide the syntax I need?
Help is appreciated. Thanks.
View 3 Replies
View Related
Jun 24, 2008
Hi there i am using the datediff funtion but it does not seem to be inclusive of the two dates eg
SELECT DATEDIFF(day, s_Date, e_date) AS NumberOfDays,*
FROM weekendtest
i know i could do
SELECT DATEDIFF(day, s_Date, e_date)+1 AS NumberOfDays,*
FROM weekendtest
but i was just wondering if there some other function i should use that would be more appropriate
View 3 Replies
View Related
Oct 21, 2004
Hi
I've a problem!!!
i want to use datediff thats no problem. But the first parameter has to
be parameterised. What datatype do i declare it as. i declare it as varchar
it returns me an error : Invalid parameter 1 specified for datediff.
i'm doing this in a stored procedure.
Is there a way out or do i've to use the good old 'IF' or 'Case' ?
regards
pradeep
View 1 Replies
View Related
Oct 19, 2006
Hi,
I have a Table Where I have Two Date columns and I want to find the Differnce in the 3rd column.ie DateReceived - AsOnDate = NoofDays.I tried this code
SELECT SRNO,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays,
sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED,ASONDATE, GETDATE())
FROM Details
Plz help me...
Thanks
View 7 Replies
View Related
Jan 27, 2008
SELECT DATEDIFF(mm,0,'01/25/2008')
Returns 1296 what does it represent...
Thanks
View 2 Replies
View Related
