How can i acive the following:

Product, From, To

Pizza, 1, 3

Burger , 1, 5

Hotdog, 2, 2

Result:

[somecolumn], Occurs

1, 2

2, 3

3, 2

4, 1

5, 1

I want to do this without a function or procedure, just a "simple" s

Which version of SQL Server do you have?

And, if the answer is SQL 2008 or earlier, what volumes are we talking about?

HI,

This query show you Occurs.

SELECT Product,COUNT(*) FROM YourTable
GROUP BY Product

are you sure you need running totals for this ?

SELECT PRODUCT,From,To,
SUM(cnt) OVER (Partition by PRODUCT ORDER BY To)
FROM (
SELECT PRODUCT,From,To,1 as cnt FROM YourTable
) a
ORDER BY PRODUCT ,To

2008 R2(SP3)

2-3000000 rows

HI,

This query show you Occurs.

SELECT Product,COUNT(*) FROM YourTable
GROUP BY Product

are you sure you need running totals for this ?

SELECT PRODUCT,From,To,
SUM(cnt) OVER (Partition by PRODUCT ORDER BY To)
FROM (
SELECT PRODUCT,From,To,1 as cnt FROM YourTable
) a
ORDER BY PRODUCT ,To

Maybe, how else would you count 'Occurs'.

Pizza must be included in 1, 2 and 3.

http://sqlfiddle.com/#!6/08320a

Good day,

If I understood your needs correctly then you should use numbers table. Please check if this is what you need:

/******************************* DDL+DML */
create table T (
	[Product] nvarchar(10), 
	[From] int, 
	[To] int
) 

insert T ([Product], [From], [To])
values
('Pizza'  , 1, 3),
('Burger' , 1, 5),
('Hotdog' , 2, 2)
GO

/******************************* DDL+DML */
select NumberF,COUNT(NumberF) as Occurs
from _ArielyAccessoriesDB.dbo.ArielyNumbersTbl Numbers -- I am using Numbers table which is accessories table that any instance should have (just numbers from 1 to 10 million with clustered index)
inner join T on T.[From] <= Numbers.NumberF and T.[To] >= Numbers.NumberF
group by NumberF
GO

/******************************* Clean */
DROP table T

* I recommend to use Accessories database, which is in READ_ONLY mode (this will keep it safe and better performance). This is how you can create the Numbers table:

/******************************************************** _ArielyAccessoriesDB  Accessories tables, functions */
USE [_ArielyAccessoriesDB]
GO

---------------------------------------------------> Numbers table
CREATE TABLE ArielyNumbersTbl (NumberF  int  not null)
GO

INSERT INTO ArielyNumbersTbl(NumberF)
SELECT TOP 10000000 row_number() over(order by (select NULL)) as N
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
GO

ALTER TABLE ArielyNumbersTbl ADD CONSTRAINT PK_ArielyNumbersTbl PRIMARY KEY CLUSTERED (NumberF)
GO

Fiddle:

Fiddle