Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Calculating week to date SQL

Posted on 2016-07-22
Medium Priority
69 Views
I have an SSRS report that I need to add "Week to Date" calculations  as I'm currently doing for MTD and YTD.  My first dataset calculates the AR_Period.

``````SELECT  AR_Period ,
DATENAME(mm, DATEADD(mm, CONVERT(INT, SUBSTRING(AR_Period, 5, 2)), -1))
+ ' ' + SUBSTRING(AR_Period, 1, 4) AS AR_Period_Text
FROM    [KLL AR Periods - Vertical]
WHERE   ( FISCALYR = '2016' )
AND ( '2016' > 2009 )
OR ( FISCALYR = '2016' )
AND ( '2016' = 2009 )
AND ( AR_Period >= '200904' )
ORDER BY AR_Period;
``````

Which for this example returns the following.
AR_Period      AR_Period_Text
201601      January 2016
201602      February 2016
201603      March 2016
201604      April 2016
201605      May 2016
201606      June 2016
201607      July 2016

The second dataset is the one I was hoping to add WkStartDate, WkEndDate
``````SELECT  Period_End_Date AS MthEndDate ,
DATEADD(mm, DATEDIFF(mm, 0, Period_End_Date), 0) AS MthStartDate ,
DATEADD(yy, DATEDIFF(yy, 0, Period_End_Date), 0) AS YearStartDate ,
DATEADD(yy, DATEDIFF(yy, 0, Period_End_Date) + 1, -1) AS YearEndDate
FROM    [KLL AR Periods - Vertical]
WHERE   ( AR_Period = '201607' );
``````

Calculates the  following:
MthEndDate                              MthStartDate                      YearStartDate                      YearEndDate
2016-07-31 00:00:00.000      2016-07-01 00:00:00.000      2016-01-01 00:00:00.000      2016-12-31 00:00:00.000

Then my primary query which I perform my MTD/YTD calculations
``````DECLARE @Division CHAR(3);
DECLARE @MthStartDate DATETIME;
DECLARE @MthEndDate DATETIME;
DECLARE @YrStartDate DATETIME;
SET @Division = 'KLL';
SET @MthStartDate = '2016-07-01 00:00:00.000';
SET @MthEndDate = '2016-07-31 00:00:00.000';
SET @YrStartDate = '2016-01-01 00:00:00.000';

SELECT  DATA.customer ,
zzxcustr.cust_name ,
DATA.CLASS ,
DATA.style_name ,
DATA.style ,
DATA.SCALEFIN AS [Size] ,
DATA.color_code AS [Color] ,
SUM(DATA.Style_StdCost) AS LDP ,
SUM(DATA.Style_Retail_Price) AS MSRP ,
SUM(CASE WHEN ISNULL(DATA.Style_Retail_Price, 0) = 0 THEN 0
ELSE ( DATA.Style_Retail_Price - DATA.Style_StdCost )
/ DATA.Style_Retail_Price
END) AS [TY MU] ,
Style_Season ,
SUM(DATA.total_qty * DATA.price * DATA.co_rate) AS YTD_Ext_Gross_Amt ,
SUM(DATA.total_qty) AS YTD_Total_Qty ,
SUM(DATA.total_qty * DATA.price * DATA.co_rate - ( DATA.total_qty
* DATA.price
* DATA.co_rate )
* ( ISNULL(zzxdiscr.disc_perc, 0) / 100 )) AS YTD_Ext_Net_Amt ,
SUM(DATA.total_qty * DATA.Style_StdCost) AS YTD_Ext_Cost ,
SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
AND @MthEndDate )
THEN ( DATA.total_qty * DATA.price
* DATA.co_rate )
ELSE ( 0 )
END)) AS MTD_Ext_Gross_Amt ,
SUM(CONVERT(MONEY, ( CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
AND
@MthEndDate )
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(zzxdiscr.disc_perc, 0)
/ 100 ) )
ELSE ( 0 )
END ) * DATA.co_rate)) AS MTD_Ext_Net_Amt ,
SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
AND @MthEndDate )
THEN ( DATA.total_qty * DATA.Style_StdCost )
ELSE ( 0 )
END)) AS MTD_Ext_Cost ,
SUM(( DATA.total_qty * DATA.price * DATA.co_rate )
* ( ISNULL(zzxdiscr.disc_perc, 0) / 100 )) AS YTD_Ext_Discount ,
SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
AND @MthEndDate )
THEN ( ( DATA.total_qty * DATA.price
* DATA.co_rate )
* ( ISNULL(zzxdiscr.disc_perc, 0) / 100 ) )
ELSE ( 0 )
END)) AS MTD_Ext_Discount ,
SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
AND @MthEndDate
AND DATA.slsperson1 LIKE 'JZ%'
)
THEN ( DATA.total_qty * DATA.price
* DATA.co_rate )
ELSE ( 0 )
END)) AS MTD_Ext_Intercompany ,
SUM(CONVERT(MONEY, CASE WHEN ( DATA.slsperson1 LIKE 'JZ%' )
THEN ( DATA.total_qty * DATA.price
* DATA.co_rate )
ELSE ( 0 )
END)) AS YTD_Ext_Intercompany
FROM    [KLL All Order Detail With Style Data] DATA
INNER JOIN zzxcustr ON DATA.customer = zzxcustr.customer
LEFT OUTER JOIN zzxdiscr ON DATA.discount = zzxdiscr.discount
LEFT OUTER JOIN ( SELECT    division ,
style ,
color_code ,
lbl_code ,
dimension ,
MAX(trans_date) AS MaxDate
FROM      zzcordrd
WHERE     stage = 'RECV'
GROUP BY  division ,
style ,
color_code ,
lbl_code ,
dimension
) ORD ON DATA.division = ORD.division
AND DATA.style = ORD.style
AND DATA.color_code = ORD.color_code
AND DATA.lbl_code = ORD.lbl_code
AND DATA.SCALEFIN = ORD.dimension
WHERE   ( DATA.line_status = 'I' )
AND     @MthEndDate )
AND ( DATA.division = @Division )
--AND ( DATA.customer IN ( @Customers ) )
AND ( DATA.customer IN ( 'DEC0010', 'MAD9060', 'SEV0010' ) )
GROUP BY DATA.CLASS ,
DATA.style_name ,
DATA.style ,
DATA.SCALEFIN ,
DATA.color_code ,
DATA.Style_StdCost ,
DATA.Style_Retail_Price ,
DATA.customer ,
zzxcustr.cust_name ,
Style_Season
ORDER BY DATA.customer ,
Style_Season;
``````
0
Question by:mburk1968
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 66

Accepted Solution

Jim Horn earned 1000 total points
ID: 41725060
Without digging into your custom SQL (sorry), check out my article called SQL Calendar Table, which is code that will create a table of days and all sorts of other column such as custom weeks and months such that you can modify your query to join on this table, and sum numbers based on week number.

This would save you the headache of having to custom code it in every SP, SSIS package, or SSRS report that requires this logic.

Good luck.
Jim
0

LVL 49

Assisted Solution

PortletPaul earned 1000 total points
ID: 41727501
You haven't defined what you think a week is.
Does a week start on Tuesday? Sunday?
What happens for the week spanning January 1; how do you handle that?
========

and PLEASE re-think when a month ends

In the following test case there is 1 row of data, the qty is 100.
Why would the Month_Qty or Year_Qty show null?

``````ship_date_cascaded         total_qty month_qty year_qty
-------------------------- --------- --------- --------
Jun 30 2016 12:13:14:000PM 100       null      null

(1 row(s) returned)

(1 row(s) affected)
``````
``````DECLARE @d TABLE (
, total_qty int
)

INSERT INTO @d
VALUES ('2016-06-30T12:13:14', 100)
;

DECLARE @YrStartDate datetime;
DECLARE @MthStartDate datetime;
DECLARE @MthEndDate datetime;

SET @MthStartDate = '2016-06-01 00:00:00.000';
SET @MthEndDate = '2016-06-30 00:00:00.000';
SET @YrStartDate = '2016-01-01 00:00:00.000';

SELECT
, total_qty
, CASE
WHEN (DATA.ship_date_cascaded BETWEEN @MthStartDate AND @MthEndDate) THEN total_qty
END                                     month_qty
, CASE
WHEN (DATA.ship_date_cascaded BETWEEN @YrStartDate AND @MthEndDate) THEN total_qty
END                                     year_qty
FROM @d data
;
``````

re-try that test case code with these changed lines (15, 22, 25)
``````DECLARE @d TABLE (
, total_qty int
)

INSERT INTO @d
VALUES ('2016-06-30T12:13:14', 100)
;

DECLARE @YrStartDate datetime;
DECLARE @MthStartDate datetime;
DECLARE @MthEndDate datetime;

SET @MthStartDate = '2016-06-01 00:00:00.000';
SET @MthEndDate = '2016-07-01 00:00:00.000';
SET @YrStartDate = '2016-01-01 00:00:00.000';

SELECT
, total_qty
, CASE
END                                     month_qty
, CASE
END                                     year_qty
FROM @d data
;
``````
``````ship_date_cascaded         total_qty month_qty year_qty
-------------------------- --------- --------- --------
Jun 30 2016 12:13:14:000PM 100       100       100

(1 row(s) returned)

(1 row(s) affected)
``````
0

Author Comment

ID: 41729486
My week begins on a Sunday. Although I do not know how to code for it myself I believe in the case of the week spanning January I could perhaps use a function to determine the number value in a week and subtract that from the value to have the week end 12/31?
0

LVL 49

Expert Comment

ID: 41730761

Look at week and iso_week

And look at the effect of datefirst too
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll