DateTime calculation

Question

DateTime calculation

tmmalaquias

SSC Veteran

Points: 265
More actions
June 20, 2008 at 4:55 am

#187384

hep me whit this, please,
Cod_User Date Telefone User_Name
10 2001-01-01 911111 Paul
20 2003-01-01 911111 Lorena
I have to find the final date of Paul and ensure that the costs of calls are assigned a person in the period in which the phone the person belongs

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mark Beringer-730383 SSCommitted Points: 1824 More actions · Answer 1

Sounds like you need to write a query that groups by user and selects MAX date and also joins to a cost table. Which table are the costs coming from? Can you give more info about your table structure?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

We can help... just not enough information in you post to help... Please see, read, understand, and use the methods for posting found in the URL listed in my signature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tmmalaquias SSC Veteran Points: 265 More actions · Answer 3

Hello,

the situation is, for example: I have now a number of phone that already belonged to another person, but I want to define the periods (dates) in which the phone was with someone else before me, that is the start date is the date that I was given the phone and end date is the date when the phone is assigned to another person, what I want is to create a column from the end date corresponding to begin on the person you are using or was the use?

I have a dimension that I have the code of the user; beginning date (date of award of the phone; user name

before me, on the phone 999999 already been with another person in 2000 and 2002 was attributed to another person and in 2008 is with me,

want to create a column (end date) in my size with my end date is the date when the phone is assigned to another person

for example: (same phone)

cod_user startdate phone Name

10 2000-01-01 999999 Paul

20 2002-01-01 999999 Mike

30 2008-01-01 999999 Michael

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Got it... I'll be back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tmmalaquias SSC Veteran Points: 265 More actions · Answer 5

Hello,

the situation is, for example: I have now a number of phone that already belonged to another person, but I want to define the periods (dates) in which the phone was with someone else before me, that is the start date is the date that I was given the phone and end date is the date when the phone is assigned to another person, what I want is to create a column from the end date corresponding to begin on the person you are using or was the use?

I have a dimension that I have the code of the user; beginning date (date of award of the phone; user name

before me, on the phone 999999 already been with another person in 2000 and 2002 was attributed to another person and in 2008 is with me,

want to create a column (end date) in my size with my end date is the date when the phone is assigned to another person

for example: (same phone)

cod_user startdate phone Name

10 2000-01-01 999999 Paul

20 2002-01-01 999999 Mike

30 2008-01-01 999999 Michael

i need a script to do that

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Ok... since you're new, I'd like you to take a peek at the article in the Link in my signature... it'll help you get better answers quicker in the future. 😉

Here's some sample code including your sample data in the format folks like me would like to see it in... basically, we answer lot's of posts and anything you can do to save us some time is a big help... especially since we do this stuff out of the kindness of our big ol' hearts... 😀 As, usual, I put the explanation in the comments in the code.

--===== Create and populate a test table to

-- simulate the original table.

CREATE TABLE #OriginalTable

(

cod_user INT NOT NULL,

startdate DATETIME NOT NULL,

phone VARCHAR(10) NOT NULL,

Name VARCHAR(20) NOT NULL

)

INSERT INTO #OriginalTable

(cod_user,startdate,phone,Name)

SELECT '10','2000-01-01','999999','Paul' UNION ALL

SELECT '20','2002-01-01','999999','Mike' UNION ALL

SELECT '30','2008-01-01','999999','Michael'

--===== Copy the data into a new table with a new

-- EndDate column

SELECT ISNULL(Phone,' ') AS Phone,

ISNULL(StartDate,0) AS StartDate,

CAST(NULL AS DATETIME) AS EndDate,

Cod_User,

Name

INTO #NewTable

FROM #OriginalTable

ORDER BY Phone,StartDate

--===== Create a clustered index in the order we need

ALTER TABLE #NewTable

ADD PRIMARY KEY CLUSTERED (Phone,StartDate DESC) WITH FILLFACTOR = 100

--===== Show the original values in the new table

SELECT * FROM #NewTable

--===== Declare and preset some necessary variables

-- to keep track of the values from the previous row

DECLARE @PrevPhone VARCHAR(10)

DECLARE @PrevStartDate DATETIME

DECLARE @PrevEndDate DATETIME

SET @PrevPhone = '-1'

SET @PrevStartDate = -1

--===== Do a "procedureal" update

-- This only works because of the brand new clustered index

UPDATE #NewTable

SET @PrevEndDate = EndDate = CASE WHEN Phone = @PrevPhone THEN @PrevStartDate ELSE NULL END,

@PrevPhone = Phone,

@PrevStartDate = StartDate

FROM #NewTable

--===== Show the new endate values in the new table

SELECT * FROM #NewTable

--===== Clean up after our experiment

DROP TABLE #OriginalTable,#NewTable

For more information on the technique used on the code above, please see the following article...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Mark Beringer (6/20/2008)
Sounds like you need to write a query that groups by user and selects MAX date and also joins to a cost table. Which table are the costs coming from? Can you give more info about your table structure?

Hi Mark,

Heh... Just an FYI... being in telecom myself, I've seen this problem many times. This is pretty much a standard telephone billing problem... someone wrote a system and they forgot to put EndDates in for some customers/services, the phone number gets reassigned to a new customer, and they don't know who to bill what for... the fix is to calculate the correct EndDates and then the billing system suddenly starts doing things right for those customers or services. In other words, fix the EndDates, and the "costs" will suddenly take care of themselves. Op was just saying the EndDates were needed in order for the costs to take care of themselves... OP doesn't actually want us to fix the costs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tmmalaquias SSC Veteran Points: 265 More actions · Answer 8

tmmalaquias

SSC Veteran

Points: 265

June 24, 2008 at 2:28 am

#833181

Hi, thanks for the help 😉