CODEWITHSUNDEEP
sqlsql-serverdatetimestored-procedures
John Batdorf
John Batdorf

Reputation: 2542

What is the SQL Server syntax to sum sales for s certain day using a stored procedure parameter for date range?

Here's my table definition:

tblTrans

PK - trans_id (int)
   - user_id_fk (int)
   - trans_created (datetime)
   - trans_amount (money)

I want to sum all sales data for a given day, and return it as NumberOfTransactions,TotalDollars

I have the following SQL query that I want to move to a stored procedure that takes MM/DD/YYYY as a parameter.

The query works, I'm just not sure how to pass in the parameter.

SELECT Cast(count(*) as varchar(8)) + ','
                         +  Cast(Sum(trans_amount) as varchar(8)) as SalesData 
FROM PD_Transaction 
WHERE pd_Trans_CreatedOn BETWEEN '20130122' 00:00:00.000' AND '20130122 23:59:59.997'

I want to create this as a stored procedure, and pass in just the date.

Thank you!

Upvotes: 1

Views: 504

Answers (3)

cha
cha

Reputation: 10411

If passing one date only:

CREATE PROC P @Date DATE
AS BEGIN 
    SELECT Cast(count(*) as varchar(8)) + ',' +  Cast(Sum(trans_amount) as varchar(8)) as SalesData FROM PD_Transaction WHERE pd_Trans_CreatedOn
    BETWEEN CONVERT(datetime, CONVERT(VARCHAR(10), @Date, 121) + ' 00:00:00', 121) and CONVERT(datetime, CONVERT(VARCHAR(10), @Date, 121) + ' 23:59:59', 121)
END

Upvotes: 1

peanut
peanut

Reputation: 1482

You can declare two parameters in the sproc, @StartDate and @EndDate etc. In your code, assign the parameter with 

DateTime dtStart = new DateTime(2013,01,22);
DateTime dtEnd= new DateTime(2013,01,23);

Then the sql script will be something like 

*.....FROM PD_Transaction WHERE pd_Trans_CreatedOn >= @StartDate AND pd_Trans_CreatedOn < @EndDate*

Upvotes: 0

Iswanto San
Iswanto San

Reputation: 18569

I quess you using SQL Server as RDBMS from your query. Here is the stored proc : 

CREATE PROC P @startDate DATE, @endDate DATE
AS BEGIN 
    SELECT Cast(count(*) as varchar(8)) + ',' +  Cast(Sum(trans_amount) as varchar(8)) as SalesData FROM PD_Transaction WHERE pd_Trans_CreatedOn
    BETWEEN @startDate and @endDate
END

Upvotes: 0

Related Questions