Link to home
Create AccountLog in
Avatar of hpradhan08
hpradhan08Flag for United States of America

asked on

How to setup the one week period on this code instead of a month?

I need to run weekly report and want to use our previous code which is monthly and modify them to make it run weekly: Here are the codes:
DECLARE       @P_Month             INT
DECLARE       @P_Year             INT

SELECT @P_Month=datepart(MM, dateadd(day, -1, getdate()))
SELECT @P_Year=datepart(year, dateadd(day, -1, getdate()))

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = Cast(@P_Month as varchar) + '-01-' + cast(@P_Year as varchar)
SET @EndDate = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@StartDate  )+1, 0))

select @StartDate, @EndDate
 Results:
2010-12-01 00:00:00.000      2010-12-31 23:59:59.997

How I modify this code to have result  the following
2010-12-27 00:00:00.000      2011-01-02 23:59:59.997

Could you please help?
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of hpradhan08

ASKER

Hi Genius,
No. This is the T-sql script, which we need to schedule to run on every Monday 10PM (weekly).

Could you please tell me what is -2 and %7 mean? also, what ms, -3 does?

Thank you so much for your help.
Scheduled script would also be OK... It is basically a batch job.

But to answer your questions...

The date part for day of week is dependant on the setting for the first day of week (ie datefirst). The code used by cyberkiwi is getting the days back to an ordinal day of week. So, he is adding that @@datefirst to the day of week datepart and then doing modulo 7 arithmetic (gives the remainder) being the appropriate number of days offset for the Monday week commencing... Now the datediff is determining the number of days difference and because it is doing just the days, it effectively zeros out the time - ie sets it to start of day).

The ms are milliseconds and the finest granularity (or rounding error) is three milliseconds. So to get to the very last time before the next day, we simply subtract 3 milliseconds from the start of that next day.

And if it is being run every monday then dont have to worry too much about date functions (if you are sure it will run on a monday).

the getdate() will be the monday date, so only have to get rid of the time component.

ie:

SET @StartDate = datediff(d, 0, getdate())

then enddate is a few milliseconds before 7 days later ie

SET @EndDate = dateadd(ms,-3,@StartDate+7)
-- or
SET @enddate = @startdate +6.999999995      -- (decimal to give the same 3 milliseconds before the start of next week)

ok, to give you the full picture. Here is the script, which runs monthly:

DECLARE       @P_Month             INT
DECLARE       @P_Year             INT

SELECT @P_Month=datepart(MM, dateadd(day, -1, getdate()))
SELECT @P_Year=datepart(year, dateadd(day, -1, getdate()))

DECLARE @FileName     varchar(255)
DECLARE @FileLocation varchar(255)
DECLARE @LogFolder    varchar(255)
DECLARE @Subject      varchar(255)
DECLARE @Recipient    varchar(4000)
DECLARE @Message      varchar(4000)
DECLARE @strSQL       varchar(4000)

DECLARE @Warehouses        varchar(4000)

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = Cast(@P_Month as varchar) + '-01-' + cast(@P_Year as varchar)
SET @EndDate = dateadd(ms,-3,DATEADD(MM, DATEDIFF(M,0,@StartDate  )+1, 0))
select @StartDate, @EndDate



-- Clear out BCP output table
DELETE FROM sysmon..BCP_Output
WHERE procId = @@ProcId
      and Instance = @P_Month

SET @Warehouses = '148, 172, 156, 37, 91, 92, 153, 158, 155, 157, 154, 152, 159, 19, 132, 48, 124, 173, 122, 1'

DECLARE @WarehouseIDs TABLE
(WarehouseID INT)

INSERT INTO @WarehouseIDs
SELECT Value as WarehouseID
from DBO.CSVtoIntTable(@Warehouses, ',')

-- Insert Header
INSERT INTO sysmon..BCP_Output
     (procId,Instance,OutputStr)
VALUES (@@ProcId, @P_Month,
'Warehouse' + char(9) +
'WhseDescription' + char(9) +
'N&T -Mail Stops' + char(9) +
'N&T -Hand Stops' + char(9) +
'Incidental -Mail Stops' + char(9) +
'Incidental -Hand Stops')

-- Insert Data Lines
INSERT INTO sysmon..BCP_Output
     (procId,Instance,OutputStr)

SELECT Distinct @@ProcID, @P_Month,
W.DisplayCode + char(9) +
W.Description + char(9) +
cast( SUM(CASE WHEN OrderTypeID = 8 and DVALL.WhseDeliveryCodeID = 1 then Stops  -- N&T Mail
else 0 END) as varchar) + char(9) +
cast( SUM(CASE WHEN OrderTypeID = 8 and DVALL.WhseDeliveryCodeID = 2 then Stops  --N&T Hand
else 0 END) AS varchar) + char(9) +
CAST( SUM(CASE WHEN OrderTypeID <> 8 and DVALL.WhseDeliveryCodeID = 1 then Stops  --Incidental Mail
Else 0 End) as varchar) + char(9) +
CAST( SUM(CASE WHEN OrderTypeID <> 8 and DVALL.WhseDeliveryCodeID = 2 then Stops  --Incidental Hand
Else 0 End) as varchar)

