| Author |
 Topic  |
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
 Posted - 07/17/2012 : 13:03:12
|
How can I make this query part of a stored procedure so that I can prompt for month and year?
SELECT TOP (100) PERCENT dbo.cicmpy.SalesPersonNumber, dbo.arslmfil_SQL.slspsn_name, dbo.oehdrhst_sql.cus_no, SUM(dbo.oehdrhst_sql.tot_sls_amt)
AS TotalSales, SUM(dbo.oehdrhst_sql.tot_cost) AS TotalCost
FROM dbo.arslmfil_SQL INNER JOIN
dbo.cicmpy ON dbo.arslmfil_SQL.humres_id = dbo.cicmpy.SalesPersonNumber RIGHT OUTER JOIN
dbo.oehdrhst_sql ON dbo.cicmpy.debcode = dbo.oehdrhst_sql.cus_no
WHERE (YEAR(dbo.oehdrhst_sql.inv_dt) = 2012) AND (MONTH(dbo.oehdrhst_sql.inv_dt) = 6)
GROUP BY dbo.oehdrhst_sql.slspsn_no, dbo.oehdrhst_sql.cus_no, dbo.arslmfil_SQL.slspsn_name, dbo.cicmpy.SalesPersonNumber
ORDER BY dbo.cicmpy.SalesPersonNumber
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/17/2012 : 13:17:01
|
create prod myproc
@year int ,
@month int
as
SELECT TOP (100) PERCENT dbo.cicmpy.SalesPersonNumber, dbo.arslmfil_SQL.slspsn_name, dbo.oehdrhst_sql.cus_no, SUM(dbo.oehdrhst_sql.tot_sls_amt)
AS TotalSales, SUM(dbo.oehdrhst_sql.tot_cost) AS TotalCost
FROM dbo.arslmfil_SQL INNER JOIN
dbo.cicmpy ON dbo.arslmfil_SQL.humres_id = dbo.cicmpy.SalesPersonNumber RIGHT OUTER JOIN
dbo.oehdrhst_sql ON dbo.cicmpy.debcode = dbo.oehdrhst_sql.cus_no
WHERE (YEAR(dbo.oehdrhst_sql.inv_dt) = @year) AND (MONTH(dbo.oehdrhst_sql.inv_dt) = @month)
GROUP BY dbo.oehdrhst_sql.slspsn_no, dbo.oehdrhst_sql.cus_no, dbo.arslmfil_SQL.slspsn_name, dbo.cicmpy.SalesPersonNumber
ORDER BY dbo.cicmpy.SalesPersonNumber
go
The prompting will be done by your client before the SP call.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 07/17/2012 : 13:18:24
|
create proc procName
(@date datetime) --pass in the first day of the month/year here
as
SELECT TOP (100) PERCENT dbo.cicmpy.SalesPersonNumber, dbo.arslmfil_SQL.slspsn_name, dbo.oehdrhst_sql.cus_no, SUM(dbo.oehdrhst_sql.tot_sls_amt)
AS TotalSales, SUM(dbo.oehdrhst_sql.tot_cost) AS TotalCost
FROM dbo.arslmfil_SQL INNER JOIN
dbo.cicmpy ON dbo.arslmfil_SQL.humres_id = dbo.cicmpy.SalesPersonNumber RIGHT OUTER JOIN
dbo.oehdrhst_sql ON dbo.cicmpy.debcode = dbo.oehdrhst_sql.cus_no
WHERE oehdrhst_sql.inv_dt >= @date AND oehdrhst_sql.inv_dt < DATEADD(mm, 1, @date)
--WHERE (YEAR(dbo.oehdrhst_sql.inv_dt) = 2012) AND (MONTH(dbo.oehdrhst_sql.inv_dt) = 6)
GROUP BY dbo.oehdrhst_sql.slspsn_no, dbo.oehdrhst_sql.cus_no, dbo.arslmfil_SQL.slspsn_name, dbo.cicmpy.SalesPersonNumber
ORDER BY dbo.cicmpy.SalesPersonNumber
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/17/2012 : 14:53:26
|
even if @month and @year are parameters you can do like this
.....
WHERE dbo.oehdrhst_sql.inv_dt >= DATEADD(mm,@month-1,DATEADD(yy,@year-1900,0))
AND dbo.oehdrhst_sql.inv_dt < DATEADD(mm,@month,DATEADD(yy,@year-1900,0))
...
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
|
| |
Topic |
|
Stored Procedure
