SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 A somewhat complicated SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

khovorka
Starting Member

USA
8 Posts

Posted - 09/13/2013 :  12:26:12  Show Profile  Reply with Quote
Hello,

I am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results. Not sure if I have some things out of order or what I am doing wrong in this complicated query. Thanks for you help. The query is found here:

$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 21 AND 30 THEN '21-30'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 31 AND 40 THEN '31-40'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 41 AND 50 THEN '41-50'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 51 AND 60 THEN '51-60'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 61 AND 70 THEN '61-70'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 71 AND 80 THEN '71-80'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 81 AND 90 THEN '81-90'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 91 AND 100 THEN '91-100'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) > 100 THEN '100+'".
" ELSE null END AS age_group FROM dbo.tbl_mfc_pat1 as p1 WHERE p1.center=".$use_mfc." AND p1.enrolldate BETWEEN '".$bdate."' AND '".$edate."') INNER JOIN dbo.tbl_mfc_pat4 AS p4 ON p1.patient = p4.patientid".
" GROUP BY p1.sex, age_group ORDER BY p1.sex DESC";

All help is appreciated.

Kim H.

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 09/13/2013 :  12:47:56  Show Profile  Reply with Quote
quote:
Originally posted by khovorka

Hello,

I am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results. Not sure if I have some things out of order or what I am doing wrong in this complicated query. Thanks for you help. The query is found here:

$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 21 AND 30 THEN '21-30'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 31 AND 40 THEN '31-40'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 41 AND 50 THEN '41-50'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 51 AND 60 THEN '51-60'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 61 AND 70 THEN '61-70'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 71 AND 80 THEN '71-80'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 81 AND 90 THEN '81-90'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 91 AND 100 THEN '91-100'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) > 100 THEN '100+'".
" ELSE null END AS age_group FROM dbo.tbl_mfc_pat1 as p1 WHERE p1.center=".$use_mfc." AND p1.enrolldate BETWEEN '".$bdate."' AND '".$edate."') INNER JOIN dbo.tbl_mfc_pat4 AS p4 ON p1.patient = p4.patientid".
" GROUP BY p1.sex, age_group ORDER BY p1.sex DESC";

All help is appreciated.

Kim H.

Probably the easiest way to debug this is to run the code and print out the string $query. That should tell you what might be wrong. Or, post that to the forum and people would be able to help.

Another thing that you can do is to copy that string and run it from a SQL Server Management Studio query window to see what it generates.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 09/13/2013 :  19:36:41  Show Profile  Reply with Quote
As James asked, I would help to see the content of $query.

My suggestion for code change:

$query="select p1.sex".
             ",case".
                " when datediff(yyyy,p1.birthday,getdate()) between 11 and  20 then '11-20'".
                " when datediff(yyyy,p1.birthday,getdate()) between 21 and  30 then '21-30'".
                " when datediff(yyyy,p1.birthday,getdate()) between 31 and  40 then '31-40'".
                " when datediff(yyyy,p1.birthday,getdate()) between 41 and  50 then '41-50'".
                " when datediff(yyyy,p1.birthday,getdate()) between 51 and  60 then '51-60'".
                " when datediff(yyyy,p1.birthday,getdate()) between 61 and  70 then '61-70'".
                " when datediff(yyyy,p1.birthday,getdate()) between 71 and  80 then '71-80'".
                " when datediff(yyyy,p1.birthday,getdate()) between 81 and  90 then '81-90'".
                " when datediff(yyyy,p1.birthday,getdate()) between 91 and 100 then '91-100'".
                " when datediff(yyyy,p1.birthday,getdate())>100 then '100+'".
                " else null".
             " end as age_group".
             ",sum(case when p4.q1='Yes' then 1 else 0 end) as heart_attack".
        " from tbl_mfc_pat1 as p1".
             " inner join tbl_mfc_pat4 as p4".
                     " on p4.patientid=p1.patient".
       " where p1.center=".$use_mfc.
         " and p1.enrolldate between '".$bdate."' and '".$edate."'".
       " group by p1.sex".
                ",case".
                   " when datediff(yyyy,p1.birthday,getdate()) between 11 and  20 then '11-20'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 21 and  30 then '21-30'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 31 and  40 then '31-40'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 41 and  50 then '41-50'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 51 and  60 then '51-60'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 61 and  70 then '61-70'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 71 and  80 then '71-80'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 81 and  90 then '81-90'".
                   " when datediff(yyyy,p1.birthday,getdate()) between 91 and 100 then '91-100'".
                   " when datediff(yyyy,p1.birthday,getdate())>100 then '100+'".
                   " else null".
                " end".
       " order by p1.sex desc".
       ;

Basically it's the same code, except this one doesn't use subselect and it uses sum instead of count.

Edit: Forgot the order by.

Edited by - bitsmed on 09/13/2013 19:39:29
Go to Top of Page

khovorka
Starting Member

USA
8 Posts

Posted - 09/17/2013 :  10:11:50  Show Profile  Reply with Quote
Thanks so much and adjusted query worked and I very much appreciate it.

Kim H.

Kim H.
Go to Top of Page

khovorka
Starting Member

USA
8 Posts

Posted - 09/24/2013 :  17:31:25  Show Profile  Reply with Quote
Ok,

I am having a problem with a new query very similar to the one that started this topic. Any help with what I have messed up is appreciated.

$query = "SELECT p1.sex, ".
" Count(CASE WHEN p3s.waist > 47 THEN p3s.waist END) AS HighRisk".
" Count(CASE WHEN p3s.waist > 39 AND p3s.waist <= 47 THEN p3s.waist END) AS AtRisk".
" Count(CASE WHEN p3s.waist >= 31.5 AND p3s.waist <= 39 THEN p3s.waist END) AS Caution".
" Count(CASE WHEN p3s.waist < 31.5 THEN p3s.waist END) AS Normal".
" FROM dbo.tbl_mfc_pat3_most_recent_data_summary AS p3s".
" INNER JOIN dbo.tbl_mfc_pat1 AS p1 ON p1.patient = p3s.patientid".
" WHERE p3s.waist <> '' AND NOT(p3s.waist IS NULL) AND p3s.center = 51".
" GROUP BY p1.sex";

Thanks,

Kim H.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 09/24/2013 :  17:45:34  Show Profile  Reply with Quote
At the very least, you need comma's at the end of each line in the select portion of the query. If that does not fix the problem, uncomment the print statement and run it to see what $query contains and/or post that to the forum.
$query = "SELECT p1.sex, ".
" Count(CASE WHEN p3s.waist > 47 THEN p3s.waist END) AS HighRisk,". 
" Count(CASE WHEN p3s.waist > 39 AND p3s.waist <= 47 THEN p3s.waist END) AS AtRisk,". 
" Count(CASE WHEN p3s.waist >= 31.5 AND p3s.waist <= 39 THEN p3s.waist END) AS Caution,". 
" Count(CASE WHEN p3s.waist < 31.5 THEN p3s.waist END) AS Normal". 
" FROM dbo.tbl_mfc_pat3_most_recent_data_summary AS p3s".
" INNER JOIN dbo.tbl_mfc_pat1 AS p1 ON p1.patient = p3s.patientid".
" WHERE p3s.waist <> '' AND NOT(p3s.waist IS NULL) AND p3s.center = 51".
" GROUP BY p1.sex";
//print "$query\n";

Edited by - James K on 09/24/2013 17:45:55
Go to Top of Page

khovorka
Starting Member

USA
8 Posts

Posted - 09/25/2013 :  11:32:49  Show Profile  Reply with Quote
I changed things slightly and the query now partially works
except all the values that are returned are the same
for each sex and so I know that that is not correct. Any ideas
why all the values are the same for each gender?

$query = "SELECT p1.sex, ".
" Count(CASE WHEN p3s.waist > 47 THEN 1 ELSE 0 END) AS HighRisk,".
" Count(CASE WHEN p3s.waist > 39 AND p3s.waist <= 47 THEN 1 ELSE 0 END) AS AtRisk,".
" Count(CASE WHEN p3s.waist >= 31.5 AND p3s.waist <= 39 THEN 1 ELSE 0 END) AS Caution,".
" Count(CASE WHEN p3s.waist < 31.5 THEN 1 ELSE 0 END) AS Normal".
" FROM dbo.tbl_mfc_pat3_most_recent_data_summary AS p3s".
" INNER JOIN dbo.tbl_mfc_pat1 AS p1 ON p1.patient = p3s.patientid".
" WHERE p3s.waist <> '' AND NOT(p3s.waist IS NULL) AND p3s.center = 10".
" GROUP BY p1.sex";

Here is what the comma delimited values look like
12,12,12,12,20,20,20,20

The 12 represents Female gender
The 20 represent Male gender

Each of the 12 values should not be the same
and the same for 20, they should have a wide range
of values in there. So somehow the counting is wrong?

Any help is appreciated.

