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.
You can just use the DATEADD function to get the new range markers:
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
- Proposed as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Tuesday, September 10, 2013 7:26 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?
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.
TechNet Community Support
- Proposed as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Monday, September 16, 2013 9:22 AM