Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
adbasanta
Posting Yak Master
120 Posts |
Posted - 2012-11-05 : 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 Order1,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)ASBegin 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 |
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2012-11-05 : 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)ASBegin 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,salestypeEND Now I got this result:transdate Total Salestype2012-10-01 10,000.00 Bad Order2012-11-02 5,000.00 Bad Order 2012-11-03 13,000.00 cash 2012-11-04 12,000.00 cash2012-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 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-05 : 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)ASBegin 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, salestypeEND Now I got this result:transdate Total Salestype2012-10-01 10,000.00 Bad Order2012-11-02 5,000.00 Bad Order 2012-11-03 13,000.00 cash 2012-11-04 12,000.00 cash2012-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
120 Posts |
Posted - 2012-11-05 : 05:26:46
|
OMG thanks webfred!I did not notice that simple thing!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
|
|
|
|
|