quote:
Originally posted by James K

At the very least, you need comma's at the end of each line in the select portion of the query. If that does not fix the problem, uncomment the print statement and run it to see what $query contains and/or post that to the forum.
$query = "SELECT p1.sex, ".
" Count(CASE WHEN p3s.waist > 47 THEN p3s.waist END) AS HighRisk,". 
" Count(CASE WHEN p3s.waist > 39 AND p3s.waist <= 47 THEN p3s.waist END) AS AtRisk,". 
" Count(CASE WHEN p3s.waist >= 31.5 AND p3s.waist <= 39 THEN p3s.waist END) AS Caution,". 
" Count(CASE WHEN p3s.waist < 31.5 THEN p3s.waist END) AS Normal". 
" FROM dbo.tbl_mfc_pat3_most_recent_data_summary AS p3s".
" INNER JOIN dbo.tbl_mfc_pat1 AS p1 ON p1.patient = p3s.patientid".
" WHERE p3s.waist <> '' AND NOT(p3s.waist IS NULL) AND p3s.center = 51".
" GROUP BY p1.sex";
//print "$query\n";




Kim H.
Go to Top of Page

khovorka
Starting Member

USA
8 Posts

Posted - 09/25/2013 :  11:54:06  Show Profile  Reply with Quote
I got it figured out and it was unexpected.
Thanks for the assistance given. I do appreciat it.

quote:
Originally posted by khovorka

I changed things slightly and the query now partially works
except all the values that are returned are the same
for each sex and so I know that that is not correct. Any ideas
why all the values are the same for each gender?

$query = "SELECT p1.sex, ".
" Count(CASE WHEN p3s.waist > 47 THEN 1 ELSE 0 END) AS HighRisk,".
" Count(CASE WHEN p3s.waist > 39 AND p3s.waist <= 47 THEN 1 ELSE 0 END) AS AtRisk,".
" Count(CASE WHEN p3s.waist >= 31.5 AND p3s.waist <= 39 THEN 1 ELSE 0 END) AS Caution,".
" Count(CASE WHEN p3s.waist < 31.5 THEN 1 ELSE 0 END) AS Normal".
" FROM dbo.tbl_mfc_pat3_most_recent_data_summary AS p3s".
" INNER JOIN dbo.tbl_mfc_pat1 AS p1 ON p1.patient = p3s.patientid".
" WHERE p3s.waist <> '' AND NOT(p3s.waist IS NULL) AND p3s.center = 10".
" GROUP BY p1.sex";

Here is what the comma delimited values look like
12,12,12,12,20,20,20,20

The 12 represents Female gender
The 20 represent Male gender

Each of the 12 values should not be the same
and the same for 20, they should have a wide range
of values in there. So somehow the counting is wrong?

Any help is appreciated.

quote:
Originally posted by James K

At the very least, you need comma's at the end of each line in the select portion of the query. If that does not fix the problem, uncomment the print statement and run it to see what $query contains and/or post that to the forum.
$query = "SELECT p1.sex, ".
" Count(CASE WHEN p3s.waist > 47 THEN p3s.waist END) AS HighRisk,". 
" Count(CASE WHEN p3s.waist > 39 AND p3s.waist <= 47 THEN p3s.waist END) AS AtRisk,". 
" Count(CASE WHEN p3s.waist >= 31.5 AND p3s.waist <= 39 THEN p3s.waist END) AS Caution,". 
" Count(CASE WHEN p3s.waist < 31.5 THEN p3s.waist END) AS Normal". 
" FROM dbo.tbl_mfc_pat3_most_recent_data_summary AS p3s".
" INNER JOIN dbo.tbl_mfc_pat1 AS p1 ON p1.patient = p3s.patientid".
" WHERE p3s.waist <> '' AND NOT(p3s.waist IS NULL) AND p3s.center = 51".
" GROUP BY p1.sex";
//print "$query\n";




Kim H.



Kim H.
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 09/26/2013 :  02:13:54  Show Profile  Reply with Quote
hi, you may try
CASE WHEN DATEDIFF(year, p1.birthday, getdate()) < 11 THEN
NULL
WHEN DATEDIFF(year, p1.birthday, getdate()) > 100 THEN
'100+'
ELSE
CAST((DATEDIFF(year, p1.birthday, getdate()) - 1) / 10 AS VARCHAR(2)) + '1-' + CAST(((DATEDIFF(year, col1, getdate()) - 1) / 10) + 1 AS VARCHAR(2)) + '0'
END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000