how to get a value from a Record at 95% of a SUM ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to get a value from a Record at 95% of a SUM ?

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2009-05-13 : 05:33:31

Hello all !

Here is my problem :

I have ordered items. Ordered itemps have a datetime of order (OrderMoment). The OrderedItems have also an OrderedWeight.

Let's say that in a week 1000 kg have been ordered. Now I want to know at what moment 95% (thus 950 kg) have been ordered.
So what I need to do is to get all the OrderedItemps of this week, order them by OrderMoment and then found out which recored pushes the total ordered weight over 950 kg, and from this record get the OrderMoment.

For now I would do all this in my vb.net application, but I would like the sql-server to do this in order to put the load on the sql-server.

Do you have any ideas how to do this ?

Thanks a lot for any feedback !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 06:08:29

maybe it will be easier for all of us if you could provide your table DDL, sample data and the required output

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 09:51:25

Are you using SQL 2005?

fabianus76
Posting Yak Master

191 Posts

Posted - 2009-05-13 : 12:49:00

Hello visakh16,

yes I do use sql-server 2005 !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:22:57

[code]
;With Order_CTE(RowNo,Item,Date,Weight,WeekTotal)AS
(SELECT ROW_NUMBER() OVER(PARTITION BY OrderedItems ORDER BY OrderMoment),
OrderedItems , OrderMoment, OrderedWeight,
SUM(OrderedWeight) OVER ()
FROM YourTable
WHERE OrderMoment>=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND OrderMoment<DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)
)
SELECT TOP 1 o.*
FROM Order_CTE o
CROSS APPLY (SELECT SUM(OrderedWeight) AS TotalWeight
FROM Order_CTE
WHERE RowNo<=o.RowNo
)tot
WHERE TotalWeight>= 0.95 * WeekTotal
ORDER BY o.Date ASC
[/code]

fabianus76
Posting Yak Master

191 Posts

Posted - 2009-05-13 : 13:33:01

Hello khtan,

thanks for your interest in my problme. So here is a sample structure :

CREATE TABLE [dbo].[OrderDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Weight] [real] NULL,
[OrderMoment] [datetime] NULL,
[Product_ID] [int] NULL,
[Order_ID] [int] NULL
) ON [PRIMARY]

some sample data :

INSERT INTO [orkos.com].[dbo].[OrderDetail]
([Weight]
,[OrderMoment]
,[Product_ID]
,[Order_ID])
VALUES
(2.3
,'20090514'
,99
,1)

INSERT INTO [orkos.com].[dbo].[OrderDetail]
([Weight]
,[OrderMoment]
,[Product_ID]
,[Order_ID])
VALUES
(3
,'20090514'
,98
,3)

INSERT INTO [orkos.com].[dbo].[OrderDetail]
([Weight]
,[OrderMoment]
,[Product_ID]
,[Order_ID])
VALUES
(1
,'20090515'
,97
,4)

INSERT INTO [orkos.com].[dbo].[OrderDetail]
([Weight]
,[OrderMoment]
,[Product_ID]
,[Order_ID])
VALUES
(1.4
,'20090517'
,96
,5)

INSERT INTO [orkos.com].[dbo].[OrderDetail]
([Weight]
,[OrderMoment]
,[Product_ID]
,[Order_ID])
VALUES
(25
,'20090518'
,96
,5)

INSERT INTO [orkos.com].[dbo].[OrderDetail]
([Weight]
,[OrderMoment]
,[Product_ID]
,[Order_ID])
VALUES
(1.7
,'20090519'
,96
,5)

Now the Output I want is the OrderMoment of the beforelast line (which is '20090518'). Because the sum of the weights of this and previous records compared to the total weight makes 95,06 %.

You see ?

Thanks for any advice !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

fabianus76
Posting Yak Master

191 Posts

Posted - 2009-05-13 : 15:09:32

Hello visakh16,

Thank you very very much for your input !

For now I am totally unable to understand your tsql and I will take some hours to learn about it. Then - once I understood hopefully - I'll come back an report :-)

Thanks again !

Regards,
Fabainus

my favorit hoster is ASPnix : www.aspnix.com !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 10:25:19

Welcome
hoping to hear from you soon...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources