Solved

Sql Sytnax to fill in missing month with default of zero

Posted on 2012-04-09
11
363 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:mahpog
  • 4
  • 4
  • 3
11 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37823564
Which tables are the date fields in?

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.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37823744
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_id

Open in new window

0
 

Author Comment

by:mahpog
ID: 37824649
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) as cntpgm, b.program
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
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37824700
Did the query returned the wrong results or did it generate an error?  Please indicate what's missing or the specific error message.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37824706
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mahpog
ID: 37824872
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)
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37824883
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

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37824905
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.
0
 

Author Comment

by:mahpog
ID: 37825026
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!
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 37825058
This brings the pgm name as well:
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,
    b.program
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

Open in new window

0
 

Author Closing Comment

by:mahpog
ID: 37825185
Perfect, and I need to learn to do this!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MSSQL 2014 Query Synthax 8 38
sql query 7 36
Trouble connecting to SqlServer database 4 33
How toselect unique values 3 10
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

20 Experts available now in Live!

Get 1:1 Help Now