Solved

Calculating week to date SQL

Posted on 2016-07-22
4
61 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 65

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 48

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 48

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion 6 47
T-SQL: How to append a column for serialized JSON data? 2 51
Complex SQL Server WHERE CLause 9 40
first parameter x of the decimal (x, y) 1 17
     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 …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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