@variable problems

[mossbs]

Hi guys,

Trying to pull a set of data from the database where member.join_date does not match up with a 'renewal month' attribute that they have on their record.

ie. if a member's join_date is 09/02/2010 their renewal attribute should be February.

My problem lies in that member.join_date is datetime
and attribute.attr_code_ref is and int and just a reference number to an attribute that is a certain month...see below

2079 JANUARY
2080 FEBRUARY
2081 MARCH
2082 APRIL
2083 MAY
2084 JUNE
2085 JULY
2086 AUGUST
2087 SEPTEMBER
2088 OCTOBER
2089 NOVEMBER
2090 DECEMBER


i think i need to set up a variable and use statements such as

if a.attr_code_ref = 2083 set @month = 5

and then later on use where clause of

where month(member.join_date) != @month

however... this will work on a single month but i need the @month variable to be... well variable.

any ideas? (aplogies if none of the above makes sense...!)

cheers guys,

Dan

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], [COLOR=#FF00FF]MonthName[/color] [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2079,[COLOR=red]'JANUARY'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2080,[COLOR=red]'FEBRUARY'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2081,[COLOR=red]'MARCH'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2082,[COLOR=red]'APRIL'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2083,[COLOR=red]'MAY'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2084,[COLOR=red]'JUNE'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2085,[COLOR=red]'JULY '[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2086,[COLOR=red]'AUGUST '[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2087,[COLOR=red]'SEPTEMBER'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2088,[COLOR=red]'OCTOBER'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2089,[COLOR=red]'NOVEMBER'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (2090,[COLOR=red]'DECEMBER'[/color])


[COLOR=blue]DECLARE[/color] @MontName [COLOR=blue]varchar[/color](200)
[COLOR=blue]SET[/color] @MontName = [COLOR=red]'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC'[/color]
[COLOR=blue]DECLARE[/color] @Month [COLOR=blue]int[/color]

[COLOR=blue]SELECT[/color] @Month = ([COLOR=#FF00FF]CHARINDEX[/color]([COLOR=#FF00FF]LEFT[/color]([COLOR=#FF00FF]MonthName[/color],3), @MontName)/3)+1
[COLOR=blue]FROM[/color] @Test [COLOR=blue]WHERE[/color] Id = 2090

[COLOR=blue]SELECT[/color] @Month

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[djj55]

If you are creating a temp table why not include the month number in the table?

DECLARE @Test TABLE (MonthNum int, Id int, MonthName varchar(200))
INSERT INTO @Test VALUES (1, 2079,'JANUARY')

Then you have all of the info.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[bborissov]

I created that Table variable just for testing.
Dan should not used it and change its name to the real table name and real field in that table.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

If the list of codes will not change...

Where where month(member.join_date) != a.attr_code_ref - 2078

Then again.... if the codes and month names are actually in the attribute table, and you are already joining to that table....

Where DateName(Month, member.join_date) != a.attr_code_value

This assumes that the column name is attr_code_value in the attribute table. You may need to change the column name.

If none of this is helpful, then I recommend you post some sample data and expected results.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

nice one everyone.

i played around with the @table bit for a while and had no luck...(i expect due to my lack of SQL skills)... just tried your method george and worked straight away.

spot on! cheers dude!

thanks again everyone!

dan.

 

[mossbs]

hey guys,

right, i need to write a procedure to add in a renewal payment plan for members.

the adding in bit is fine its just the pulling the correct people i am struggling with due to calculating dates etc.

our renewals process starts 2 months before they are due to renew etc so if i were to run it today i would need to pull people for may etc.

my problem lies in that members will have an 'attribute' on their record which is a ref number linked to a lookup table that gives the description of that attribute reference but i also need to include getdate into the equation to calculate 2 months in advance etc.

so essentially i need to pull people where the description (lookup_full_desc) = datename(month, getdate()) (but plus 3 months)

any ideas?

as always... apologies if none of the above makes sense - has been a long day!

cheers,

dan