Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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
0
rpk2606
Asked:
rpk2606
  • 3
  • 3
  • 2
1 Solution
 
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
 
cyberkiwiCommented:
;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:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now