如何避免查询中的HardCoded值 [英] How to Avoid the HardCoded values in my Query

问题描述

大家好，



这是我的选择查询结果..

Hi Everybody,

This is my Select query result..

Proj_Code     Material_Id	  Planned_Date	          Planned_Material_Qty
O11001	           2	          11/2/2012 0:00	  300
O11001	           2	          12/2/2012 0:00	  200
O11001	           2 	          1/2/2013 0:00	          150
O11001	           2	          2/2/2013 0:00	          100
O11001	           2	          3/2/2013 0:00	          50
O11001	           4	          11/2/2012 0:00	  300
O11001	           4	          12/2/2012 0:00	  200
O11001	           4	          1/2/2013 0:00	          100
O11001	           4	          2/2/2013 0:00	          80
O11001	           4	          3/2/2013 0:00	          50

我想显示上个月的数量，累积月份（上）和& cuurent month qty。

对于我的req，我写了这样的查询...

I want to display previous month quantity, cummulative month (prev) & cuurent month qty.
For my req i wrote query like this...

Select TT.Proj_Code,TT.Material_Id,TT.DED_Description,TT.DED_Short_Desc,
	Max(case when MONTH(TT.Planned_Date) =MONTH(getdate()) then
	TT.Planned_Date end) as Planned_Date,
	sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())-1) then
	TT.Planned_Material_Qty else 0 end) as PreviousMonth,
	sum(isnull(TT.Planned_Material_Qty,0)) as CummulativeProgress,
	sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())) then
	TT.Planned_Material_Qty else 0 end) as CurrentMonth
	from #TempTable TT
	where 
	MONTH(TT.Planned_Date) in (12,1,2) and YEAR(TT.Planned_Date) in (2012,2013) 	
	group by TT.Proj_Code,TT.Material_Id,TT.DED_Description,TT.DED_Short_Desc

现在，这是什么问题是，在这个查询条件我通过月份和年份值是硬编码但我不想这样基于开始日期和getdate，我想获取信息..



例如：想象一下，如果我的计划是在2001年开始的，那么它仍在继续。然后在哪里条件如何给出值（2001,2002,2003 .... 2013）这是正确的格式正确..？

这是'我的方式我问我如果不使用硬编码值，不要求硬编码值如何获得相同的结果。



感谢''s in Advance ...

Now, what''s my question is, in this query in where condition i pass the month and year values are hard coded but i don''t want like this based on start date and getdate , i want to fetch the information..

for ex: imagine if my Planning is started in the year of 2001 and still it''s continued. Then in where condition how to given the values ( 2001,2002,2003....2013) this is not in correct format right..?
that''s way i''m asking i don''t want hard coded values without using hardcoded values how to achieve the same result.

Thank''s In Advance...

推荐答案

请参阅： http://msdn.microsoft.com/ en-us / library / ms254953.aspx [ ^ ]。

-SA

试试这样....

我认为它会帮助你:-)

Hi,

Try like this....
I think It Will helps U. :-)

Select TT.Proj_Code,TT.Material_Id,TT.DED_Description,TT.DED_Short_Desc,
	Max(case when MONTH(TT.Planned_Date) =MONTH(getdate()) then
	TT.Planned_Date end) as Planned_Date,
	sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())-1) then
	TT.Planned_Material_Qty else 0 end) as PreviousMonth,
	sum(isnull(TT.Planned_Material_Qty,0)) as CummulativeProgress,
	sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())) then
	TT.Planned_Material_Qty else 0 end) as CurrentMonth
from #TempTable TT
where MONTH(TT.Planned_Date) in (MONTH(GETDATE()),MONTH(GETDATE())-1,MONTH(GETDATE())-2) 
AND (YEAR(TT.Planned_Date) = YEAR(GETDATE()) OR (MONTH(GETDATE())<3 AND YEAR(TT.Planned_Date)=YEAR(GETDATE())-1))

问候，

GVPrabu

Regards,
GVPrabu

这篇关于如何避免查询中的HardCoded值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！