Week, Month Year Parameter - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Week, Month Year Parameter

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-06-10 : 14:28:53

My boss would like to see one report with parameter promts. I tried doing this, but I keep getting an error. Can someone help me out here?

Thanks.

WHERE
(@YEAR(CLM_Dout) = YEAR(GetDate()))or
(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND MONTH(CLM_DOUT) = MONTH(GetDate()))or
(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-10 : 14:34:02

Is this related to SSRS?
What is the error you are getting?

(@YEAR(CLM_Dout) should be (YEAR(CLM_Dout)

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-10 : 14:34:06

What are you trying to do with @YEAR(CLM_Dout)? What is @YEAR?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-06-10 : 14:45:59

I apologize, what I am trying to do is put a parameter prompt that pops up to either select each of those formulas (week, month, year). I am actually doing this through sharePoint. If I do the formulas separate they work, but I want to put the in one report so I can pick If I want to see the report by Week, Month or year.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-10 : 14:49:01

I haven't a clue about SharePoint. You can't prompt for user input in T-SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-06-18 : 08:32:20

Hi I posted this a little back, but I wanted to activate this again, This time I would like to have this prompt maybe in a procedure code that comes up on my report? I don't even know if this can be done.

What I need to do is have 3 different prompts that I can choose from. If I wanted to just check the for the week I can,If I wanted to just check the Month I can or the year. Right now I have 3 separate reports, but I want to consolidate them. Everything is the same except the Week, Month Year parameter See the parameter below.

(@YEAR(CLM_Dout) = YEAR(GetDate()))or

(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND MONTH(CLM_DOUT) = MONTH(GetDate()))or

(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 09:24:45

[code]CREATE PROCEDURE dbo.uspMyReport
(
@Year SMALLINT,
@Month TINYINT,
@Week TINYINT
)
AS

SET NOCOUNT ON

SELECT *
FROM MyTable
WHERE (DATEPART(YEAR, MyCol) = @Year OR @Year IS NULL)
AND (DATEPART(MONTH, MyCol) = @Month OR @Month IS NULL)
AND (DATEPART(WEEK, MyCol) = @Week OR @Week IS NULL)[/code]

E 12°55'05.25"
N 56°04'39.16"

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-06-19 : 08:29:53

Ok I added in the formula, but I am getting an error and I don't know what it means, can you please help me? I am getting an error that says.....Must declare the scalar variable "@Year".

Select
@Year SMALLINT,
@Month TINYINT,
@Week TINYINT,

clm_id1, CONVERT(CHAR(10),
clm_rcvd, 110) AS Daterecvd,
CONVERT(CHAR(10),
--CLM_DOUT, 110) AS dateclosed,
clm_wkpct,
clm_1a,
clm_12a,
clm_12b, clm_55d,
clm_clir, clm_65a,
clm_medb2,
clm_tchg,
clm_base,
clm_stades,
clm_prod,
clm_nego,
clm_sppo,
clm_1e,
Note,
AccessFeeFinal,
CLM_ATT2,
CLM_ATT3,
ACCESSFEEIMPACT,
CAST(clm_sppo / clm_tchg * 100 AS decimal(5, 2)) AS PercentSavings,
MAS90#,
clm_meda4 AS OriginalAllow,
CLM_H30 AS AdjustedTotalSavings

From All_Clients_Discount_Management_DEVON_ADV

WHERE
(DATEPART(YEAR, clm_dout) = @Year OR @Year IS NULL) AND
(DATEPART(MONTH, clm_dout) = @Month OR @Month IS NULL) AND
(DATEPART(WEEK,clm_dout) = @Week OR @Week IS NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 08:52:27

Select
@Year ~~SMALLINT~~,
@Month ~~TINYINT~~,
@Week ~~TINYINT~~,

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 08:55:02

[code]SELECT @Year AS Year,
@Month AS Month,
@Week AS Week,
clm_id1,
CONVERT(CHAR(10), clm_rcvd, 110) AS Daterecvd,
CONVERT(CHAR(10), CLM_DOUT, 110) AS dateclosed,
clm_wkpct,
clm_1a,
clm_12a,
clm_12b,
clm_55d,
clm_clir,
clm_65a,
clm_medb2,
clm_tchg,
clm_base,
clm_stades,
clm_prod,
clm_nego,
clm_sppo,
clm_1e,
Note,
AccessFeeFinal,
CLM_ATT2,
CLM_ATT3,
ACCESSFEEIMPACT,
CAST(100.0 * clm_sppo / clm_tchg AS DECIMAL(5, 2)) AS PercentSavings,
[MAS90#],
clm_meda4 AS OriginalAllow,
CLM_H30 AS AdjustedTotalSavings
FROM All_Clients_Discount_Management_DEVON_ADV
WHERE (DATEPART(YEAR, clm_dout) = @Year OR @Year IS NULL)
AND (DATEPART(MONTH, clm_dout) = @Month OR @Month IS NULL)
AND (DATEPART(WEEK, clm_dout) = @Week OR @Week IS NULL)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources