October 10, 2017 at 11:16 am
rosarozina - Tuesday, October 10, 2017 10:42 AMFor example I wanted to get the total quantity of Bids and Complete Orders separately for the dates between 6/1/17-8/30/17.
For between those dates I wanted to get BidQty = 2, BidTotals =42747 (27123+15624);
OrderCommercial= 65230 (12570+29570+35660)
OrderResidential = 169033 (24591+36600+40122+22340+45320)
so I want to get something that looks like this:
Period BidQty BidTotals OrderCommcercial OrderResidential OrderQty
6/1/17-8/30/17 2 42747 65230 169033 4If there is any other way of doing it , i want some idea also.
Thank you
Your totals appear to be based on data that is outside of your date range. When you get that figured out, let us know.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 10, 2017 at 11:18 am
Here's what I used to try and get the data:CREATE TABLE #tblOrders (
OrderID int NOT NULL PRIMARY KEY,
JobStatus nvarchar(50) NOT NULL,
JobType nvarchar(50) NOT NULL,
BidDate datetime NULL,
BidTotals money NULL ,
Qty int NULL ,
OrderDate datetime NULL,
TaxAmount money NULL,
ContractAmount money NULL,
);
INSERT INTO
#tblOrders (OrderID, JobStatus, JobType, BidDate, BidTotals, Qty, OrderDate, TaxAmount, ContractAmount)
VALUES (101, 1, 'Complete', '05/10/2017', 24578, 1, '05/25/2017', 200, 24591),
(102, 2, 'Complete', '05/12/2017', 12564, 1, '05/30/2017', 150, 12570),
(103, 1, 'Bid', '06/05/2017', 27123, 1, NULL, 0, 0),
(104, 3, 'Bid', '06/09/2017', 15624, 1, NULL, 0, 0),
(105, 1, 'Complete', '06/12/2017', 36521, 1, '06/25/2017', 300, 36600),
(106, 2, 'Complete', '06/20/2017', 29563, 1, '07/05/2017', 100, 29570),
(107, 3, 'Complete', '07/05/2017', 40122, 1, '07/20/2017', 330, 40122),
(108, 1, 'Complete', '07/10/2017', 22333, 1, '07/25/2017', 150, 22340),
(109, 1, 'Complete', '07/12/2017', 45222, 1, '07/22/2017', 400, 45320),
(110, 2, 'Complete', '07/20/2017', 33654, 1, '08/10/2017', 120, 35660);
DECLARE @StartDate AS date = '2017-06-01';
DECLARE @EndDate AS date = '2017-08-30';
--I use the following select statement in the stored procedure for specific date ranges
SELECT CONVERT(varchar(10), @StartDate, 121) + ' - ' + CONVERT(varchar(10), @EndDate, 121) AS Period
, COUNT(CASE WHEN JobType= 'Bid' THEN QTY ELSE NULL END) AS BIDQty
, SUM(CASE WHEN JobType= 'Bid' THEN BidTotals ELSE 0 END) AS BIDTotals
, SUM(CASE WHEN JobStatus = 2 AND JobType ='Complete' THEN ContractAmount + TaxAmount ELSE 0 END) AS OrderCommercial
, SUM(CASE WHEN JobStatus IN (1, 3) AND JobType ='Complete' THEN ContractAmount + TaxAmount ELSE 0 END) AS OrderResidential
, COUNT(CASE WHEN JobStatus IN (1, 3) AND JobType ='Complete' THEN Qty ELSE NULL END) AS ORDERQty
FROM #tblOrders
WHERE (OrderDate BETWEEN @StartDate AND @EndDate OR OrderDate IS NULL)
OR BidDate BETWEEN @StartDate AND @EndDate
DROP TABLE #tblOrders;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 10, 2017 at 11:54 am
Thank you very much sgmunson, this seems to work. I also have another question If I want to get the average by dividing the OrderQty by the total number of months within the date range and want another column with the name AvgQty, how is it possible to do it here? Also If I want to do the whole thing for same date previous year, in a different row how do i do that.
October 12, 2017 at 12:08 pm
rosarozina - Tuesday, October 10, 2017 11:54 AMThank you very much sgmunson, this seems to work. I also have another question If I want to get the average by dividing the OrderQty by the total number of months within the date range and want another column with the name AvgQty, how is it possible to do it here? Also If I want to do the whole thing for same date previous year, in a different row how do i do that.
That's a rather different kettle of fish. I don't have time this week, but you're first task would be to at least create some sample data that will contain the additional year in the data, and someone should be able to assist. You'll probably either end up with a GROUP BY after creating a CTE to represent your data properly for that methodology, or end up with window clauses on your aggregates that all use the same PARTITION BY clause within the OVER clause that would be added.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply