CODEWITHSUNDEEP
sql-server
Maury Markowitz
Maury Markowitz

Reputation: 9279

Partition a row over (num) rows in SQL Server

I have data of the form:

ID         name     date    count
---------------------------------
1           A     1/1/2015    3
2           B     1/4/2015    2
3           C     1/6/2015    4
4           D     1/10/2015   2

Which I would like to turn into something like...

1 A 1/1/2015
1 A 1/2/2015
1 A 1/3/2015
2 B 1/4/2015
2 B 1/5/2015
3 C 1/6/2015
...

I believe this is possible using a partition query, but I'm having a real problem understanding the examples I find on the MS page. I need to use ROW_NUMBER in DATEADD, but I can't figure out how to get it to return the right number of rows, say 3 for the first case. The TerritoryName example seems close...

In the worst case I can do this in VBA code, and I also know of solutions using tables filled with dates, but I think this is something I should know how to do in the SELECT if possible.

UPDATE: I am not allowed to change anything in the original database (a licensing issue). I could use a #blah, but would prefer to avoid this if possible.

Upvotes: 3

Views: 99

Answers (3)

Katerine459
Katerine459

Reputation: 485

If not all dates are represented, and you only want to list dates that are in the table, I think something like the following would work:

SELECT ranknum, itemname, itemdate
FROM (SELECT RANK() OVER(PARTITION BY t.itemdate ORDER BY allitems.itemname) ranknum
     , t.id
     , allitems.itemname
     , t.itemdate
     FROM (SELECT DISTINCT itemname FROM tablename) allitems 
         LEFT JOIN tablename t ON allitems.itemname = t.itemname) ranked
WHERE id IS NOT NULL
ORDER BY ranknum, itemdate

Upvotes: 0

Ivan Golović
Ivan Golović

Reputation: 8832

Perhaps you could use built-in spt_values table, if selecting on Type='P', Number returns consecutive numbers between 0 and 2047. If column count contains reliable value of day count query could be:

select t.id, t.name, dateadd(d, numbers.Number, t.date) as date
from t
join master..spt_values as numbers
on numbers.Type = 'P'
and numbers.Number < t.count

Fiddle: http://sqlfiddle.com/#!18/62aee/2

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

This is where a tally table is the right tool for the job. I keep one as a view on my system. It is insanely fast.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Now we just need some sample data and we can then join to our tally table.

set dateformat mdy --you really should use ANSI standard YYYYMMDD

insert @Something values
(1, 'A', '1/1/2015', 3)
,(2, 'B', '1/4/2015', 2)
,(3, 'C', '1/6/2015', 4)
,(4, 'D', '1/10/2015', 2)

select s.*
    , DATEADD(day, t.N - 1, s.SomeDate)
from @Something s
join cteTally t on t.N <= s.SomeCount

Since you can't create a view you can trim this cte down a bit also to fit your current needs and have the cte be in your query. Something like this.

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1
    )
select s.*
    , DATEADD(day, t.N - 1, s.SomeDate)
from @Something s
join cteTally t on t.N <= s.SomeCount

Upvotes: 2

Related Questions