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
 creating bounded groupings

Author  Topic 

mksdf
Starting Member

26 Posts

Posted - 2012-12-23 : 01:55:51
I have a data set with addresses, dates etc. Year built of properties is what im interested in. I also have a table with groupings/subsets in. I want to write a query that puts the 100's of locations into these subsets but I dont know what the best method is. I need them to be in groups of 10 year intervals.

Any help would be appreciated.

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-23 : 02:15:25
sounds like a mapping table to me with a groupid to denote associated groups.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mksdf
Starting Member

26 Posts

Posted - 2012-12-23 : 03:19:02
could you elaborate on that please?
Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-23 : 03:19:53
quote:
Originally posted by mksdf

could you elaborate on that please?
Thanks



howzz the address etc stored on your table currently?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mksdf
Starting Member

26 Posts

Posted - 2012-12-23 : 03:23:14
I believe it is in YYYY-MM-DD 00:00.00 or something like that. I can just convert it if I need to can't I?

Thanks for your help btw
Go to Top of Page

mksdf
Starting Member

26 Posts

Posted - 2012-12-23 : 06:30:31
sorry im not sure if i was very clear there. The data I need to group is location stuff like address, cost of building etc with date built as one of the columns. I need to group it by date of say 10 years and add up all the values. The dates are in the datetime format YYYY-MM-DD 00:00.00 in the main data set.

Thanks
Go to Top of Page

mksdf
Starting Member

26 Posts

Posted - 2012-12-23 : 19:19:25
oh. and the address info is stored as streetname, city, state, postcode. all as strings
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-23 : 20:16:15
please post your schema, sample data and expected result.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-24 : 15:17:10
quote:
Originally posted by mksdf

oh. and the address info is stored as streetname, city, state, postcode. all as strings


As per your initial explanation what I suggested was to create an intermediate mapping table having GroupID and AddressID as fields which will link between Address table and grouping table. Each record links an address record to group in this table. the fields will be linked to master tables (address,Grouping) via foreign key constraints

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -