-3

I have a column called Dealname, ApproverName and Approval Date

I want to run a query every monday that will return all the Deals that have been approved by a certain person in the previous week. By previous week I mean the previous Monday to the Sunday.

How do I get deals approved in the last week?

cs_12345
  • 5
  • 1
  • Hi there, I'm using SQL Server Management Studio – cs_12345 Jul 03 '19 at 13:00
  • Possible duplicate of [How do I find last weeks dates Monday to Friday using SQL Server?](https://stackoverflow.com/questions/34719183/how-do-i-find-last-weeks-dates-monday-to-friday-using-sql-server) – JNevill Jul 03 '19 at 13:07
  • do you have any progress so far? post your SQL – scgough Jul 03 '19 at 13:07
  • Possible duplicate of [Previous Monday & previous Sunday's date based on today's date](https://stackoverflow.com/questions/12422248/previous-monday-previous-sundays-date-based-on-todays-date) – JNevill Jul 03 '19 at 13:09
  • Possible duplicate of [Previous Monday & previous Sunday's date based on today's date](https://stackoverflow.com/questions/12422248/previous-monday-previous-sundays-date-based-on-todays-date) – Eric Brandt Jul 03 '19 at 14:19

1 Answers1

0

I think I got it. Does this look right?

 Select 
        PO.[Name] "Deal"


    ,RA.[Date] "Approval Date"  
    ,U2.[DisplayName] "Signatory/ies"      

    From [RCT].[Resolution] RC

    Full Outer Join [RCT].[ResolutionApproval] RA On (RC.[ResolutionId] = RA. [ResolutionId])  

    Full Outer Join [RCT].[Portfolio] PO On PO.[PortfolioCode] = RC.[PortfolioCode]

    Full outer Join [RCT].[User] U2  on U2.UserId = RA.[ApproverId]<br>

    Where RA.[DATE] BETWEEN (
        DATEADD(day, -6,

        DATEADD(day, -1 * (( DATEPART(weekday, GETDATE()) % 7) -1), GETDATE())

        )
    )
    AND 
    (
    DATEADD(day, -1 * (( DATEPART(weekday, GETDATE()) % 7) -1), GETDATE())
    )
cs_12345
  • 5
  • 1
  • Is this something from your own production server? How is it relevant to OP's question (which, other than describing a few columns is severely lacking in DDL code to begin with)? – Zorkolot Jul 03 '19 at 20:55