Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

rookie stored procedure help part 2

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
jimbona27
Asked:
jimbona27
  • 19
  • 9
  • 9
  • +1
3 Solutions
 
PaultheBrokerCommented:
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
 
jimbona27Author Commented:
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
 
PaultheBrokerCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
imitchieCommented:
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
 
jimbona27Author Commented:
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
 
GeraldHlasgowCommented:
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
 
GeraldHlasgowCommented:
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
 
jimbona27Author Commented:
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
 
GeraldHlasgowCommented:
Just put a comma between the two dates

exec stp_GetCountMonthWise '20010101', '20081001'
0
 
jimbona27Author Commented:
sorry,
exec usp_test '20010101' '20081001'

returns
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '20081001'.
0
 
GeraldHlasgowCommented:
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
 
imitchieCommented:
exec usp_test '2001-01-01', '2008-10-01'
0
 
PaultheBrokerCommented:
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
 
jimbona27Author Commented:
ok great, just adding the other ages I need..
looking good, thanks
0
 
jimbona27Author Commented:
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
 
PaultheBrokerCommented:
a couple of typos - line 65 and 69 should both be '55' - otherwise i think it looks ok to me
0
 
jimbona27Author Commented:
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
 
GeraldHlasgowCommented:
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
 
PaultheBrokerCommented:
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
 
jimbona27Author Commented:
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
 
GeraldHlasgowCommented:
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
 
GeraldHlasgowCommented:
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
 
GeraldHlasgowCommented:
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
 
jimbona27Author Commented:
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
 
PaultheBrokerCommented:
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
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
i'll open a new question.
0
 
PaultheBrokerCommented:
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
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
dob type: char(10)
ie. 01/12/1970
0
 
jimbona27Author Commented:
ah 103 - means dd/mm/yy, not dd/mm/yyyy

lets see..
0
 
jimbona27Author Commented:
i need the whole year, not yy, but yyyy.
0
 
GeraldHlasgowCommented:
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
 
PaultheBrokerCommented:
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
 
jimbona27Author Commented:
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
 
PaultheBrokerCommented:
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
 
jimbona27Author Commented:
go here.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 19
  • 9
  • 9
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now