SQL Server 2012 :: Recurrences - Retrieve Only Rows Where Item Not Change For Given Value
Sep 16, 2015
I have a table with a datetime and an Item column.
I want to retrieve only the rows where item didn't change for a given value.
In the example below, given the value of 5 I only want the rows starting at 19:14:50 to 19:26:06.
Dateteime Item
2015-06-05 19:05:03.0002
2015-06-05 19:08:31.0002
2015-06-05 19:14:50.0001
2015-06-05 19:19:33.0001
2015-06-05 19:20:46.0001
[Code] ....
View 9 Replies
ADVERTISEMENT
Aug 29, 2014
I need to extract a price from a package solution table that was current on a certain date.
Columns are:
Product_Reference int,
Change_Date int, -- Note that this is in yyyymmdd format
Price decimal
So for one items you would get
Product_ReferenceChange_DatePrice
100014200401281.59
100014200605131.75
100014200802121.99
100014200906252.35
100014201002242.50
100014201107151.10
100014201205151.15
The challenge is to return the price of the item on 20070906. In this example 1.75.
I've tried joining it to itself
SELECT DISTINCT p1.[Product_Reference]
,p1.[Change_Date]
,p1.[Price]
FROM PHistory p1
INNER JOIN PHistory p2
ON p1.Product_Reference = p2.Product_Reference
WHERE p1.Product_Reference = 100014
AND p1.Change_Date >= 20070906
and p2.Change_Date < 20070906
But it returns the price above and below that date.
View 3 Replies
View Related
Jun 18, 2015
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement
2015-06-26 14:00:00.000 6 to 4
2015-06-26 14:00:00.000 11 to 7
2015-06-26 15:30:00.000 9 to 6
2015-06-26 21:00:00.000 7 to 3
2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
View 9 Replies
View Related
Apr 14, 2014
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
[Code] .....
View 3 Replies
View Related
Aug 6, 2015
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
[code]....
View 2 Replies
View Related
Feb 2, 2007
I've set up DB mail and sent a test e-mail and that comes through fine.
I set up an Operator with email Name: DWhelpton@k-and-s.com;MWeaver@k-and-s.com
I created a job and set up the notifications to e-mail the operator on failure.
When the job runs and fails, I do not get an e-mail and I get the following exception in the db mail log:
Date 2/2/2007 8:35:00 AM
Log Database Mail (Database Mail Log)
Log ID 402
Process ID 3936
Last Modified 2/2/2007 8:35:00 AM
Last Modified By NT AUTHORITYSYSTEM
Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Could not retrieve item from the queue.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ICommand CreateSendMailCommand(Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DBSession)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
What step am I missing?
View 1 Replies
View Related
Apr 2, 2015
Is there anyway we could retrieve the job history for all the jobs in all the sql server using Central Management Server?
View 0 Replies
View Related
Oct 21, 2015
How can we get the result set as TESTING1,TESTING2 from below
DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $
View 6 Replies
View Related
Jul 17, 2015
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
View 5 Replies
View Related
Sep 28, 2000
Hi, can someone plz give me an idea of how to proceed with this...
I've got a server side cursor that retrieves a list of customers from a sql server table which match a specific criteria (those who have had orders in the yr 2000)
For each customer, I need to retrieve a list of 5 top items purchased along with dollars spent on each item by that customer.
I've tried to do this in a loop that uses a counter but probably my syntax was off and I'm not sure that this is better than a correlated join? Can someone show me a temlate of the appropriate syntax to use for this operation
I appreciate suggestions, thanks again.
Irene
View 2 Replies
View Related
Aug 26, 2015
My data look like this:
ITEMID DESC QTY PRICE
134 ITEM1 4 $200
134 ITEM1 1 $200
134 ITEM1 1 $200
how do I write SQL stmt to add the quantity if the itemid is the same?
The result should be:
ITEMID DESC QTY PRICE
134 ITEM1 6 $200
View 2 Replies
View Related
May 12, 2015
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies
View Related
May 12, 2015
I would like to get extract the first row of some time based data where any of the other values changes. For example, in the following 10 rows
ABDTE
112015-01-01
112015-01-02
112015-01-03
212015-01-04
112015-01-05
1NULL2015-01-06
1NULL2015-01-07
212015-01-08
212015-01-09
222015-01-10
I want to remove the rows where A and B are the same as on the previous row. So rows 2, 3, 7 and 9 should be eliminated. Note that A and B can have the same values multiple times, just not in succession in the extract. I've tried ranking but I can't figure out how to keep it from lumping all the values of A and B in the same group. The following incorrectly eliminates rows 5 and 8:
;with data as (
select 1 as A, 1 as B, '2015-01-01' as DTE union
select 1 as A, 1 as B, '2015-01-02' as DTE union
select 1 as A, 1 as B, '2015-01-03' as DTE union
select 2 as A, 1 as B, '2015-01-04' as DTE union
[Code] .....
Of course the real data has many columns and multiple data types that can have nulls. I just want get the row when anything changes. Is there a slick way to do this in SQL?
View 9 Replies
View Related
Aug 14, 2014
What issues will I encounter if I change the MSSQLSERVER password?
View 2 Replies
View Related
Dec 5, 2014
I create a db by following code. Now I want to change name of FILEGROUP. How can I do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
FILENAME = 'c:datamy1')
[Code] .....
View 1 Replies
View Related
Dec 5, 2014
I create a db by following code. now i want to change name of FILEGROUP. how can i do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
[Code] .....
View 1 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Aug 21, 2015
I like to backup the stored procedures' code used in my databases. So I created this Script:
/*
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NOT NULL DROP TABLE [Monitor].[dbo].[Procedurecode];
*/
DECLARE
@db nvarchar(50),
@strSQL nvarchar (100)
IF CURSOR_STATUS('global','cur1')>=-1 BEGIN DEALLOCATE cur1 END
[code]....
Problem is (and I seem not to be the only one with tis) described here:
"If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running." There is nothing magical in SQL server that knows you intend a series of dynamic sql commands to be part of a single job running in a single context. You have to build the entire string for the job you want to execute."
[URL]
So I only get the SPs of the current database.
View 5 Replies
View Related
Oct 18, 2007
Hi
I have a table with a column 'value' as nvarchar
I want to select some rows from this table that the value of 'value' can be converted to decimal ...
it's possible that value of 'Value' contains some other characters other than digits .
View 10 Replies
View Related
Mar 3, 2015
I am in process to develop TSql code to identify change in data.
I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.
But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?
select HASHBYTES('SHA','121'+'34'), HASHBYTES('SHA','12'+'134'),BINARY_CHECKSUM('121','34'),BINARY_CHECKSUM('12','134');
View 2 Replies
View Related
Dec 6, 2013
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies
View Related
May 26, 2008
Hello..
Can any one you help me in retrieving the rows which immediately after a ID 9.
i.e, My condition is to retrieve the Record which immediately follows the event 9.
The retrieved record event should be in (24,25,26)
Table structure is
---------------------------------------------
MatchID SegmentID Event Player1 Player1ClubID
---------------------------------------------
1 0 9 1 88
1 0 24 1 89
1 0 10 1 88
1 0 9 1 89
1 0 25 1 88
1 0 10 1 88
1 0 10 1 89
1 0 10 1 89
----------------------------------------------
My out put should have
---------------------------------------------
MatchID SegmentID Event Player1 Player1ClubID
---------------------------------------------
1 0 24 1 89
1 0 25 1 88
---------------------------------------------
How to get this,
Can any one help me.
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
View 5 Replies
View Related
May 14, 2006
I have a table MyTable that contains two fields MyData and CreationTime. I would like to write a query
SELECT MyData FROM MyTable
WHERE CreationTime < less than one week from now
Does anyone know the real syntax for that?
Thanks in advance,
Joannès
View 3 Replies
View Related
Jan 7, 2008
Hello all,
I've just deleted some rows by error in one of my tables. I'd like to find out a quick way to retrieve them.
I'm using MSSQL server 2000, version 8.00.194
Thank you in advance
View 9 Replies
View Related
Oct 17, 2014
I keep getting requests to increase the width of a varchar colum every now and then.
I want to ask if its perfectly ok when you have active users connecting to the application to do this?
View 7 Replies
View Related
Mar 6, 2007
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
View 3 Replies
View Related
Jul 18, 2012
I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.
SELECT DISTINCT
ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER,
PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER
ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN
FROM PAYROLLYEAREND ) s
WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'
In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows. The where clause is not part of overall query. Just isolating on one ID.
View 2 Replies
View Related
Aug 31, 2015
I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
View 2 Replies
View Related
Sep 10, 2007
Hi,
I am new at SQL hopefully this would be a rather easy question for you guys to help me out with.
I have a table called PRODUCT with the following fields:
a. Product Name
b. Product Dept.
c. Purchase Date.
I would like to run a query to obtain all rows tha has more than one purchases on any particular day.
Any replies would be great.
View 1 Replies
View Related
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 Replies
View Related
Jan 10, 2014
Is it possible to retrieve Distinct rows from this Select Statement?
I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.
Code:
SELECT dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated,
CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
FROM dbo.Customers INNER JOIN
dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))
View 8 Replies
View Related
May 27, 2015
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 21, @on
[code]...
View 6 Replies
View Related
Jul 12, 2007
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies
View Related
