SQL- Weekly report seven days (Sunday to Saturday )

Question

I have created the following query to reoccur every seven days starting including Saturday and Sunday. This report will be run every seven days. However, the problem I am facing on the days where there were files received in our SFTP folder (inbound) report should have an entry for the missing Null= 0. The primary goal to make this an automated process that will be executed every Sunday through Sunday every seven days Example:

SELECT SubmitterID,SubmitterName,convert(varchar(15), DateReceived, 101) DateReceived,sum(ClaimCount) as TotalCount
FROM FalloutClaimReport
WHERE DateReceived BETWEEN '2019-06-01' AND '2019-06-07'
--ORDER BY COUNT(submitterID) DESC;
GROUP BY submitterid, SubmitterName, convert(varchar(15), DateReceived, 101)

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2019-06-01' --AND '2019-06-10'
SET @StartDate = '2019-06-07'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

Answer 1

Audrey,

I would suggest two possible solutions. Assuming by SRS you meant, SSRS....

1) I would set your SSIS job to run through a SQL Agent every 7 days. It would call a stored procedure (SP) that then ran and wrote into a table... when that SP would be called it would run:

SELECT 
SubmitterID,
SubmitterName,
convert(varchar(15), DateReceived, 101) DateReceived,
sum(ClaimCount) as TotalCount
FROM FalloutClaimReport
WHERE cast(DateReceived as date) BETWEEN dateadd(d,-7,cast(getdate() as date)) AND dateadd(d,-1,cast(getdate() as date))

GROUP BY 
submitterid,
SubmitterName,
convert(varchar(15), DateReceived, 101)

2) If you decide to go the SSRS route, you should make a report subscription that sends automatically to the users you need that calls the stored procedure above and sends what you need to whoever needs it. (The code above should be enough for that assuming it's selecting what you need)

Answer 2

I'm a little uncertain what you're looking for but I think the general approach is you're trying to get a week's worth of data.

Date calculations

Let's start with some queries (and these presume a US install as the default day is Monday.

SELECT
    DATEADD(WEEK, -1, CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), -1) AS date)) AS TheLastSundayOfTheFullWeek
,   DATEADD(WEEK, -1, CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), +5) AS date)) AS TheLastSaturdayOfTheFullWeek
,   CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), -1) AS date) AS SundayOfTheCurrentWeek
,   CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), +5) AS date) AS SaturdayOfTheCurrentWeek;

These queries generate the following dates

TheLastSundayOfTheFullWeek TheLastSaturdayOfTheFullWeek SundayOfTheCurrentWeek SaturdayOfTheCurrentWeek
2019-06-30                 2019-07-06                   2019-07-07             2019-07-13

The last full week would run 6/30 to 7/06. The current week would be defined as 7/7 to 7/13.

Depending on which week definition you need, choose the appropriate pair of columns.

Dealing with the unknowns

In situations like this, I build out a virtual table with all expected dates (or elements) my report should have. I then use that to drive a connection to the actual data table. Since we don't know that we'll find any rows for a given date, I connect the tables with a LEFT JOIN

SELECT
    FCR.SubmitterID
,   FCR.SubmitterName
,   CONVERT(varchar(15), ED.DateReceived, 101) AS DateReceived
,   SUM(FCR.ClaimCount) AS TotalCount
FROM
    (
        -- This logic builds out a list of all the dates that must exist on the report
        -- I used the logic for TheLastSundayOfTheFullWeek
        SELECT
            DATEADD(DAY, D.DayOffset, DATEADD(WEEK, -1, CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), -1) AS date))) AS DateReceived
        FROM
        (
            -- Generate a series of 7 numbers from 0 t 6
            SELECT TOP 7
                -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
            FROM
                sys.all_columns AS AC 
        ) D(DayOffset)
    ) AS ED
    LEFT OUTER JOIN
        dbo.FalloutClaimReport AS FCR
        ON FCR.DateReceived = ED.DateReceived
GROUP BY
    CONVERT(varchar(15), ED.DateReceived, 101)
,   FCR.SubmitterID
,   FCR.SubmitterName;

That generates a result set like

We didn't have data on the the 30th or the 5th but there are still records on the query. If you need default values in there, wrap the column with ISNULL/COALESCE calls.

DBFiddle version to provide a testing sandbox