Pulling Data for A Week

[leebaldwin]

Good afternoon:

I have the following sql statement that I have written and it is pulling in data with no issue. My problem is that we want to have a full weeks of data. So, for this week (02/06 - 02/10) we would use the date for Sunday (02/05) and we would run the report each day with that date. I am not sure how to get the Sunday date.

SELECT
b.jopgm AS "Job Program",
b.jojob AS "Job Name",
b.jonbr AS "Job Number",
c.c1name AS "Customer Name",
b.o1tkn AS "Taken By",
b.jouser AS "User",
b.joentt AS "Event Type",
b.jolib AS "Library",
b.jofile AS "File",
b.jotstp AS "Timestamp",
b.o1cmpy AS "Company Code",
b.o1cst1 AS "Sold-To Number",
b.o1sfx1 AS "Sold-To Suffix",
b.o1ord# AS "Order Number",
b.o1line AS "Order Line",
CAST(date(b.o1ojul+693960) as date) AS "Order Date",
CAST(date(b.o1rjul+693960) as date) AS "Request Date",
b.o1whse AS "Order Warehouse",
b.o1svia AS "Ship Via",
b.o1orby AS "Ordered By"

FROM cams.OPP010H as b

INNER JOIN cams.CIP010 AS c ON c.c1cst#=b.o1cst1 AND c.c1csfx=b.o1sfx1

WHERE CAST(date(b.o1ojul+693960) as date) > (Current Date - 3 Days) AND b.jopgm = 'OPR810' AND b.jouser IN('user1','user2',...) and o1tkn in('id1', 'id2', ...)

UNION ALL

SELECT
s.jopgm AS "Job Program",
s.jojob AS "Job Name",
s.jonbr AS "Job Number",
c.c1name AS "Customer Name",
s.o1tkn AS "Taken By",
s.jouser AS "User",
s.joentt AS "Event Type",
s.jolib AS "Library",
s.jofile AS "File",
s.jotstp AS "Timestamp",
s.o1cmpy AS "Company Code",
s.o1cst1 AS "Sold-To Number",
s.o1sfx1 AS "Sold-To Suffix",
s.o1ord# AS "Order Number",
s.o1line AS "Order Line",
CAST(date(s.o1ojul+693960) as date) AS "Order Date",
CAST(date(s.o1rjul+693960) as date) AS "Request Date",
s.o1whse AS "Order Warehouse",
s.o1svia AS "Ship Via",
s.o1orby AS "Ordered By"

FROM cams.OPP010SH as s

INNER JOIN cams.CIP010 AS c ON c.c1cst#=s.o1cst1 AND c.c1csfx=s.o1sfx1

WHERE CAST(date(b.o1ojul+693960) as date) > (Current Date - 3 Days) AND b.jopgm = 'OPR810' AND b.jouser IN('user1','user2',...) and o1tkn in('id1', 'id2', ...)

Thanks in advance for any advice.

[lbendlin]

Do you need help determining the last sunday before "today" ?

[leebaldwin]

Yes sir, that is exactly what I need. Regardless of the weekday, it should pull the previous Sunday. Thank you.

[lbendlin · Accepted solution]

what should happen when you run this on a sunday?  Still the same rule ( ie take the previous sunday) ?

let
    Source = List.Dates(#date(2023,1,1),60,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Previous Sunday", each let d=Date.DayOfWeek([Column1]), p=if d=0 then 7 else d, r=[Column1]-#duration(p,0,0,0) in r)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[leebaldwin]

@lbendlin That is awesome. Definitely what I needed.