A somewhat complicated SQL Query

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

A somewhat complicated SQL Query

Author

Topic

khovorka
Starting Member

8 Posts

Posted - 2013-09-13 : 12:26:12

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-13 : 12:47:56

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.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-13 : 19:36:41

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.

khovorka
Starting Member

8 Posts

Posted - 2013-09-17 : 10:11:50

Thanks so much and adjusted query worked and I very much appreciate it.

Kim H.

Kim H.

khovorka
Starting Member

8 Posts

Posted - 2013-09-24 : 17:31:25

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 17:45:34

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";

khovorka
Starting Member

8 Posts

Posted - 2013-09-25 : 11:32:49

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.

khovorka
Starting Member

8 Posts

Posted - 2013-09-25 : 11:54:06

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.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-09-26 : 02:13:54

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources