August 2, 2016 at 3:50 am
Hi
I need to be able to compare current data to previous. e.g if my query is:
Select Count(WorkOrder) as Current, Count(WorkOrder) as Previous FROM tableA
WHERE RequireByDate between '20160725' and '20160731'
in the results I must also get the count for previous date which is BETWEEN '20160718' and '20160724'
the dates will be based on the parameter date selection.
I hope this makes sense.
August 2, 2016 at 4:22 am
Here's one way of doing it:
DECLARE @CurrentStartDate date = '20160718';
DECLARE @CurrentEndDate date = '20160724';
DECLARE @PreviousStartDate date = '20150718';
DECLARE @PreviousEndDate date = '20150724';
SELECT
(SELECT COUNT(WorkOrder)
FROM TableA
WHERE RequireByDate BETWEEN @CurrentStartDate AND @CurrentEndDate) As Current
,(SELECT COUNT(WorkOrder)
FROM TableA
WHERE RequireByDate BETWEEN @PreviousStartDate AND @PreviousEndDate) AS Previous
John
August 2, 2016 at 5:24 am
Thanks John for the quick response.
The only issue with this query is I don't know what selection date is the user going to use when running the report since the date is going to be the parameter.
If the current date parameter selection eg. datediff(day,StartDate,EndDate) is 3 days, the previous will be StartDate -3 days previous.
August 2, 2016 at 5:30 am
Well, that was just an example. You could have, for example, a start date and an interval as parameters to your report, and then calculate the other dates from those according to your business rules. It should be very easy. If you want me to show you how, please provide more information on exactly what the user specifies and what the business rules are.
John
August 4, 2016 at 5:19 am
Hi John
here's the sample data, hope this makes sense:
CREATE TABLE CountWO
(WorkOrderCode varchar(10) null,RequiredByDate datetime null);
DECLARE @StartDate DATETIME,
DECLARE @EndDate DATETIME,
SET @StartDate = '20160308';
SET @EndDate = '20160313'
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00172','2016-03-01 00:00:00.000');
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00194','2016-03-01 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00175','2016-03-02 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00253','2016-03-03 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00258','2016-03-04 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00216','2016-03-04 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01007','2016-03-05 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01562','2016-03-05 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00945','2016-03-06 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00995','2016-03-07 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01003','2016-03-08 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01010','2016-03-09 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01013','2016-03-10 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01016','2016-03-11 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01019','2016-03-12 00:00:00.000')
INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S01023','2016-03-13 00:00:00.000')
SELECT COUNT(WorkorderCode) AS CurrentCount, COUNT(WorkOrderCode) AS PreviousCount FROM CountWO
WHERE RequiredByDate BETWEEN @StartDate AND @EndDate +1
-----
Results:
CurrentCount = 6: PreviousCount = 8
how I got the 6 IS the COUNT BETWEEN StartDate AND ENDDate.
The previous which IS 8 IS the COUNT BETWEEN the 2nd TO the 7th.
So whatever the StartDate AND EndDate the USER SELECT, i must be able TO calculate the previous records BETWEEN the exact number OF days.
August 4, 2016 at 5:36 am
All I've done here in addition to what I posted before is calculate the previous start and end dates based on the given start and end dates. You could probably have worked that out for yourself. Never mind, here's the code:DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @PreviousStartDate date
DECLARE @PreviousEndDate date
SET @StartDate = '20160308';
SET @EndDate = '20160313'
SELECT
@PreviousStartDate = DATEADD(day,-6,@StartDate)
,@PreviousEndDate = DATEADD(day,-6,@EndDate);
SELECT
(SELECT COUNT(WorkOrderCode)
FROM CountWO
WHERE RequiredByDate BETWEEN @StartDate AND DATEADD(day,1,@EndDate)) AS CurrentCount
,(SELECT COUNT(WorkOrderCode)
FROM CountWO
WHERE RequiredByDate BETWEEN @PreviousStartDate AND @PreviousEndDate) AS PreviousCount
John
August 4, 2016 at 6:47 am
Thank you so much, it works
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply