find out which rows that together match a criteria

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

SQL Server 2012 Forums

Transact-SQL (2012)

find out which rows that together match a criteria

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-28 : 02:46:20

Hi

I have a table with a "qty" column, I would like to find out which x number of rows to display based on the total sum of "Qty", lets say I need to find out what x numbers of rows total sum of qty give me 50, anyone knows how to make such a Query?

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-28 : 03:05:00

For the requirement you have to write a logic using Cursor or While loop.
Provide simulation environment script so that i can try to write logic.

Regards
Viggneshwar A

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-28 : 03:24:09

[code]

USE [TestDB]
GO

/****** Object: Table [dbo].[FileTest] Script Date: 2015-01-28 09:20:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FileTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](50) NULL,
[Qty] [int] NULL,
[DateAdded] [datetime] NULL,
CONSTRAINT [PK_FileTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [TestDB]
GO

INSERT INTO [dbo].[FileTest] ([FileName] ,[Qty],[DateAdded]) VALUES ('123.csv' ,1 ,'2015-01-20 00:00:00.000')
INSERT INTO [dbo].[FileTest] ([FileName] ,[Qty],[DateAdded]) VALUES ('456.csv' ,22 ,'2015-01-17 00:00:00.000')
INSERT INTO [dbo].[FileTest] ([FileName] ,[Qty],[DateAdded]) VALUES ('789.csv' ,34 ,'2015-01-19 00:00:00.000')
INSERT INTO [dbo].[FileTest] ([FileName] ,[Qty],[DateAdded]) VALUES ('777.csv' ,30 ,'2015-01-16 00:00:00.000')
INSERT INTO [dbo].[FileTest] ([FileName] ,[Qty],[DateAdded]) VALUES ('888.csv' ,20 ,'2015-01-14 00:00:00.000')
GO

[/code]

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-28 : 06:54:49

This looks like a best fit (Bin Packing) problem.

You could start by reading articles like:
https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/

It might be best to do this in the middle tier and not in t-sql.

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-29 : 03:19:53

I suspected that the same thing (middle tier), thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources