Query pulling same nos. for different parameters?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Query pulling same nos. for different parameters??

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-17 : 12:24:34

Hi,

The below query needs to pulls data for the last month and the month before that depending on the startdate.
So, if I enter startDate ='11/15/2008', it should shows me data for Oct 08 and Sept 08.
if I enter startDate ='9/15/2008' it should show me data for Aug 08 and Jul 08.

The query that I have is not changing the numbers for the "last month", "Two_months_ago".
even if I enter diffrent dates in the start_date, it just shows the same numbers.
Is somthing wrong in the logic ?

Declare @Get_Date datetime
, @StartDate Varchar(30)

select @StartDate = '11/1/2008'

Select @Get_Date = convert(varchar ,month(Getdate()) )+ '/01/' +convert(varchar, year(Getdate()))

select distinct i.Type, i.Bill_Desc, i.group Product_Group
,isnull(Union.Last_Month,0) Last_Month , isnull(Union.Two_Months_Ago,0) Two_Months_Ago
from v_customer i
left join
(select Type, Bill_Desc, group, sum(Last_Month) 'Last_Month', sum(Two_Months_Ago) 'Two_Months_Ago'
from
(select Type, Bill_Desc, group, Mon_Num,
Case when Mon_Num = 'MON1' then sum(Tot_Count) else '' end 'Last_Month',
Case when Mon_Num = 'MON2' then sum(Tot_Count) else '' end 'Two_Months_Ago',

From
(
select 'MON1' Mon_Num, isnull(count(*),0) Tot_Count, Type, Bill_Desc, group
from v_customer
where order_date >= dateadd(m,-1,@Get_Date) and order_date < @Get_Date
Group by Type, Bill_Desc, group

Union all

select 'MON2' Mon_Num, isnull(count(*),0) Tot_Count, Type, Bill_Desc, group
from v_customer
where order_date >= dateadd(m,-2,@Get_Date) and order_date < dateadd(m,-1,@Get_Date)
Group by Type, Bill_Desc, group

Group by Type, Bill_Desc, group
) MonthlyTable1
Group by Mon_Num, Type, Bill_Desc, group
) MonthlyTable2
Group by Type, Bill_Desc, group
) Union
On i.Type = Union.Type and i.Bill_Desc = Union.Bill_Desc and i.group = Union.group

Where isnull(Union.Last_Month,0)+ isnull(Union.Two_Months_Ago,0)
> 0
and i.Customer_Type <> 'ABC'

Thanks for looking,
Petronas.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 12:29:51

dont use varchar for dates. treat it as date itself

Declare @Get_Date datetime
, @StartDate datetime

select @StartDate = '11/1/2008'

Select @Get_Date = dateadd(mm,datediff(mm,0,@StartDate),0)

select distinct i.Type, i.Bill_Desc, i.group AS Product_Group
,isnull(Union.Last_Month,0) AS Last_Month , isnull(Union.Two_Months_Ago,0) AS Two_Months_Ago
from v_customer i
left join
(select Type, Bill_Desc, group, sum(Last_Month) 'Last_Month', sum(Two_Months_Ago) 'Two_Months_Ago'
from
(select Type, Bill_Desc, group, Mon_Num,
Case when Mon_Num = 'MON1' then sum(Tot_Count) else '' end 'Last_Month',
Case when Mon_Num = 'MON2' then sum(Tot_Count) else '' end 'Two_Months_Ago',

From
(
select 'MON1' Mon_Num, isnull(count(*),0) Tot_Count, Type, Bill_Desc, group
from v_customer
where order_date >= dateadd(m,-1,@Get_Date) and order_date < @Get_Date
Group by Type, Bill_Desc, group

Union all

select 'MON2' Mon_Num, isnull(count(*),0) Tot_Count, Type, Bill_Desc, group
from v_customer
where order_date >= dateadd(m,-2,@Get_Date) and order_date < dateadd(m,-1,@Get_Date)
Group by Type, Bill_Desc, group


Group by Type, Bill_Desc, group
) MonthlyTable1
Group by Mon_Num, Type, Bill_Desc, group
) MonthlyTable2
Group by Type, Bill_Desc, group
) Union
On i.Type = Union.Type and i.Bill_Desc = Union.Bill_Desc and i.group = Union.group

Where isnull(Union.Last_Month,0)+ isnull(Union.Two_Months_Ago,0)
> 0
and i.Customer_Type <> 'ABC'

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-17 : 12:53:47

Thanks, again for your help. It worked when I did not use varchar. But in the report I have to define startdate as varchar(string) because it takes two parmaeters either 'M' (last month) or you can enter the date. Is there anyone that I can keep startdate as varchar and convert the query . I beleive I need to convert just this part for it to work

Select @Get_Date = dateadd(mm,datediff(mm,0,@StartDate),0)
Truly appreciate your help,
Petronas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources