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
4
Medium Priority
?
69 Views
Last Modified: 2016-07-27
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;

Open in new window


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' );

Open in new window


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 ( DATA.ship_date_cascaded BETWEEN @YrStartDate
                                      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;

Open in new window

0
Comment
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
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

Accepted Solution

by:
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

by:PortletPaul
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)

Open in new window

DECLARE @d TABLE (
      ship_date_cascaded datetime
    , 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
      CONVERT(varchar, ship_date_cascaded, 9) ship_date_cascaded
    , 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
;

Open in new window


PLEASE see Beware of Between

re-try that test case code with these changed lines (15, 22, 25)
DECLARE @d TABLE (
      ship_date_cascaded datetime
    , 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
      CONVERT(varchar, ship_date_cascaded, 9) ship_date_cascaded
    , total_qty
    , CASE
            WHEN (DATA.ship_date_cascaded >= @MthStartDate AND DATA.ship_date_cascaded  < @MthEndDate) THEN total_qty
      END                                     month_qty
    , CASE
            WHEN (DATA.ship_date_cascaded >= @YrStartDate AND DATA.ship_date_cascaded  < @MthEndDate) THEN total_qty
      END                                     year_qty
FROM @d data
;

Open in new window

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)

Open in new window

0
 

Author Comment

by:mburk1968
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

by:PortletPaul
ID: 41730761
Suggest you read https://msdn.microsoft.com/en-AU/library/ms174420.aspx

Look at week and iso_week

And look at the effect of datefirst too
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

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…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question