| Author |
 Topic  |
|
|
adbasanta
Posting Yak Master
108 Posts |
 Posted - 11/05/2012 : 04:05:17
|
Good day!
I want to generate the Sum of Sales base on a given date by Salestpye but it seems that the query does not recognize the column (totalsales,salestype) even if its correct. In short the result must be:
cash credit pay Bad Order
1,000.00 4,000.00 3,000.00 6,000.00
Here is my code:
CREATE PROCEDURE [dbo].[sp_Get_Sumof_Sales]
-- Add the parameters for the stored procedure here
@fromdate AS varchar(50), @todate AS varchar(50)
AS
Begin
SET NOCOUNT ON;
Select transdate,totalsales,salestype FROM tbl_pos_sales_summary
PIVOT (SUM(totalsales) FOR salestype IN ([cash], [credit], [pay], [Bad Order])) as SumofSales
WHERE (CONVERT(Date, transdate)) BETWEEN @fromdate AND @todate
GROUP BY transdate,totalsales,salestype
END
Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
Edited by - adbasanta on 11/05/2012 04:59:42
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 11/05/2012 : 04:51:44
|
Ive modified the query to this to make it simple without pivot: How can i make the the salestype in one line?
ALTER PROCEDURE [dbo].[sp_Get_Sumof_Sales]
-- Add the parameters for the stored procedure here
@fromdate AS varchar(50), @todate AS varchar(50)
AS
Begin
SET NOCOUNT ON;
SELECT transdate,SUM(totalsales) AS Total,salestype FROM tbl_pos_sales_summary
WHERE (CONVERT(Date, transdate)) BETWEEN @fromdate AND @todate
GROUP BY transdate,salestype
END
Now I got this result:
transdate Total Salestype
2012-10-01 10,000.00 Bad Order
2012-11-02 5,000.00 Bad Order
2012-11-03 13,000.00 cash
2012-11-04 12,000.00 cash
2012-11-04 4,000.00 credit
2012-11-04 5,000.00 credit
So the result must be:
Total Salestype
15,000.00 Bad Order
25,000.00 cash
9,000.00 credit
And I dont want to display the date column.
Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
Edited by - adbasanta on 11/05/2012 04:54:08 |
 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 11/05/2012 : 05:24:44
|
quote:
Originally posted by adbasanta
Ive modified the query to this to make it simple without pivot: How can i make the the salestype in one line?
ALTER PROCEDURE [dbo].[sp_Get_Sumof_Sales]
-- Add the parameters for the stored procedure here
@fromdate AS varchar(50), @todate AS varchar(50)
AS
Begin
SET NOCOUNT ON;
SELECT transdate, SUM(totalsales) AS Total,salestype FROM tbl_pos_sales_summary
WHERE (CONVERT(Date, transdate)) BETWEEN @fromdate AND @todate
GROUP BY transdate, salestype
END
Now I got this result:
transdate Total Salestype
2012-10-01 10,000.00 Bad Order
2012-11-02 5,000.00 Bad Order
2012-11-03 13,000.00 cash
2012-11-04 12,000.00 cash
2012-11-04 4,000.00 credit
2012-11-04 5,000.00 credit
So the result must be:
Total Salestype
15,000.00 Bad Order
25,000.00 cash
9,000.00 credit
And I dont want to display the date column.
Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Too old to Rock'n'Roll too young to die. |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 11/05/2012 : 05:26:46
|
OMG thanks webfred!
I did not notice that simple thing!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
|
|
| |
Topic |
|
|
|
Summarizing Sum by Sales Type in One Line..?
