Solved

Calculating week to date SQL

Posted on 2016-07-22
4
66 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 250 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 250 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

636 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