Hi I Need Help About Lag/lead

Apr 18, 2008



i want to create a new table from one table like this

TIME_ID SALES LAG1 LEAD1
--------- ---------- ---------- ----------
10-OCT-00 238,479 23,183
11-OCT-00 23,183 238,479 24,616
12-OCT-00 24,616 23,183 76,516
13-OCT-00 76,516 24,616 29,795
14-OCT-00 29,795 76,516

i think i use some functions in my stored procedure can anyone help me about this stuation Thanks!!

View 3 Replies


ADVERTISEMENT

PAGEIOLATCH Is A Lead Blocker

Nov 30, 2005

Hi Guru,After spening quite sometimes to watch my box, I've seen PAGEIOLATCH isa lead blocker in my SQL Server 2000 server. Below is the detailed:SPID lastwaittype waitresource blocked status cmd57 LCK_M_S KEY: 7:963690681:8 65 sleeping execute65 PAGEIOLATCH_SH 7:1:217904 0 sleeping selectI thought, latching should be very short-term synchronization. Fromsystemprocess table, I saw the latch waited in a minute sleepingwithout doing any work.My database is about 23GB and more than 5000 tables. The RAID subsystemis RAID1 with 1 disk mapped to C and D logically. Data files and tempdbfiles are located in one location. Tranlog file and log backup filesare located in the same location with different disk spindle.Currently, we are experiencing very slowness and IO bound. I'm ready torebuild the server by putting the RAID10 and 1 and distributingmultiple data files to different RAID10 and tempdb and log files toRAID1.Other than this, how to minimize the IO latch contention?Thanks so much,Silaphet,

View 1 Replies View Related

Tool To Trace Lead Blocker

Jun 5, 2006

Hi guys,

Do you have any stored procs or utility to track down the lead blocker as well as the blocked processes on SQL Server 2000? Similar to a tree structure with the lead blocker on top followed by the processes being blocked by the lead blocker.

Thank you very much.

View 3 Replies View Related

Can Migration Lead To Index Problems?

Mar 5, 2007

Starting with a SQL Server 2000 database, my company built a new server with SQL Server 2005 and restored the SS2000 database from backups. We're now seeing some extreme issues where queries that took 30 seconds now take 3 minutes. Would this make you suspect index issues? Any recommendations on diagnosing and fixing this would be appreciated.

View 4 Replies View Related

All Roads Lead To Rome But Which One Is Most Desirable?

Jul 29, 2005

-- Business Rule, first name, middle name and last name can all be null-- ddlcreate table #cat (catID char(8) primary key, first_name varchar(15)null, middle_name varchar(2) null, last_name varchar(15) null)-- dml, populate sample datainsert into #catvalues ('Black123','ghost','','bigger')insert into #catvalues ('Arab0123','Hama','','Abbas')insert into #catvalues ('Mixed001','',null,null)insert into #catvalues ('Mixed002',null,null,null)insert into #catvalues ('Mixed003',null,'','Smith')insert into #catvalues ('White123','','','Talley')insert into #catvalues ('Yello123','Nick','H','Pisa')-- dml, name concatenation, get all or anyselect (first_name + ' ' + middle_name + ' ' + last_name) as namefrom #cat-- the above does not meet with requirement-- option 1select (IsNull(first_name,'') + ' ' + Case Len(middle_name) when 0 then'' else IsNull((middle_name + ' '),'') end + IsNull(last_name,'')) asnamefrom #cat-- option 2select (IsNull(first_name,'') + ' ' +IsNull(NullIf(Coalesce((middle_name + ' '),''),''),'') +IsNull(last_name,'')) as namefrom #catq:both option 1 and option 2 produces same result, which one is moredesirable?TIA.

View 4 Replies View Related

Transact SQL :: Getting Delta Value With Lead Windows Functions?

Jul 1, 2015

I have a table with the next structure:

DECLARE @MaxCountHistogram TABLE 
 (
  MaxId   INT IDENTITY PRIMARY KEY NOT NULL, 
  PublicationId   INT NOT NULL,
  ProviderId      INT NOT NULL,
  DateLog  DATETIME NOT NULL,
  Amount        FLOAT NOT NULL
  )
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)

I need to take for each Publication and Provider  and getting the diferential between two different months, for example:

Period                        Delta Amount                     Provider      PublicationId
20150530                   10.2564                                     3 432
20150630                     3  Result of (13.2564- 10.2564 )        3                      432

View 4 Replies View Related

Using Northwind Database To Create A View (was Can You Lead On The Right Track...)

Jan 27, 2006

I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.

Here is my script:

use Northwind
GO

/*STEP 2, #1*/

/* does it exist, if so drop it */
if exist (select * from dbo.sysobjects
where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[OrdersToSpain]
GO

/* Create the View */
create view "OrdersToSpain" AS
SELECT
Orders.OrderID AS Order_ID,
Orders.CustomerID AS Customer_ID,
Orders.OrderDate AS Ordered_Date.
Orders.ShippedDate AS Shipped_Date,
Orders.ShipCountry AS DestinationSpain
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipCounty LIKE '%SPAIN%'
GO

Here are the errors I am getting:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7
Line 7: Incorrect syntax near '.'.

View 6 Replies View Related

Which Certifications Lead To A Position Involving Data Mining?

Jan 20, 2007

I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?

Thanks.

View 1 Replies View Related

SQL Server 2012 :: Incorporate RANK Or NTILE With LEAD Function?

Jan 9, 2014

We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:

if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go

-- Create temp tables for sample data

create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)

-- Insert some sample data

insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')

-- Use lead function to determine how many months are between

-- consecutive dates per account

; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval

where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.

-- Show the results

select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis

Results (this is not getting me what I'm looking for):

RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122

[code]....

This is what I'm trying to achieve:

RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114

[code]....

The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.

I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.

View 5 Replies View Related

SQL 2012 :: Using LEAD Function To Show Previous Month Difference?

Mar 25, 2014

I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?

This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.

I tried to do LEAD but with not success..

/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],

[code]....

View 2 Replies View Related







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