March 18, 2009 at 7:15 am
revised:
I have an SQL query that looks like this:
the only thing in these queries that is different is the month parameter, is there any way to make this entire query dynamic so I don't have to create 2 seperate queries if YTD (year to date) is 0 but still have the data come out like it is? Is there a way to make the where clause dynamic in general?
if @YTD = 0
Begin
select x.salesYr, x.Group from (select salesYr,
CommericalSales = ISNULL((Select (SUM(Sales) from tblsSales where group = 'Commercial' and salesYear = y.SalesYear and salesMonth <= @month and Pending = @PendingDeals)),0),
ResSales = = ISNULL((Select (SUM(Sales) from tblsSales where group = 'Res' and salesYear = y.SalesYear and salesMonth <= @month and Pending = @PendingDeals)),0)
from tblSales y where
salesYr in (@CYear, @PYear)
Group By salesYear) as x
End
Else
select x.salesYr, x.Group from (select salesYr,
CommericalSales = ISNULL((Select (SUM(Sales) from tblsSales where group = 'Commercial' and salesYear = y.SalesYear and salesMonth = @month and Pending = @PendingDeals)),0),
ResSales = = ISNULL((Select (SUM(Sales) from tblsSales where group = 'Res' and salesYear = y.SalesYear and salesMonth = @month and Pending = @PendingDeals)),0)
from tblSales y where
salesYr in (@CYear, @PYear)
Group By salesYear) as x
end
March 18, 2009 at 7:41 am
select x.salesYr, x.Group from (select salesYr,
CommericalSales = ISNULL((Select (SUM(Sales) from tblsSales where group = 'Commercial' and salesYear = y.SalesYear
and ( salesMonth 0 )
and Pending = @PendingDeals)),0),
ResSales = = ISNULL((Select (SUM(Sales) from tblsSales where group = 'Res' and salesYear = y.SalesYear and salesMonth <= @month and Pending = @PendingDeals)),0)
from tblSales y where
salesYr in (@CYear, @PYear)
Group By salesYear) as x
You may use the logic like in the bolded part of the code.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 7:45 am
salesMonth 0
The bolded code should be like this. For whatever reason the forum's editor messed up the code when it got posted.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 7:48 am
salesMonth lessorequal @month and YTD = 0 OR salesMonth = @month and YTD notequal 0
S....:angry:
I am having problems with posting the correct code so I used words lessorequal and notequal instead of the operators
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply