Solved

rookie stored procedure help part 2

Posted on 2007-11-22
39
380 Views
Last Modified: 2008-02-01
hi everyone,
have recently finished a question to get this result which allows me to return the year, the month, the counted total and the male and female stats for a registration table.
 
the table schema has the following..

id, firstname, lastname, registration_date (yyyymmdd), sex, dateofbirth

the thing I also need to incorporate into this sp is age grouping,
if this is possible the results will return as they do with the current structure of the sp although it will also look at the registrations date of birth and spit the results into age groups, i.e. 18-30, 31- 40 etc...

hopefully this isnt too much work with the current structure.

current format of DOB is 18/May/1930

please provide an example, many thanks


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

 

alter PROCEDURE [dbo].[stp_GetCountMonthWise]  

		@startdate DATETIME,

		@finishdate DATETIME 

AS
 

SELECT		TheYear = MAX(YEAR(registration_date)),

			TheMonth = MAX(DATENAME(mm,registration_date)),

			TheTotal = COUNT(id),

			female  = SUM(case when isnull(sex,'Unknown') = 'Female' then 1 else 0 end),

			male = SUM(case when isnull(sex,'Unknown')  = 'Male' then 1 else 0 end)

FROM        registrations

WHERE       registration_date >= @startdate 

AND         registration_date <= @finishdate

group by	YEAR(registration_date), datepart(mm,registration_date)

Order by	YEAR(registration_date), datepart(mm,registration_date)

Open in new window

0
Comment
Question by:jimbona27
  • 19
  • 9
  • 9
  • +1
39 Comments
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20335763
Just use a series of case statements...(once again, your date format is really messed up...) but basically the approach is to create a series of calculated fields for each 'bucket'

age18_30 = SUM(case where datediff(year, convert(datetime,dateofbirth,103), getdate() between 18 and 30 then 1 else 0 end)
age31_40 = SUM(case where datediff(year, convert(datetime,dateofbirth,103), getdate() between 31 and 40 then 1 else 0 end)

This will give you summed totals in the same way as you have maie & female counts.  However, if you wanted to GROUP BY these age buckets, then it would look slightly different -

Select
age_group =  case where datediff(year, convert(datetime,dateofbirth,103), getdate() between 18 and 30 then 18_30 else
 case where datediff(year, convert(datetime,dateofbirth,103), getdate() between 31and 40 then 31_40 else ....
 end
end(one 'end' for each case statement)
....
GROUP BY
 case where datediff(year, convert(datetime,dateofbirth,103), getdate() between 18 and 30 then 18_30 else
 case where datediff(year, convert(datetime,dateofbirth,103), getdate() between 31and 40 then 31_40
 else ....etc...
 end
end(one 'end' for each case statement)

If you want a fully worked example (if the above is not sufficient for you) then you will need to specify in more detail what you want the output to be
0
 

Author Comment

by:jimbona27
ID: 20335835
thanks,
i need to return the following details

year
month          
total  
female          
male    

this is what I have, but I also need to return this:

age 18-24 (female)
age  25-34 (female)
age34-55 (female)    
age 45-54 (female)    
age 55+ (female)

age 18-24 (male)
age 25-34 (male)
age 34-55 (male)    
age 45-54 (male)    
age 55+ (male)
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20335907
easy ... though slightly laborious..create columns with both the age range and gender condition as follows

age18_24female = SUM(case where datediff(year, convert(datetime,dateofbirth,103), getdate() between 18 and 24 and gender = female then 1 else 0 end)
...etc
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 120 total points
ID: 20336069
something like
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

 

alter PROCEDURE [dbo].[stp_GetCountMonthWise]  

                @startdate DATETIME,

                @finishdate DATETIME 

AS

 

SELECT

TheYear = MAX(YEAR(registration_date)),

TheMonth = MAX(DATENAME(mm,registration_date)),

TheTotal = COUNT(id),

[age 18-24 (female)] = SUM(case

	when isnull(sex,'Unknown') = 'Female'

	 and datediff(year, convert(datetime,DOB,103), getdate()) between 18 and 24

	then 1 else 0 end),

[age 25-34 (female)] = SUM(case

	when isnull(sex,'Unknown') = 'Female'

	 and datediff(year, convert(datetime,DOB,103), getdate()) between 25 and 34

	then 1 else 0 end),

---- fil in the blanks

[age 55+ (female)] = SUM(case

	when isnull(sex,'Unknown') = 'Female'

	 and datediff(year, convert(datetime,DOB,103), getdate()) >= 55

	then 1 else 0 end),

[age 18-24 (female)] = SUM(case

	when isnull(sex,'Unknown') = 'Male'

	 and datediff(year, convert(datetime,DOB,103), getdate()) between 18 and 24

	then 1 else 0 end),

---- the rest

FROM        registrations

WHERE       registration_date >= @startdate 

AND         registration_date <= @finishdate

group by        YEAR(registration_date), datepart(mm,registration_date)

Order by        YEAR(registration_date), datepart(mm,registration_date)

Open in new window

0
 

Author Comment

by:jimbona27
ID: 20337975
ok thanks, tried this and I get the following:

Msg 208, Level 16, State 6, Procedure stp_GetCountMonthWiseTest, Line 7
Invalid object name 'dbo.stp_GetCountMonthWiseTest'.
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

 

alter PROCEDURE [dbo].[stp_GetCountMonthWiseTest]  

                @startdate DATETIME,

                @finishdate DATETIME 

AS

 

SELECT

TheYear = MAX(YEAR(registration_date)),

TheMonth = MAX(DATENAME(mm,registration_date)),

TheTotal = COUNT(id),

[age 18-24 (female)] = SUM(case

        when isnull(sex,'Unknown') = 'Female'

         and datediff(year, convert(datetime,DOB,103), getdate()) between 18 and 24

        then 1 else 0 end),

[age 25-34 (female)] = SUM(case

        when isnull(sex,'Unknown') = 'Female'

         and datediff(year, convert(datetime,DOB,103), getdate()) between 25 and 34

        then 1 else 0 end),

---- fil in the blanks

[age 55+ (female)] = SUM(case

        when isnull(sex,'Unknown') = 'Female'

         and datediff(year, convert(datetime,DOB,103), getdate()) >= 55

        then 1 else 0 end),

[age 18-24 (female)] = SUM(case

        when isnull(sex,'Unknown') = 'Male'

         and datediff(year, convert(datetime,DOB,103), getdate()) between 18 and 24

        then 1 else 0 end)

---- the rest

FROM			registrations

WHERE			registration_date >= @startdate 

AND				registration_date <= @finishdate

group by        YEAR(registration_date), datepart(mm,registration_date)

Order by        YEAR(registration_date), datepart(mm,registration_date)

Open in new window

0
 
LVL 1

Accepted Solution

by:
GeraldHlasgow earned 380 total points
ID: 20337979
Do bear in mind though, that "DateDiff(year," only returns the difference in the year portions of the dates, not the difference in years between the dates.  In other words:

select datediff(year, '20-Oct-2006','23-Nov-2007'),
datediff(year, '20-Nov-2006','23-Nov-2007'),
datediff(year, '20-Dec-2006','23-Nov-2007')

will return 1 1 1 not 1 1 0 because it's just comparing 2006 against 2007, not the whole date.

You're probably better off using DateAdd.  Something like
Before the select statement include something like
 

DECLARE	@Today DATETIME

SELECT @Today = CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME)
 

Then replace the various bits of the select with...
 

[age 18-24 (female)] = SUM(case

        when isnull(sex,'Unknown') = 'Female'

         and dateadd(year,18,convert(datetime,DOB,103)) >= @Today

         and dateadd(year,25,convert(datetime,DOB,103)) < @Today

        then 1 else 0 end),

[age 25-34 (female)] = SUM(case

        when isnull(sex,'Unknown') = 'Female'

         and dateadd(year,25,convert(datetime,DOB,103)) >= @Today

         and dateadd(year,35,convert(datetime,DOB,103)) < @Today

        then 1 else 0 end),
 

etc etc

Open in new window

0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20337994
Another thought.  If you're not including people of unknown sex in the results, rather then use ISNULL on each record, you could just add

AND sex IS NOT NULL

to the end of the WHERE clause and then get rid of the ISNULL parts.
0
 

Author Comment

by:jimbona27
ID: 20338030
this is the complete sp I have now.
i think the data format might be wrong??
im triggering the sp with

exec stp_GetCountMonthWise '20010101' '20081001'

this returns:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '20081001'.
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[usp_test]  

		@startdate DATETIME,

		@finishdate DATETIME 

AS
 

DECLARE			@Today DATETIME

SELECT			@Today = CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME) 
 

SELECT			TheYear = MAX(YEAR(registration_date)),

				TheMonth = MAX(DATENAME(mm,registration_date)),

				TheTotal = COUNT(id),

				female = SUM(case when isnull(sex,'Unknown') = 'Female' then 1 else 0 end),

				male = SUM(case when isnull(sex,'Unknown')  = 'Male' then 1 else 0 end),

				[age 18-24 (female)] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,18,convert(datetime,DOB,103)) >= @Today

						 and dateadd(year,25,convert(datetime,DOB,103)) < @Today

						then 1 else 0 end)

FROM            registrations

WHERE           registration_date > @startdate 

AND             registration_date < @finishdate

group by		YEAR(registration_date), datepart(mm,registration_date)

Order by		YEAR(registration_date), datepart(mm,registration_date)

Open in new window

0
 
LVL 1

Assisted Solution

by:GeraldHlasgow
GeraldHlasgow earned 380 total points
ID: 20338056
Just put a comma between the two dates

exec stp_GetCountMonthWise '20010101', '20081001'
0
 

Author Comment

by:jimbona27
ID: 20338058
sorry,
exec usp_test '20010101' '20081001'

returns
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '20081001'.
0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20338091
Don't understand.

As I said, put a comma between the two dates.   Whenever you run a stored procedure all parameters must be separated by commas.

See below (and above)
exec stp_GetCountMonthWise '20010101', '20081001'
 

                                     ^

---- comma goes here ----------------+

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20338103
exec usp_test '2001-01-01', '2008-10-01'
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20338110
Jim - I'm guessing your error message is probably because imitch's script is trying to ALTER stp_GetCountMonthWiseTest - but your original proc is stp_GetCountMonthWise- so either change the line to

CREATE PROECEDURE stp_GetCountMonthWiseTest
or to: ALTER PROCEDURE stp_GetCountMonthWise
Gerald - good point of course on DateDiff - u r right - DateAdd is much better here as u suggest
0
 

Author Comment

by:jimbona27
ID: 20338119
ok great, just adding the other ages I need..
looking good, thanks
0
 

Author Comment

by:jimbona27
ID: 20338124
this is what I have,
can you just confirm the over 55 is correct as I dont have much data right now so I cant be toooo sure...
thanks
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[usp_test]  

		@startdate DATETIME,

		@finishdate DATETIME 

AS
 

DECLARE			@Today DATETIME

SELECT			@Today = CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME) 
 

SELECT			TheYear = MAX(YEAR(registration_date)),

				TheMonth = MAX(DATENAME(mm,registration_date)),

				TheTotal = COUNT(id),

				female = SUM(case when isnull(sex,'Unknown') = 'Female' then 1 else 0 end),

				male = SUM(case when isnull(sex,'Unknown')  = 'Male' then 1 else 0 end),

	

-- female stats

				[age 18-24 (female)] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,18,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,25,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 25-34 (female)] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,25,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,35,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 35-44 (female)] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,35,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,45,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 45-54 (female)] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,45,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,66,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 55+ (female)] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,56,convert(datetime,dob,103)) >= @Today

						then 1 else 0 end),
 
 

-- male stats

				[age 18-24 (male)] = SUM(case

						when sex = 'Male'

						 and dateadd(year,18,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,25,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 25-34 (male)] = SUM(case

						when sex = 'Male'

						 and dateadd(year,25,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,35,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 35-44 (male)] = SUM(case

						when sex = 'Male'

						 and dateadd(year,35,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,45,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 45-54 (male)] = SUM(case

						when sex = 'Male'

						 and dateadd(year,45,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,66,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age 55+ (male)] = SUM(case

						when sex = 'Male'

						 and dateadd(year,56,convert(datetime,dob,103)) >= @Today

						then 1 else 0 end)

FROM            registrations

WHERE           registration_date > @startdate 

AND             registration_date < @finishdate

group by		YEAR(registration_date), datepart(mm,registration_date)

Order by		YEAR(registration_date), datepart(mm,registration_date)

Open in new window

0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20338134
a couple of typos - line 65 and 69 should both be '55' - otherwise i think it looks ok to me
0
 

Author Comment

by:jimbona27
ID: 20338517
hi there,
thats about it although just a quick check,  if I add the following line

                        [age 18-24 (female)] = SUM(case
                                    when isnull(sex,'Unknown') = 'Female'
                                     and dateadd(year,18,convert(datetime,dob,103)) >= @Today
                                     and dateadd(year,25,convert(datetime,dob,103)) < @Today
                                    then 1 else 0 end),
                        [age 18-24 (female)test] = SUM(test), // this line

test is an Int (either 0 or 1)... will that count the result of 1s or 0s within each age group?

many thanks,
0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20338528
If test is either 1 or 0 then SUM(test) will equal the number of records within each group where test = 1 and of course where the record as a whole matches any other criteria you've selected (e.g. date ranges).
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20338534
no - it won't - it will just sum the 1's across the whole table.  The partitioning of the agegroups only happens within the case statement.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jimbona27
ID: 20338557
the test value in the table represents whether they want to receive email or not.
i would like to return how many females/ males in each age group want emails?
so the returned schema would look something like this:

age 18-24 (female)
totalemails (count)
age  25-34 (female)
totalemails (count)
age34-55 (female)    
totalemails (count)
age 45-54 (female)    
totalemails (count)
age 55+ (female)
totalemails (count)

age 18-24 (male)
totalemails (count)
age 25-34 (male)
totalemails (count)
age 34-55 (male)    
totalemails (count)
age 45-54 (male)    
totalemails (count)
age 55+ (male)
totalemails (count)
0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20338569
Sorry I was thinking of group as in

group by                YEAR(registration_date), datepart(mm,registration_date)

if you mean group as in female age 18-24, female age 25 -34 etc, then Paul's pretty much right.  It won't sum across the whole table because of the 'group by' clause, but you will get one sum for each value in the 'group by' groups.
0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20338578
Sorry, premature click.  For that you'd need something like
[age 18-24 (female)] = SUM(case

        when isnull(sex,'Unknown') = 'Female'

         and datediff(year, convert(datetime,DOB,103), getdate()) between 18 and 24

        then test else 0 end),

Open in new window

0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20338580
Sorry, copied and pasted from the wrong example:
                                [age 18-24 (female)] = SUM(case

                                                when isnull(sex,'Unknown') = 'Female'

                                                 and dateadd(year,18,convert(datetime,dob,103)) >= @Today

                                                 and dateadd(year,25,convert(datetime,dob,103)) < @Today

                                                then test else 0 end),

Open in new window

0
 

Author Comment

by:jimbona27
ID: 20338590
i would like to return the value of
age 18-24 has 13 females wanting to receive emails
age 18-24 has 2 females dont want to receive emails
etc...

is that possible?
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20338643
Jimbona - asking multiple questions is technically a violation of the user agreement....but anyway I suggest you look at the case statements we have given you already - you can just add another 'and' clause to your statement

18-24FEMALE_EMAIL=    case when isnull(sex,'Unknown') = 'Female'
                                                 and dateadd(year,18,convert(datetime,dob,103)) >= @Today
                                                 and dateadd(year,25,convert(datetime,dob,103)) < @Today
and test = 1
                                                then 1 else 0 end),

18-24FEMALE_NOEMAIL=    case when isnull(sex,'Unknown') = 'Female'
                                                 and dateadd(year,18,convert(datetime,dob,103)) >= @Today
                                                 and dateadd(year,25,convert(datetime,dob,103)) < @Today
and test = 0
                                                then 1 else 0 end),
0
 

Author Comment

by:jimbona27
ID: 20338700
thanks GeraldHlasgow, I have tried the following

                        [age 18-24 (female)] = SUM(case
                        when isnull(sex,'Unknown') = 'Female'
                         and dateadd(year,18,convert(datetime,dob,103)) >= @Today
                         and dateadd(year,25,convert(datetime,dob,103)) < @Today
                        then NAME_OF_COLUMN else 0 end),

although I get the following

Msg 242, Level 16, State 3, Procedure stp_GetCountMonthWise, Line 10
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


				[optin18female] = case 

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,18,convert(datetime,dob,103)) >= @Today

                         and dateadd(year,25,convert(datetime,dob,103)) < @Today

						 and test = 1

                        then 1 else 0 end),

				[optout18female] = case 

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,18,convert(datetime,dob,103)) >= @Today

                         and dateadd(year,25,convert(datetime,dob,103)) < @Today

						 and test = 0

                        then 1 else 0 end),

Open in new window

0
 

Author Comment

by:jimbona27
ID: 20338835
i'll open a new question.
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20338837
Check the data in the DOB field.  The convert statement is looking for something in the format dd/mm/yyyy (that's what the 103 means).  if the data really is dd/mmm/yyyy (12/Nov/2007) then that might be giving you problems.  As I've mentioned before, char(8) yyyymmdd is a nice way to store daily dates - (code 112) or convert the field to a datetime...
0
 

Author Comment

by:jimbona27
ID: 20338966
ok great, i've corrected the backend so it saves the date as 19/11/2007

although i am still getting the problem, the cell type is char(10):

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

0
 

Author Comment

by:jimbona27
ID: 20339044
ah think after making that change it brakes the original version.


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[stp_GetCountMonthWise]  

		@startdate DATETIME,

		@finishdate DATETIME 

AS
 

DECLARE			@Today DATETIME

SELECT			@Today = CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME) 
 

SELECT			TheYear = MAX(YEAR(registration_date)),

				TheMonth = MAX(DATENAME(mm,registration_date)),

				TheTotal = COUNT(id),

				female = SUM(case when isnull(sex,'Unknown') = 'Female' then 1 else 0 end),

				male = SUM(case when isnull(sex,'Unknown')  = 'Male' then 1 else 0 end),

	

-- female stats

				[age18female] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,18,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,25,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age25female] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,25,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,35,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age35female] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,35,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,45,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age45female] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,45,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,55,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age55female] = SUM(case

						when isnull(sex,'Unknown') = 'Female'

						 and dateadd(year,55,convert(datetime,dob,103)) >= @Today

						then 1 else 0 end),
 
 

-- male stats

				[age18male] = SUM(case

						when sex = 'Male'

						 and dateadd(year,18,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,25,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age25male] = SUM(case

						when sex = 'Male'

						 and dateadd(year,25,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,35,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age35male] = SUM(case

						when sex = 'Male'

						 and dateadd(year,35,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,45,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age45male] = SUM(case

						when sex = 'Male'

						 and dateadd(year,45,convert(datetime,dob,103)) >= @Today

						 and dateadd(year,55,convert(datetime,dob,103)) < @Today

						then 1 else 0 end),

				[age55male] = SUM(case

						when sex = 'Male'

						 and dateadd(year,55,convert(datetime,dob,103)) >= @Today

						then 1 else 0 end)
 

FROM            registrations

WHERE           registration_date > @startdate 

AND             registration_date < @finishdate

group by		YEAR(registration_date), 

				datepart(mm,registration_date),

				further_information

Order by		YEAR(registration_date), 

				datepart(mm,registration_date)

Open in new window

0
 

Author Comment

by:jimbona27
ID: 20339057
dob type: char(10)
ie. 01/12/1970
0
 

Author Comment

by:jimbona27
ID: 20339098
ah 103 - means dd/mm/yy, not dd/mm/yyyy

lets see..
0
 

Author Comment

by:jimbona27
ID: 20339180
i need the whole year, not yy, but yyyy.
0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20339253
3 is dd/mm/yy and 103 is dd/m/yyyy

However, I think we're all making a fundamental mistake here.  Surely the codes 3 or 103 and their ilk are used for determining the format when converting *from* a date to a string, not the other way round.

I didn't realise that the codes would have had an impact converting *to* a datetime.

I suspect the problem is related to SQL trying to convert a value bigger than 12 into a month.

You could try inserting the statement SET DATEFORMAT DMY into your stored procedure before the main SELECT statement.
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20339298
Hi Gerald - I think the codes work either way - converting to OR from - though I suspect that they are optional - by default the SET DATEFORMAT would be used in the case of ambiguity, so these codes act as an override - so I would be surprised if the solution above worked (so surprise me !!! ) :)
0
 

Author Comment

by:jimbona27
ID: 20339595
im still experiencing a problem with the email receive count,

i have the following:


                                                [18-24FEMALE_EMAIL] = case
                                                                        when isnull(sex,'Unknown') = 'Female'
                                                 and dateadd(year,18,convert(datetime,dob,103)) >= @Today
                                                 and dateadd(year,25,convert(datetime,dob,103)) < @Today
                                                                         and further_information = 1
                                                then 1 else 0 end,
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20339634
sorry - you of course need to SUM around it...btw - why no points???????

                                                [18-24FEMALE_EMAIL] = SUM(case
                                                                        when isnull(sex,'Unknown') = 'Female'
                                                 and dateadd(year,18,convert(datetime,dob,103)) >= @Today
                                                 and dateadd(year,25,convert(datetime,dob,103)) < @Today
                                                                         and further_information = 1
                                                then 1 else 0 end),
0
 

Author Comment

by:jimbona27
ID: 20339827
go here.
0
 

Author Comment

by:jimbona27
ID: 20339829
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now