Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4

    Answered: Select the last full week of data

    I am sure there is a simple answer to this, I just cant come up with it. I have a set of tables a Header table with PK ShpmtNum, ShpmntDate and summary data and Data table that contains the FK ShpmntNum and data for export and reporting. Currently once a week on Tuesday we batch load daily files from the previous Wed-Tues. Everything is loaded to a temp table and then extracted before adding the data to the main table. We are switching it to a automated daily load while still doing a weekly extract. I need to determine the last Tuesday date on the Header table and then extract data back to the Wednesday before. So if its Tuesday but the data has not yet loaded then we would pull for previous week, if Tuesday is loaded we pull for the current week. If data has not loaded for several weeks we would determine the last full week Wed-Tues and pull that data. The tables look like this.

    Shipment-Date, PK(ShipmentNumber), SummaryData
    FK(ShipmentNumber), ShpimentData

  2. Best Answer
    Posted by DNance

    "try dayofweek function. get max date where dayofweek = Tuesday and then go back from that date."


  3. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    try dayofweek function. get max date where dayofweek = Tuesday and then go back from that date.

  4. #3
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4
    Thanks, I was able to use that function to create a view with all available Tuesdays and use that in for this and in reports.

  5. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    Not terribly well tested, but you can try this. The CTE may be better off as a variable, but in case you need this in a view:

    Code:
    create table #shiphdr
    (shipnbr int not null primary key,
     shipdt datetime not null,
     summarydata varchar(100));
    
    with cte (rownum)
    as
    (select top 30 row_number() over (order by name)
     from sys.objects)
    
    insert into #shiphdr
    select 31 - rownum,
    	dateadd(dd, -1 * rownum,  getdate()),
    	'important stuff'
    from cte;
    
    with lastshipment (maxdt)
    as
    (select max(shipdt)
     from #shiphdr
     where datename(dw, shipdt) = 'Tuesday')
    
    select *
    from #shiphdr s cross join
    	lastshipment l
    where s.shipdt > dateadd(dd, -7, l.maxdt)
      and s.shipdt <= l.maxdt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •