More complex Sum and Group by a week period

First thanks to Mike and Baie for helping me before.
I have a set of data similar to the below one.
issueID IssueDate
1 1/17/2005
2 1/18/2005
3 1/19/2005
4 1/24/2005
5 1/27/2005
6 2/7/2005
I need to query the set between 2 dates for example,
1/3/2005 and 6/5/2005 and what I need to return is the
sum of issues per 7 day groups starting from 1/3/2005.
Even if there are no issues opened in the 7 days
following the 1/3/2005 date, I need the query to return
0.
I need The query output to look like this:
WStart Count
1/3/2005 0
1/10/2005 0
1/17/2005 3
1/24/2005 2
1/31/2005 1
The reason I need to see this output this way, is that I
need to compare it to a projected set that the managers
estimated per 7 day periods starting from 1/3/2005.
Is this doable as a query? I only have read access to
the database, so I won't be able to create stored
procedures or views.
Please let me know if you have ideas.Having a calendar table (http://www.aspfaq.com/show.asp?id=2519), you can tr
y
something like:
declare @.sd datetime, @.ed datetime
set @.sd = '20050103'
set @.ed = '20050605'
select
min(c.td),
count(t.IssueID)
from
calendar as c
left join
t
on c.dt = t.IssueDate
where
c.dt >= @.sd and c.dt < dateadd(day, 1, @.ed)
group by
datediff(day, c.dt, @.sd) / 7
go
Not tested.
AMB
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||If it's possible, (as your sample data indicates) to NOT have actual data in
some of the date range groups you need to output as aggregate "buckets", the
n
your best bet, if you can, is to create a calendar table, and do an Outer
Join from it to the data table(s)...
But you say you have only read access, so then the only real option is to,
within a batch, create a table variable that contains the range of dates you
want to group on, and join this table variable to the data table(s)
Declare @.Dts Table(Dt DateTime Not Null)
Declare @.DT DateTime Set @.DT = '20050103'
While @.DT < '20050630' Begin
Insert @.DTs (DT) Values(@.DT)
End
Select D.DT, Count(*)
From @.DTs D Left Join DataTable T
On T.IssueDate Between D.DT And DateAdd(w, 1, D.DT)
Group By D.DT
-- Something like that--
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||Just to throw one more option onto the pile...
To generate the dates for the ws that you wish to query for you could use
the following query
select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605'
This will generate start and end dates for the target ws which you could
put in a table variable as CBretana suggests. You can also embed this query
in your original query like so...
select start_dt, count(issueID)
from (select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605') ws
left outer join test1
on issuedate >= start_dt and issuedate < exclusive_end_dt
group by start_dt
This approach is likely only useful for small sets of ws.
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||Danny Mansour wrote:
> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
A table of (nonnegative) integers is often handy.
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
CREATE TABLE N
(
i INT NOT NULL CHECK (i>=0) PRIMARY KEY
)
INSERT INTO N (i)
SELECT Ones.d + 10*Tens.d + 100*Hundreds.d + 1000*Thousands.d +
10000*TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands
CREATE VIEW Calendar (d)
AS
SELECT CAST('19000101' AS DATETIME) + N.i
FROM N
CREATE TABLE Issues
(
issue_id INT NOT NULL PRIMARY KEY,
issue_date DATETIME NOT NULL
)
-- Sample data
INSERT INTO Issues (issue_id, issue_date)
VALUES (1, '20050117')
INSERT INTO Issues (issue_id, issue_date)
VALUES (2, '20050118')
INSERT INTO Issues (issue_id, issue_date)
VALUES (3, '20050119')
INSERT INTO Issues (issue_id, issue_date)
VALUES (4, '20050124')
INSERT INTO Issues (issue_id, issue_date)
VALUES (5, '20050127')
INSERT INTO Issues (issue_id, issue_date)
VALUES (6, '20050207')
-- This is a purely relational solution. If you need to do
-- more general queries, this might be helpful.
-- start_date and end_date is the overall period
-- interval is a number of days
-- period_start and period_end is the period we're tallying where
-- period_start is inclusive and period_end is exclusive
-- issue_tally is the tally for the period
CREATE VIEW IssueTally
(start_date, end_date, interval,
period_start, period_end, issue_tally)
AS
SELECT SD.d, ED.d, L.i,
SD.d + L.i * T.i, SD.d + L.i * (T.i + 1),
COUNT(I.issue_id)
FROM Calendar AS SD -- start date
INNER JOIN
Calendar AS ED -- end date
ON ED.d > SD.d
INNER JOIN
N AS L -- interval length
ON L.i > 0
INNER JOIN
N AS T -- number of intervals
ON T.i <= DATEDIFF(DAY, SD.d, ED.d) / L.i
LEFT OUTER JOIN
Issues AS I
ON I.issue_date >= SD.d + L.i * T.i AND
I.issue_date < SD.d + L.i * (T.i + 1)
GROUP BY SD.d, ED.d, L.i, T.i
SELECT start_date, end_date, interval,
period_start, period_end, issue_tally
FROM IssueTally
WHERE start_date = '20050103' AND
end_date = '20050605' AND
interval = 7
ORDER BY period_start
JAG|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.