| Author |
 Topic  |
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
 Posted - 06/12/2012 : 15:48:48
|
Right now when I run the code below it gives me sales for each month based on the year I'm prompted for.
Is it possible to change the column headings to month+year(Jan2002, Feb2002 ....) Instead of (1,2,3.....)
ALTER PROCEDURE [dbo].[MonthlySales]
-- Add the parameters for the stored procedure here
@YEAR varchar (4)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
--DECLARE @YEAR numeric
--set @YEAR = @YYEAR
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(str(month(inv_dt)) )
FROM oehdrhst_sql
ORDER BY '],[' + ltrim(str(month(inv_dt)))
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query =
'
Declare @YEAR varchar
SELECT * FROM
(SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt
FROM oehdrhst_sql
where year(inv_dt)='+CAST(@year as varchar(4))+'
) src
PIVOT (SUM(tot_sls_amt) FOR OrderYear
IN ('+@listCol+')) AS pvt'
execute (@query)
END
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/12/2012 : 15:55:59
|
replace Month(inv_dt) with LEFT(Datename(mm,inv_dt),3) + datename(yyyy,inv_dt)
in above code on all places
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 06/12/2012 : 16:21:54
|
Made the changes. Get the following Error now.
Msg 8114, Level 16, State 5, Procedure MonthlySales, Line 22
Error converting data type nvarchar to float.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/12/2012 : 16:30:35
|
show the code
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 06/12/2012 : 16:39:14
|
ALTER PROCEDURE [dbo].[MonthlySales]
-- Add the parameters for the stored procedure here
@YEAR varchar (4)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
--DECLARE @YEAR numeric
--set @YEAR = @YYEAR
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(str( LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)) )
FROM oehdrhst_sql
ORDER BY '],[' + ltrim(str( LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)))
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query =
'
Declare @YEAR varchar
SELECT * FROM
(SELECT cus_no,slspsn_no, LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt) OrderYear, tot_sls_amt
FROM oehdrhst_sql
where year(inv_dt)='+CAST(@year as varchar(4))+'
) src
PIVOT (SUM(tot_sls_amt) FOR OrderYear
IN ('+@listCol+')) AS pvt'
execute (@query)
END
|
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/12/2012 : 16:56:57
|
what datatype is column inv_dt?
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 06/12/2012 : 16:58:57
|
| datetime |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/12/2012 : 17:32:11
|
which of the columns are of datatype nvarchar ?
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 06/12/2012 : 17:38:46
|
cus_no char20
slspsn_no int
tot_sls_amt decimal 16,2
Only varchar are the variables in the code.
|
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/12/2012 : 17:59:52
|
OrderYear has changed from being an int to varchar. could that be the issue. if you could post a few sample data it would help troubleshoot. in your initial post about parameters to this sproc where you were passing @year OrderYear was an int. But now things have changed. that error is misleading. post some oehdrhst_sql sample data
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/12/2012 : 19:06:20
|
use this instead
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)
FROM yeehaw
ORDER BY '],[' + LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)
FOR XML PATH('')
), 1, 2, '') + ']'
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/12/2012 : 20:13:17
|
whats the purpose of that variable inside EXEC? if you want to pass a parameter to dynamic sql string why not use sp_exeutesql?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
lisayling
Starting Member
China
5 Posts |
Posted - 06/13/2012 : 05:40:30
|
thanks, this topic is good, i can accept
unspammed
lisayling |
|
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 06/13/2012 : 09:07:22
|
yosiasz code seemed to kind of work. It lists the column heading like I wanted but the Year parameter seems to be ignored. It goes through and lists sales for every month and year.
I entered 2002 for the year when prompted and my resultes are below.
cus_no slspsn Apr2002 Feb2001 Feb2002 Jan2001 Jan2002 Mar2002 May2002
901 10975 NULL NULL 3369.50 NULL NULL NULL NULL
903 10975 NULL 8771.75 NULL 7485.00 11438.00 NULL 34247.95
904 10975 2173.63 NULL 5018.50 4385.01 NULL 5018.50 NULL
906 10975 NULL NULL NULL NULL 4924.88 6053.00 5153.00
908 10975 255.00 NULL 3050.00 1323.10 NULL NULL NULL
902 11052 NULL NULL 20291.64 NULL 6702.91 NULL NULL
905 11052 41061.20 NULL NULL 5181.20 6783.20 12808.60 4193.00
907 11052 11968.40 NULL NULL 1547.00 NULL 7475.00 6340.00
Here is the current code:
ALTER PROCEDURE [dbo].[MonthlySales]
-- Add the parameters for the stored procedure here
@YEAR varchar (4)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
--DECLARE @YEAR numeric
--set @YEAR = @YYEAR
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)
FROM oehdrhst_sql
ORDER BY '],[' + LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query =
'
Declare @YEAR varchar
SELECT * FROM
(SELECT cus_no,slspsn_no, LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt) OrderYear, tot_sls_amt
FROM oehdrhst_sql
where year(inv_dt)='+CAST(@year as varchar(4))+'
) src
PIVOT (SUM(tot_sls_amt) FOR OrderYear
IN ('+@listCol+')) AS pvt'
print (@query)
END
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/13/2012 : 09:32:16
|
ALTER PROCEDURE dbo.MonthlySales
(
@Year SMALLINT
)
AS
SET NOCOUNT ON
SET @Year = COALESCE(@Year, DATEPART(YEAR, GETDATE()))
DECLARE @FromDate DATE = DATEADD(YEAR, @Year - 1900, '19000101'),
@ToDate DATE = DATEADD(YEAR, @Year - 1900, '19010101')
DECLARE @ListCol VARCHAR(1000),
@Query VARCHAR(MAX)
SET @ListCol = STUFF((
SELECT ', ' + QUOTENAME(CONVERT(CHAR(3), MAX(inv_dt), 7) + DATENAME(YEAR, MAX(inv_dt)))
FROM dbo.oehdrhst_sql
WHERE inv_dt >= @FromDate
AND inv_dt < @ToDate
GROUP BY DATENAME(YEAR, inv_dt),
DATEPART(MONTH, inv_dt)
ORDER BY DATENAME(YEAR, inv_dt),
DATEPART(MONTH, inv_dt)
FOR XML PATH('')
), 1, 2, '')
SET @Query ='
SELECT p.*
FROM (
SELECT cus_no,
slspsn_no,
CONVERT(CHAR(3), inv_dt, 7) + DATENAME(YEAR, inv_dt) AS OrderYear,
tot_sls_amt
FROM dbo.oehdrhst_sql
WHERE inv_dt >= ' + QUOTENAME(CONVERT(CHAR(8), @FromDate, 112), '''') + '
AND inv_dt < ' + QUOTENAME(CONVERT(CHAR(8), @ToDate, 112), '''') + '
) AS s
PIVOT (
SUM(s.tot_sls_amt)
FOR s.OrderYear IN (' + @ListCol +')
) AS p'
PRINT @Query
-- EXEC (@Query)
N 56°04'39.26"
E 12°55'05.63" |
Edited by - SwePeso on 06/13/2012 10:35:11 |
|
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 06/13/2012 : 09:46:11
|
| What does that do for me???? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/13/2012 : 09:48:57
|
Everything you asked for.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 06/13/2012 : 10:22:26
|
You are correct. The first time I looked at the post it only had the first part of the code.
That works great.
What would I need to do if I wanted to prompt for a range? 2010 thru 2012
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/13/2012 : 10:22:29
|
quote:
Originally posted by Vack
What does that do for me????
test and see with your data
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/13/2012 : 10:37:35
|
ALTER PROCEDURE dbo.MonthlySales
(
@FromYear SMALLINT,
@ToYear SMALLINT
)
AS
SET NOCOUNT ON
SELECT @FromYear = COALESCE(@FromYear, DATEPART(YEAR, GETDATE())),
@ToYear = COALESCE(@ToYear, DATEPART(YEAR, GETDATE()))
DECLARE @FromDate DATE = DATEADD(YEAR, @FromYear - 1900, '19000101'),
@ToDate DATE = DATEADD(YEAR, @ToYear - 1900, '19010101')
DECLARE @ListCol VARCHAR(1000),
@Query VARCHAR(MAX)
SET @ListCol = STUFF((
SELECT ', ' + QUOTENAME(CONVERT(CHAR(3), MAX(inv_dt), 7) + DATENAME(YEAR, MAX(inv_dt)))
FROM dbo.oehdrhst_sql
WHERE inv_dt >= @FromDate
AND inv_dt < @ToDate
GROUP BY DATENAME(YEAR, inv_dt),
DATEPART(MONTH, inv_dt)
ORDER BY DATENAME(YEAR, inv_dt),
DATEPART(MONTH, inv_dt)
FOR XML PATH('')
), 1, 2, '')
SET @Query ='
SELECT p.*
FROM (
SELECT cus_no,
slspsn_no,
CONVERT(CHAR(3), inv_dt, 7) + DATENAME(YEAR, inv_dt) AS OrderYear,
tot_sls_amt
FROM dbo.oehdrhst_sql
WHERE inv_dt >= ' + QUOTENAME(CONVERT(CHAR(8), @FromDate, 112), '''') + '
AND inv_dt < ' + QUOTENAME(CONVERT(CHAR(8), @ToDate, 112), '''') + '
) AS s
PIVOT (
SUM(s.tot_sls_amt)
FOR s.OrderYear IN (' + @ListCol +')
) AS p'
PRINT @Query
-- EXEC (@Query)
N 56°04'39.26"
E 12°55'05.63" |
|
|
| |
Topic |
|
|
|
Change column names using xml Path
