October 9, 2017 at 8:57 am
I would like to write a stored procedure that use the date range for the different sum and count functions. The tblOrder table has two dates; orderdate and biddate. What I want to do is when @StartDate and @EndDate are passed I want it to be used as a filter for both the Orderdate and Biddate. And also After I got the totalOrder and TotalBid I want to divide it by the total month in the date range so that I could get the average.
I was trying to write something like:
CREATE PROC usp_SalesReport
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SELECT SUM(Order) As TotalOrder
, Count(Qty) As TotalQty
, Sum(Jobs) As TotalJobs
, Count(BidQty) As TotalBids
, TotalOrder/toalmonths As Average
From tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
END
I got nothing on the result. Can I use case statement in here? I tried it and again I got no result.
SELECT OrderID, Qty, OrderStatus,
CASE WHEN OrderStatus = 1 THEN (SELECT COUNT(Qty)) END AS QTY
, CASE WHEN OrderStatus= 3 THEN SUM(Jobs) END AS TotalJobs
FROM tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
GROUP BY OrderID, Qty, OrderStatus
Can anyone tell me what is the best way to do it? I need help pls. Thank you
October 9, 2017 at 10:01 am
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
If that criteria returned no rows, I would hazard a guess that there are no rows that have both the order date and bid date fall in your specified date range. You should probably troubleshoot that first.
October 9, 2017 at 10:30 am
There are actually rows within that date range
October 9, 2017 at 11:13 am
rosarozina - Monday, October 9, 2017 8:57 AMI would like to write a stored procedure that use the date range for the different sum and count functions. The tblOrder table has two dates; orderdate and biddate. What I want to do is when @StartDate and @EndDate are passed I want it to be used as a filter for both the Orderdate and Biddate. And also After I got the totalOrder and TotalBid I want to divide it by the total month in the date range so that I could get the average.
I was trying to write something like:
CREATE PROC usp_SalesReport
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SELECT SUM(Order) As TotalOrder
, Count(Qty) As TotalQty
, Sum(Jobs) As TotalJobs
, Count(BidQty) As TotalBids
, TotalOrder/toalmonths As Average
From tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
ENDI got nothing on the result. Can I use case statement in here? I tried it and again I got no result.
SELECT OrderID, Qty, OrderStatus,
CASE WHEN OrderStatus = 1 THEN (SELECT COUNT(Qty)) END AS QTY
, CASE WHEN OrderStatus= 3 THEN SUM(Jobs) END AS TotalJobs
FROM tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
GROUP BY OrderID, Qty, OrderStatus
Can anyone tell me what is the best way to do it? I need help pls. Thank you
Sort of amazed that you didn't get an error message. Can you provide the DDL (CREATE TABLE statement) for the table and some sample data (INSERT INTO statements) and how you would call the procedure based on the sample data and what you would expect to be returned?
October 9, 2017 at 11:47 am
rosarozina - Monday, October 9, 2017 10:30 AMThere are actually rows within that date range
If there were, then you would be getting records. You may have records where the OrderDate is in that date range and you may have records where the BidDate is in that date range, but you have no records where BOTH the OrderDate and the BidDate are in that date range. I suspect that you want records where EITHER date is in the range, not just the ones where BOTH dates are in the range.
This is why we ask that people supply sample data and expected results. We can't see your data, so we are only guessing at where the problem is and what the solution is.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 9, 2017 at 2:33 pm
how do I post the sample date. I can post the create statement but the insert into will be too much. If I just insert a sample data, it might not have the desired outcome. Can you suggest a way?
October 9, 2017 at 2:39 pm
rosarozina - Monday, October 9, 2017 2:33 PMhow do I post the sample date. I can post the create statement but the insert into will be too much. If I just insert a sample data, it might not have the desired outcome. Can you suggest a way?
You do understand that sample data means just that, not production data. Build a set of data that is representative of your existing data but isn't live data. We only need about 20 rows of data that represents the live data, just not as much.
October 10, 2017 at 2:01 am
rosarozina - Monday, October 9, 2017 2:33 PMhow do I post the sample date. I can post the create statement but the insert into will be too much. If I just insert a sample data, it might not have the desired outcome. Can you suggest a way?
Check this Forum Etiquette: How to post data/code on a forum to get the best help
October 10, 2017 at 2:12 am
chances are you didn't get a result because tblOrders may exist in multiple schema !
Best practise: always schema-qualify your objects !
Select ...
from yourschemaname.yourobjectname
where ...
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 10, 2017 at 9:39 am
CREATE TABLE [dbo].[tblOrders](
[OrderID] [int] NOT NULL,
[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,
PRIMARY KEY (OrderID) )
INSERT INTO tblOrders (OrderID, JobStatus, JobType, BidDate, BidTotals, Qty, OrderDate, TaxAmount, ContractAmount)
VALUES (101,1,'Complete','5/10/17',24578,1,'5/25/17',200,24591),
(102,2,'Complete','5/12/17',12564,1,'5/30/17',150,12570),
(103,1,'Bid','6/5/17',27123,1,'',0,0),
(104,3,'Bid', '6/9/17',15624,1,'',0,0),
(105,1,'Complete', '6/12/17',36521,1,'6/25/17',300,36600),
(106,2,'Complete','6/20/17',29563,1,'7/5/17',100,29570),
(107,3,'Complete','7/5/17',40122,1,'7/20/17',330,40122),
(108,1,'Complete','7/10/17',22333,1,'7/25/17',150,22340),
(109,1,'Complete','7/12/17',45222,1,'7/22/17', 400, 45320),
(110,2,'Complete','7/20/17',33654,1,'8/10/17',120,35660)
I use the following select statement in the stored procedure for specific date ranges
SELECT OrderID, JobStatus, JobType, BidDate,BidTotals,Qty,OrderDate,TaxAmount,ContractAmount,
CASE WHEN JobStatus = 2 AND JobType ='Complete' THEN (SELECT SUM(ContractAmount + TaxAmount)) END AS OrderCommercial
, CASE WHEN JobStatus in (1,3) AND JobType ='Complete' THEN (SELECT SUM(ContractAmount + TaxAmount)) END AS OrderResidential
, CASE WHEN JobStatus in (1,3) AND JobType ='Complete' THEN (SELECT COUNT(Qty) END AS ORDERQty
, CASE WHEN JobTpe= 'Bid' THEN (SELECT SUM(BidTotal)) END AS BIDS
, CASE WHEN JobTpe= 'Bid' THEN (SELECT COUNT(QTY) END AS BIDQty
FROM tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
END
October 10, 2017 at 10:01 am
rosarozina - Tuesday, October 10, 2017 9:39 AMCREATE TABLE [dbo].[tblOrders](
[OrderID] [int] NOT NULL,
[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,
PRIMARY KEY (OrderID) )INSERT INTO tblOrders (OrderID, JobStatus, JobType, BidDate, BidTotals, Qty, OrderDate, TaxAmount, ContractAmount)
VALUES (101,1,'Complete','5/10/17',24578,1,'5/25/17',200,24591),
(102,2,'Complete','5/12/17',12564,1,'5/30/17',150,12570),
(103,1,'Bid','6/5/17',27123,1,'',0,0),
(104,3,'Bid', '6/9/17',15624,1,'',0,0),
(105,1,'Complete', '6/12/17',36521,1,'6/25/17',300,36600),
(106,2,'Complete','6/20/17',29563,1,'7/5/17',100,29570),
(107,3,'Complete','7/5/17',40122,1,'7/20/17',330,40122),
(108,1,'Complete','7/10/17',22333,1,'7/25/17',150,22340),
(109,1,'Complete','7/12/17',45222,1,'7/22/17', 400, 45320),
(110,2,'Complete','7/20/17',33654,1,'8/10/17',120,35660)I use the following select statement in the stored procedure for specific date ranges
SELECT OrderID, JobStatus, JobType, BidDate,BidTotals,Qty,OrderDate,TaxAmount,ContractAmount,
CASE WHEN JobStatus = 2 AND JobType ='Complete' THEN (SELECT SUM(ContractAmount + TaxAmount)) END AS OrderCommercial
, CASE WHEN JobStatus in (1,3) AND JobType ='Complete' THEN (SELECT SUM(ContractAmount + TaxAmount)) END AS OrderResidential
, CASE WHEN JobStatus in (1,3) AND JobType ='Complete' THEN (SELECT COUNT(Qty) END AS ORDERQty
, CASE WHEN JobTpe= 'Bid' THEN (SELECT SUM(BidTotal)) END AS BIDS
, CASE WHEN JobTpe= 'Bid' THEN (SELECT COUNT(QTY) END AS BIDQty
FROM tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
END
So what date range should we use and what is the expected results vs the actual results?
October 10, 2017 at 10:07 am
You didn't specify what start and end dates you used. If you don't supply dates, then the parameters will default to NULL and no records will qualify.
You should also provide expected results, so we know how close we are to what you expect.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2017 at 10:09 am
Once I correct most of the errors in your code it still errors with this, which I expected:
Msg 8120, Level 16, State 1, Line 30
Column 'tblOrders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Based on the sample data what are you expecting to have returned and based on what date range?
October 10, 2017 at 10:42 am
For 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 4
If there is any other way of doing it , i want some idea also.
Thank you
October 10, 2017 at 10:54 am
Looks like this is what's needed, but the original poster will have to validate and supply his or her own start and end date parameters: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, '', 0, 0),
(104,3, 'Bid', '06/09/2017', 15624, 1, '', 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-05-01';
DECLARE @EndDate AS date = '2017-06-30';
--I use the following select statement in the stored procedure for specific date ranges
SELECT OrderID, JobStatus, JobType, BidDate, BidTotals, Qty, OrderDate, TaxAmount, ContractAmount,
CASE WHEN JobStatus = 2 AND JobType ='Complete' THEN SUM(ContractAmount + TaxAmount) END AS OrderCommercial
, CASE WHEN JobStatus in (1,3) AND JobType ='Complete' THEN SUM(ContractAmount + TaxAmount) END AS OrderResidential
, CASE WHEN JobStatus in (1,3) AND JobType ='Complete' THEN COUNT(Qty) END AS ORDERQty
, CASE WHEN JobType= 'Bid' THEN SUM(BidTotals) END AS BIDS
, CASE WHEN JobType= 'Bid' THEN COUNT(QTY) END AS BIDQty
FROM #tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND BidDate BETWEEN @StartDate AND @EndDate
GROUP BY OrderID, JobStatus, JobType, BidDate, BidTotals, Qty, OrderDate, TaxAmount, ContractAmount;
DROP TABLE #tblOrders;
Bad guess... the OP piped up before I got this posted.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply