Query for my problem!! - SQL Server Forums

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Query for my problem!!

Author

Topic

VikramPC
Starting Member

2 Posts

Posted - 2011-02-15 : 04:45:36

I have to find out the amount(LAmt) for members who lie btw diff age groups, below are the table structures:

table1
DOB ID
12/24/1986 F125
06/05/1988 F223
05/04/1987 F589
07/07/2001 F856

table2
LAmt ID LDate
$56 F125 01/01/2011
$45 F223 08/22/2005
$87 F589 09/24/2004
$87 F856 10/10/2000
$87 F587 09/09/2002

Query:
1) I have to find the age group for the members who lie between 1 to 5 & 6 to 10 and so on, Also find the the no. of days since they have been registered(Using LDate-Current Date) , also the LAmt for those members.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-15 : 05:44:58

Welcome to SQL Team!

There's a number of things you should note:

1) Your way of expressing dates is dangerous. (mm/dd/yyyy) is not a universal standard. The ISO Standard for dates is YYYYMMDD or YYYY-MM-DDTHH:MM:SS.SSS.

2) To Do what you want I'd recommend that you have another table for the age groups.

Here's what I'd be tempted to do. There is a CROSS APPLY in this to work out the age of the member. If you are not familiar with this ask questions.


DECLARE @memberDOB TABLE (
	[memberID] CHAR(4) PRIMARY KEY
	, [DOB] DATE
	)
	
-- Table2 (what is this??)
DECLARE @table2 TABLE (
	[memberID] CHAR(4) PRIMARY KEY
	, [LAmt] MONEY
	, [LDate] DATE
	)

INSERT @memberDOB ([memberID], [DOB])
      SELECT 'F125', '19861224'
UNION SELECT 'F223', '19880605' -- This is my birthday also! (except I'm 10 years older :( )'
UNION SELECT 'F589', '19870504'
UNION SELECT 'F856', '20010707'
UNION SELECT 'XX34', GETDATE() -- This person is 0 years old
UNION SELECT 'XX12', DATEADD(YEAR, - 1, GETDATE()) -- This person should be 1 (it is his birthday today)

INSERT @table2 ([memberID], [LAmt], [LDate])
      SELECT 'F125', 56, '20110101'
UNION SELECT 'F223', 45, '20050822'
UNION SELECT 'F589', 87, '20040924'
UNION SELECT 'F856', 87, '20001010'
UNION SELECT 'F587', 87, '20020909'

-- Table to hold age groups
DECLARE @ageGroups TABLE (
	[AgeGroupDescription] VARCHAR(255)
	, [AgeGroupStart] TINYINT
	, [AgeGroupEnd] TINYINT
	
	PRIMARY KEY ( [AgeGroupStart], [AgeGroupEnd] )
	)
	
INSERT @ageGroups ([AgeGroupDescription], [AgeGroupStart], [AgeGroupEnd])
      SELECT '0', 0, 0
UNION SELECT '1 - 5', 1, 5
UNION SELECT '6 - 10', 6, 10
UNION SELECT '11 - 15', 11, 15
UNION SELECT '16 - 35', 16, 36
UNION SELECT '36 - 64', 36, 64
UNION SELECT '65+', 65, 255


-- Query.

DECLARE @today DATE SET @today = GETDATE()

SELECT
	md.[memberID] AS [MemberID]
	, md.[DOB] AS [MemberDOB]
	, ma.[memberAge] AS [MemberAge]
	, ag.[AgeGroupDescription] AS [Age Group]
	, t2.[LDate] AS [LDate]
	, DATEDIFF(DAY, t2.[LDate], GETDATE()) AS [NoDaysSinceRegistration]
	, t2.[LAmt] AS [LAmt]
FROM
	@memberDOB AS md
	
	-- Work out the age of the member
	CROSS APPLY (
		SELECT [memberAge] = DATEDIFF(YEAR, md.[DOB], @today) -
		CASE
			WHEN DATEPART(MONTH, md.[DOB]) < DATEPART(MONTH, @today) THEN 0
			WHEN DATEPART(MONTH, md.[DOB]) > DATEPART(MONTH, @today) THEN 1
			WHEN DATEPART(DAY, md.[DOB]) <= DATEPART(DAY, @today) THEN 0
			ELSE 1
		END
		)
		AS ma
	
	-- Use age to generate age group
	JOIN @ageGroups AS ag ON
		ag.[AgeGroupStart] <= ma.[MemberAge]	
		AND ag.[AgeGroupEnd] >= ma.[MemberAge]
		
	-- Fees?
	LEFT JOIN @table2 AS t2 ON t2.[memberID] = md.[memberID]

Results:


MemberID MemberDOB  MemberAge   Age Group  LDate      NoDaysSinceRegistration LAmt
-------- ---------- ----------- ---------- ---------- ----------------------- ---------------------
F125     1986-12-24 24          16 - 35    2011-01-01 45                      56.00
F223     1988-06-05 22          16 - 35    2005-08-22 2003                    45.00
F589     1987-05-04 23          16 - 35    2004-09-24 2335                    87.00
F856     2001-07-07 9           6 - 10     2000-10-10 3780                    87.00
XX12     2010-02-15 1           1 - 5      NULL       NULL                    NULL
XX34     2011-02-15 0           0          NULL       NULL                    NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

VikramPC
Starting Member

2 Posts

Posted - 2011-02-15 : 13:19:43

Thanks a lot for the info.
1)I would change the date format to YYYYMMDD.
2)There are just few tables listed, there are no constrains created for any table. It is just the JOIN by common fields & data.
I will not be inserting any data into DB. Data is already present.

In your query above: What will 'Insert @memberDOB', 'INSERT @ageGroups' and 'INSERT @table2' do? Are we trying to insert data into the tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources