none
ADDING AN ADDITIONAL FILTER WHILE JOINING TABLES pt. 2

    Question

  • I have another unique situation that I am not too familiar with. I have a table join that queries a set of data that spans a four week period.

    What I need to add is an additional filter to get the sum of the sales from the previous four week period as well. So.. everything stays the same, just the start date and end date will differ.  Here is what I have so far:

    SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME,
    (SUM(B.ITEM_EXT) - SUM(B.ITEM_DISCOUNT) - SUM(B.ITEM_COUPON)) AS TAXABLE,
    COUNT(DISTINCT B.SALES_ID) AS CUSTOMER_COUNT,
    SUM(ITEM_QUANTITY) AS ITEMS_COUNT
    FROM DEPARTMENTS  A INNER JOIN SALES_ENTRIES B
    ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
    WHERE A.DEPARTMENT_ACTIVE = 'TRUE' AND B.TIME_STAMP BETWEEN @FOURTH_WEEK AND @END_DATE
    GROUP BY A.DEPARTMENT_ID, A.DEPARTMENT_NAME
    ORDER BY A.DEPARTMENT_NAME ASC

    An example would be getting all the data from 7/28 - 8/25 and 7/21 - 8/18.

    Thank you in advance for any help you can provide.

    Patrick


    Patrick Regis

    Sunday, September 08, 2013 4:50 PM

Answers

  • You can just use the DATEADD function to get the new range markers:

    DATEADD(WEEK,-4, @YOURCurrentPeriodStart)

    DATEADD(WEEK,-4, @YOURCurrentPeriodEnd)


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Sunday, September 08, 2013 9:44 PM
    Moderator
  • Yes, but if I have the table in SSRS tied to a dataset, how can I produce both sets of data from the one query?

    Patrick Regis


    Hi Patrick,

    As far as I know, one table can only be tied to one dataset, how to you want to show both of the sets with one table? If you want to union all the records from 7/28 - 8/25 and 7/21 - 8/18, the final dataset will mix all the records and we cannot distinguish which group does the record belong to. Additionally, we can see there are some duplicated record of these two group, if you want to remove the duplicated ones, we can just query data between 7/21 - 8/25. Please provide our details about the requirements and the final result.


    Allen Li
    TechNet Community Support

    Tuesday, September 10, 2013 7:35 AM
    Moderator

All replies

  • You can just use the DATEADD function to get the new range markers:

    DATEADD(WEEK,-4, @YOURCurrentPeriodStart)

    DATEADD(WEEK,-4, @YOURCurrentPeriodEnd)


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Sunday, September 08, 2013 9:44 PM
    Moderator
  • Yes, but if I have the table in SSRS tied to a dataset, how can I produce both sets of data from the one query?

    Patrick Regis

    Monday, September 09, 2013 12:30 AM
  • Yes, but if I have the table in SSRS tied to a dataset, how can I produce both sets of data from the one query?

    Patrick Regis


    Hi Patrick,

    As far as I know, one table can only be tied to one dataset, how to you want to show both of the sets with one table? If you want to union all the records from 7/28 - 8/25 and 7/21 - 8/18, the final dataset will mix all the records and we cannot distinguish which group does the record belong to. Additionally, we can see there are some duplicated record of these two group, if you want to remove the duplicated ones, we can just query data between 7/21 - 8/25. Please provide our details about the requirements and the final result.


    Allen Li
    TechNet Community Support

    Tuesday, September 10, 2013 7:35 AM
    Moderator