equal distribution of start and finish dates

Question. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls helpHi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls help|||Question. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita wrote:
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help
This will handle a two-month period, anything more than that is going to
be more complicated:
USE tempdb
GO
CREATE TABLE dbo.Test (
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
GO
CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
RETURNS INT
AS
BEGIN
RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
END
GO
SELECT
start_date,
finish_date,
cost,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4) END AS portion1,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4)) END AS portion2
FROM dbo.Test|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:

> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rate
d
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
>
>|||On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:

>Hi all
>I have got 3 columns in my table- start date,finish date and cost..in the
>following format...
>start_date finish_date cost
>12/12/2000 20/12/2000 $2000
>01/09/2000 12/10/2000 $400
>Now if the month and year of the start and finish date is same, the cost
>remains same...
>but if the month of the two dates are different, i have to distribute the
>cost between the two months by calculating the cost for the number of days
>for both of the months..
>but i am not able to figure out how?
>i am using sql 2005 ..
>my table has got about 1 million rows...
>pls help
Hi mita,
First, you need to create a table that holds all months in your
reporting period (or more). Something like this:
CREATE TABLE dbo.Months
(MonthStart datetime NOT NULL PRIMARY KEY,
MonthEnd datetime NOT NULL);
go
DECLARE @.TheMonth datetime;
SET @.TheMonth = '200000101'; -- Start at january 2000
WHILE @.TheMonth <= '20191231' -- End at december 2019
BEGIN;
INSERT INTO dbo.Months (MonthStart, MonthEnd)
VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
END;
The above is a one time operation, provided you never drop the table.
Don't forget to add some extra months to the table in a year or ten!
With this table in place, your query becomes something like this:
SELECT PeriodStart, PeriodEnd,
TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
/ DATEDIFF (day, start_date, finish_date) AS cost
FROM (SELECT CASE WHEN a.start_date > b.MonthStart
THEN a.start_date
ELSE b.MonthStart
END AS PeriodStart,
CASE WHEN a.finish_date < b.MonthEnd
THEN a.finish_date
ELSE b.MonthEnd
END AS PeriodEnd,
a.start_date, a.finish_date,
a.cost AS TotalCost
FROM dbo.MyTable AS a
INNER JOIN dbo.Months AS b
ON b.MonthStart <= a.finish_date
AND b.MonthStart >= a.start_date) AS d
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||hi hugo
i just ran this query for creating the months table which u suggested... i
am getting an error....
"Conversion failed when converting datetime from character string." what do
i do?
"Tracy McKibben" wrote:

> mita wrote:
> This will handle a two-month period, anything more than that is going to
> be more complicated:
> USE tempdb
> GO
> CREATE TABLE dbo.Test (
> start_date DATETIME NOT NULL,
> finish_date DATETIME NOT NULL,
> cost DECIMAL(18,3)
> )
> INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
> INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
> INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
> GO
> CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
> RETURNS INT
> AS
> BEGIN
> RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
> DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
> ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
> END
> GO
> SELECT
> start_date,
> finish_date,
> cost,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4) END AS portion1,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4)) END AS portion2
> FROM dbo.Test
>|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:

> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rate
d
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
>
>

Enterprise Manager

We have inherited a system with a Sybase DB and have been
asked to move it to SQL 7. We have converted the data and
the database with expected datatype problems and it seems
to be working. The application is a PowerBuilder 7
program that was using and ODBC and is now using OLE DB
for the SQL. In a one man environment it is great, move
it to the production machine and tell all 47 folks to sign
on, the Enterprise Manager quits responding, tables lock
all over the place and no one can do updates or inserts.
The tables have PK and FK with clustered indexes pretty
thoroughly placed. The machine is a NT 4.0 with 1GB of
RAM with a raid drive 20GB.
Any suggestions on what to do to increase performance on
this one?
TIA,
camiHello Cami !
First of all you must find out who is holding locks and what objects,
respectiv what actions are holding locks on the tables.
Jens Süßmeyer.
"Cami" <clawson@.informs.com> schrieb im Newsbeitrag
news:029801c36688$a4567220$a401280a@.phx.gbl...
> We have inherited a system with a Sybase DB and have been
> asked to move it to SQL 7. We have converted the data and
> the database with expected datatype problems and it seems
> to be working. The application is a PowerBuilder 7
> program that was using and ODBC and is now using OLE DB
> for the SQL. In a one man environment it is great, move
> it to the production machine and tell all 47 folks to sign
> on, the Enterprise Manager quits responding, tables lock
> all over the place and no one can do updates or inserts.
> The tables have PK and FK with clustered indexes pretty
> thoroughly placed. The machine is a NT 4.0 with 1GB of
> RAM with a raid drive 20GB.
> Any suggestions on what to do to increase performance on
> this one?
> TIA,
> cami
>|||Jens,
We have located the tables but it happens randomly on different tables and the most frustrating thing is the Enterprise Manager just dying while I'm trying to watch who is doing what and what is locking. I can do all of that in the QueryAnalyzer but what is locking up the EM?
tia,
cami
>--Original Message--
>Hello Cami !
>First of all you must find out who is holding locks and what objects,
>respectiv what actions are holding locks on the tables.
>Jens S=FC=DFmeyer.
>
>"Cami" <clawson@.informs.com> schrieb im Newsbeitrag
>news:029801c36688$a4567220$a401280a@.phx.gbl...
>> We have inherited a system with a Sybase DB and have been
>> asked to move it to SQL 7. We have converted the data and
>> the database with expected datatype problems and it seems
>> to be working. The application is a PowerBuilder 7
>> program that was using and ODBC and is now using OLE DB
>> for the SQL. In a one man environment it is great, move
>> it to the production machine and tell all 47 folks to sign
>> on, the Enterprise Manager quits responding, tables lock
>> all over the place and no one can do updates or inserts.
>> The tables have PK and FK with clustered indexes pretty
>> thoroughly placed. The machine is a NT 4.0 with 1GB of
>> RAM with a raid drive 20GB.
>> Any suggestions on what to do to increase performance on
>> this one?
>> TIA,
>> cami
>
>.
>|||I foresee running SQL profiler in your future. You will want to see if you
are having deadlocks and who/what is blocking who/what.
Here are a couple useful links
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp -
Detecting and Ending Deadlocks
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_00mr.asp -
Monitoring Server Performance and Activity
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/itcommunity/chats/trans/sql/sql1023.asp -
Performance Monitoring for the SQL Server Professional Chat
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Cami" <clawson@.informs.com> wrote in message
news:029801c36688$a4567220$a401280a@.phx.gbl...
> We have inherited a system with a Sybase DB and have been
> asked to move it to SQL 7. We have converted the data and
> the database with expected datatype problems and it seems
> to be working. The application is a PowerBuilder 7
> program that was using and ODBC and is now using OLE DB
> for the SQL. In a one man environment it is great, move
> it to the production machine and tell all 47 folks to sign
> on, the Enterprise Manager quits responding, tables lock
> all over the place and no one can do updates or inserts.
> The tables have PK and FK with clustered indexes pretty
> thoroughly placed. The machine is a NT 4.0 with 1GB of
> RAM with a raid drive 20GB.
> Any suggestions on what to do to increase performance on
> this one?
> TIA,
> cami
>