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
Go to Top of Page

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]




Go to Top of Page

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.
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-29 : 03:19:53
I suspected that the same thing (middle tier), thanks.
Go to Top of Page
   

- Advertisement -