mahpog
asked on
Sql Sytnax to fill in missing month with default of zero
I have a sql query that I do for a year and count the number of occurrences of that program for each month. I have January with a count of "1" but nothing for February, but then I have something for March, say '5', and and so on for the full year. I do this for each program.
How do I write the sql syntax to get the 12 months of data with "0" filler for each program.
****************
select year(date_created) as selyear, month(date_created) as selmonth, count(a.program) as cntpgm,
b.program_id
FROM mrb_details a, mrb_program b
where date_created between '01/01/2011' and '12/31/2011'
and a.program = b.program_id
group by year(date_created), month(date_created), b.program_id
order by month(date_created) asc, b.program_id
*******************
Thanks!
sample.pdf
How do I write the sql syntax to get the 12 months of data with "0" filler for each program.
****************
select year(date_created) as selyear, month(date_created) as selmonth, count(a.program) as cntpgm,
b.program_id
FROM mrb_details a, mrb_program b
where date_created between '01/01/2011' and '12/31/2011'
and a.program = b.program_id
group by year(date_created), month(date_created), b.program_id
order by month(date_created) asc, b.program_id
*******************
Thanks!
sample.pdf
Please try the following:
select
r.selyear,
r.selmonth,
(select count(1) from mrb_details a
where a.program = b.program_id
and a.date_created between '01/01/2011' and '12/31/2011'
and year(date_created) = r.selyear
and month(date_created) = r.selmonth) as cntpgm,
b.program_id
from
(select 2011 as selyear, number as selmonth
from spt_values
where type = 'P' and number between 1 and 12) r,
mrb_program b
group by r.selyear, r.selmonth, b.program_id
order by r.selmonth, b.program_idASKER
okay here is the original query unedited( I am attaching screenshots of two tables referenced)
thx!
**************************
select year(date_created) as selyear, month(date_created) as selmonth, isnull(count(a.program),0)
FROM mrb_details a, mrb_program b
where date_created between '01/01/2011' and '12/31/2011'
and a.program = b.program_id
group by year(date_created), month(date_created), b.program
order by month(date_created), b.program
**************************
sample.pdf
thx!
**************************
select year(date_created) as selyear, month(date_created) as selmonth, isnull(count(a.program),0)
FROM mrb_details a, mrb_program b
where date_created between '01/01/2011' and '12/31/2011'
and a.program = b.program_id
group by year(date_created), month(date_created), b.program
order by month(date_created), b.program
**************************
sample.pdf
Did the query returned the wrong results or did it generate an error? Please indicate what's missing or the specific error message.
Try this:
select year(a.date_created) as selyear, month(a.date_created) as selmonth, count(a.program) as cntpgm, b.program
FROM (
SELECT [Date_created], Program
FROM mrb_details
where date_created between '01/01/2011' and '12/31/2011') as a
LEFT JOIN mrb_program b
ON a.program = b.program_id
group by year(date_created), month(date_created), b.program
order by month(date_created), b.program
This first identifies all of the records from table: mrb_details where the [Date_Created] falls within your date range.
It then uses a left join (just in case there are some Program codes in mrb_details that are not in mrb_program
to join to mrb_program, and then counts the number of records by year, month, program.
select year(a.date_created) as selyear, month(a.date_created) as selmonth, count(a.program) as cntpgm, b.program
FROM (
SELECT [Date_created], Program
FROM mrb_details
where date_created between '01/01/2011' and '12/31/2011') as a
LEFT JOIN mrb_program b
ON a.program = b.program_id
group by year(date_created), month(date_created), b.program
order by month(date_created), b.program
This first identifies all of the records from table: mrb_details where the [Date_Created] falls within your date range.
It then uses a left join (just in case there are some Program codes in mrb_details that are not in mrb_program
to join to mrb_program, and then counts the number of records by year, month, program.
ASKER
wdosanjos - I got an error on spt_values,when I ran.
*********************
fyed - I ran your query and got same result from original query.
***************
I want to output a value for each month in the year for 2011. So, if the pgm has a value for January, I output that value. and again for feb - thru december for 2011. If there is no value for the pgm, I want to output "0".
For example
pgm jan f eb mar apr may jun jul aug sep oct nov dec
b2 1 0 0 0 3 0 0 3 1 0 0 1
(my current query only returns the pgm with a value for the month)
*********************
fyed - I ran your query and got same result from original query.
***************
I want to output a value for each month in the year for 2011. So, if the pgm has a value for January, I output that value. and again for feb - thru december for 2011. If there is no value for the pgm, I want to output "0".
For example
pgm jan f eb mar apr may jun jul aug sep oct nov dec
b2 1 0 0 0 3 0 0 3 1 0 0 1
(my current query only returns the pgm with a value for the month)
Please try again:
select
r.selyear,
r.selmonth,
(select count(1) from mrb_details a
where a.program = b.program_id
and a.date_created between '01/01/2011' and '12/31/2011'
and year(date_created) = r.selyear
and month(date_created) = r.selmonth) as cntpgm,
b.program_id
from
(select 2011 as selyear, number as selmonth
from [master].[dbo].[spt_values]
where type = 'P' and number between 1 and 12) r,
mrb_program b
group by r.selyear, r.selmonth, b.program_id
order by r.selmonth, b.program_id
Provide us with some sample data and what you want the output to look like
Your explanation and the little information you gave us in the PDF is not enough info.
Your explanation and the little information you gave us in the PDF is not enough info.
ASKER
wdosanjos - it gave me a full output of all the pgms and counts. That is what I wanted.
how do I code to get the pgm name to come out as well. in last sample, the output had the pgm name.
- After this, I need to read up on the steps you took.
thx!
how do I code to get the pgm name to come out as well. in last sample, the output had the pgm name.
- After this, I need to read up on the steps you took.
thx!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, and I need to learn to do this!
It would be even more helpful if you could take a screen shot of the top half of the query design grid, with both tables expanded so we can see all of the appropriate fields.