FROM(
      Select
      OrderTypeID,
      DV.WarehouseID,
      WhseDeliveryCodeID,
      Count(OrderID) as Stops

      FROM(
            Select Distinct            
            OD.OrderID,
            OD.BatchNumber,
            oh.OrderTypeID,
            OD.WarehouseID,
            OD.WhseDeliveryCodeID

            from (select odh.OrderDetailID, odh.OrderDetailStatusID
                   from OrderDetailHistory odh with(NOLOCK)
                   Where odh.OrderDetailStatusID = 6
                    and odh.HistoryDate between @StartDate and @EndDate
                   ) odh
            inner join OrderDetail od with(NOLOCK)
             on ODH.OrderDetailID = od.OrderDetailID
            inner join OrderHeader oh with(NOLOCK)
             on OD.OrderID = oh.OrderID
      
            Where OD.WarehouseID in (Select WarehouseID from @WarehouseIDs )
                  AND OD.WhseDeliveryCodeID in (1,2)
            ) DV
      group by DV.OrderTypeID, DV.WarehouseID, DV.WhseDeliveryCodeID
      ) dvall
inner join Warehouse W with(NOLOCK)
 on DVALL.WarehouseID = W.WarehouseID
group by
W.DisplayCode, W.Description

SET @FileLocation = dbo.varGlobal('BillingReport','Folder')

-- Define filename
SET @FileName = @FileLocation + 'BillReportStopsbyWarehouseOrderType_' + CONVERT(VARCHAR(6), @StartDate, 12) + '.txt'

-- Perform BCP to get file to local
SET @strSQL = 'bcp "SELECT OutputStr'
          + ' FROM sysmon..BCP_Output with (nolock)'
          + ' WHERE procId = ' + cast(@@ProcId as varchar)
              + ' AND Instance = ' + cast(@P_Month as varchar)
          + ' ORDER by Id" queryout "'
          + @FileName
          + '" -T -c -q -S '
          + @@ServerName
EXEC master..xp_cmdshell @strSQL

-- File sent via BCP, now clear our data from table
DELETE FROM sysmon..BCP_Output
    WHERE procId = @@ProcId
      and Instance = @P_Month

   -- Send Emails
   SET @Recipient = 'abc@org.com'

   SET @Subject = 'Billing Report of Stops by Warehouse & OrderType for ' + cast(@P_Month as varchar) + '-' +
                                    cast(@P_Year as varchar)
   SET @Message = 'Attached is the  ' + @FileName
   EXEC mon.dbo.[Send_Mail] @Recipient, @Subject, @Message, null, @FileName


**********
I'm trying to modify this script to run every monday at 10PM. Once again thank you so much for your prompt response. I appreciate it.

Alternatively, try the following:
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = Convert(DateTime, CONVERT(VarChar(10), GetDate() - 1, 112), 112)
SET @EndDate = DATEADD(ms, -3, @StartDate + 7)

select @StartDate, @EndDate

Open in new window

OK, do you still want to run it monthly as well as weekly ?

What do you want in place of P_Month ? Maybe weekno (which adds a new problem - start of year and the 53rd week) ?  Maybe Month + Day ?

Are you assured of it running every Monday ? Do you need to go back and re-run ?
yes.  But I'll create separate job for the weekly executing the modified script.

P_week if it doesn't adds new problem.

Yes. It's assured to run every Monday at 10PM.
No. They just need the weekly report.
If you simply replaced
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = Cast(@P_Month as varchar) + '-01-' + cast(@P_Year as varchar)
SET @EndDate = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@StartDate  )+1, 0))

Open in new window

In the monthly script, with this as given in first comment
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = datediff(d, (@@datefirst + datepart(dw,getdate()) -2) % 7, getdate())
SET @EndDate = dateadd(ms,-3,@StartDate+7)

Open in new window

That will give you the weekly range (week starting Monday) that is robust even if you chose to run on Tuesday in the future, or if you had to run it manually on Thursday out of schedule.

How does it work? Mark has already done a comprehensive job explaining it in http:#a34442365
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you Mark, for the good advice. But currently, we've other 3 reports combine with this and runs as batch file every month.  Just this one, we need to create separete job to run weekly.
Ah well... there is now the code in there to use for weekly or for monthly :) simply kill the create procedure stuff and keep the bit you want in between the begin / end of that "IF @p1" statement. So the only difference between the two is the calculation of start,end,P_period.

If you prefer, you can still use the modulo arithmetic for the weekly startdate, or, rely on the fact that it is being run on the start of a week commencing.

Also, think that it is probably the prior week you want to report on so need to subtract 7 from startdate.

There is still the concern about sysmon..BCP_Output where the first twelve weeks of any year has the same value as the twelve months of any year.
I'll take it from here.  Thank you very much  Mark and other experts. I appreciate your help.
Didnt see the first "ping"

Split

http:#a34344284   assist
http:#a34442882   assist
http:#a34442831   assist
http:#a34441300   solution
All,
 
Following an 'Objection' by mark_wills (at https://www.experts-exchange.com/questions/26831290/Automated-Request-for-Review-Objection-to-Delete-Q-26705319.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
 
At this point I am going to re-start the auto-close procedure.
 
Thank you,
 
Vee_Mod
Community Support Moderator