T-Sql Query problem

Hi, I am having data in a table  as below.

DMDGROUP      WEEKDATE      QTY
9896138670      6/10/2008      5
9896138670      13/10/2008      6
9896138670      27/10/2008      11
9896138670      3/11/2008      8
9896138670      10/11/2008      7
9896138670      1/12/2008      9
9896138670      8/12/2008      6
9885544076      13/10/2008      4
9885544076      3/11/2008      3
9885544076      10/11/2008      8
9885544076      17/11/2008      10
9885544076      1/12/2008      1

Now, I would like to include missing weeks with columns having QTY=0 and also the weekcount starting from 1 for each DMDGROUP. So, it looks like the below table.


DMDGROUP      WEEKDATE      QTY      weekcount
9896138670      6/10/2008      5      1
9896138670      13/10/2008      6      2
9896138670      20/10/2008      0      3
9896138670      27/10/2008      11      4
9896138670      3/11/2008      8      5
9896138670      10/11/2008      7      6
9896138670      17/11/2008      0      7
9896138670      24/11/2008      0      8
9896138670      1/12/2008      9      9
9896138670      8/12/2008      6      10
9885544076      13/10/2008      4      1
9885544076      20/10/2008      0      2
9885544076      27/10/2008      0      3
9885544076      3/11/2008      3      4
9885544076      10/11/2008      8      5
9885544076      17/11/2008      10      6
9885544076      24/11/2008      0      7
9885544076      1/12/2008      1      8



Can someone help me with as soon as possible.

Thanks
rpk2606Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
;with dg as (select DMDGROUP,MIN(Weekdate) as Week1, MAX(WEEKDATE) as LastWeek from tbl group by DMDGROUP)
select dg.DMDGROUP,wk.WeekDate,isnull(t.QTY,0),wk.WeekNo
FROM dg
cross apply (
      select w.number+1 as WeekNo, dateadd(wk,w.number,dg.Week1) as WeekDate
      from master..spt_values w
      where w.type='P' and w.number<=DATEDIFF(WK,dg.Week1,dg.LastWeek)) wk
left join tbl t on t.DMDGROUP=dg.DMDGROUP and t.WEEKDATE=wk.WeekDate
order by dg.DMDGROUP,wk.WeekDate
0
 
HainKurtSr. System AnalystCommented:
i suggest create a table numbers (n:int) and put 0,1,...,9

create view weeks as
select 10*d1.n+d2.n from numbers d2, numbers d1 where 10*d1.n+d2.n < 52

this will give use 0,1,2,...,51
0
 
HainKurtSr. System AnalystCommented:
and this one will give

select min(weekdate) middate, max(weekdate) maxdate, DATEPART(WK , min(weekdate)) minweek, DATEPART(WK , max(weekdate)) maxweek, dmdgroup from dmd group by dmdgroup

2008-10-13 00:00:00      2008-12-01 00:00:00      42      49      9885544076
2008-10-06 00:00:00      2008-12-08 00:00:00      41      50      9896138670
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
HainKurtSr. System AnalystCommented:
i give up ;)
0
 
rpk2606Author Commented:
awesome man...thanks a lot cyberkiwi
0
 
cyberkiwiCommented:
You're welcome!
0
 
rpk2606Author Commented:
Hi Cyberkiwi..I am trying to run this.

select dg.DMDUNIT,dg.DMDGROUP,wk.weekdate,isnull(t.QTY,0),wk.WeekNo
FROM SCADHOC.rpk.dg
cross apply (
      select w.number+1 as WeekNo, dateadd(wk,w.number,dg.Week1) as weekdate
      from master..spt_values w
      where w.type='P' and w.number<=DATEDIFF(WK,dg.Week1,dg.LastWeek)) wk
left join week t on t.dmdunit=dg.dmdunit and t.DMDGROUP=dg.DMDGROUP and t.weekdate=wk.weekdate
order by dg.dmdunit,dg.DMDGROUP,wk.weekdate

But I am getting this error now.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'week'.
0
 
cyberkiwiCommented:
;with dg as (
    select DMDGROUP,MIN(Weekdate) as Week1, MAX(WEEKDATE) as LastWeek
    from SCADHOC.rpk.dg
    group by DMDGROUP)
select dg.DMDGROUP,wk.WeekDate,isnull(t.QTY,0),wk.WeekNo
FROM dg
cross apply (
      select w.number+1 as WeekNo, dateadd(wk,w.number,dg.Week1) as WeekDate
      from master..spt_values w
      where w.type='P' and w.number<=DATEDIFF(WK,dg.Week1,dg.LastWeek)) wk
left join SCADHOC.rpk.dg t on t.DMDGROUP=dg.DMDGROUP and t.WEEKDATE=wk.WeekDate
order by dg.DMDGROUP,wk.WeekDate

The first 4 lines creates a virtual table named "dg".  It is heavily referenced further down.
The 2nd to last row is where you plug in your actual table name (and aliased at "t")
0
All Courses

From novice to tech pro — start learning today.