Solved

Calculating week to date SQL

Posted on 2016-07-22
4
39 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
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now