Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CASE statement or LOOP statement that find unduplicated new entries for the year

Posted on 2013-06-11
9
Medium Priority
?
262 Views
Last Modified: 2013-06-12
I have a table named tblAttendance in SQL 2008 that has a listing of all students and their hours.  I would like to make a CASE statement where I can find all the unduplicated records that entered hours for the first time each month of our fiscal year.

Using the following fiscal month pattern order starting with June, I would like to find an unduplicated recordcount of all records that have a total > 0 for the first time.

JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMEBER
DECEMBER
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE

For example,  if two students have entered hours for the first time in JULY, then the total for that month would be 2, since there are two records that have a total > 0

But once they are counted they would not be counted in all the months after.  Hence the totals for each month would be unduplicated.
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39238565
It would be helpful to get a sampling of data from your tblAttendance table, and the results you would expect based on that sample.
0
 

Author Comment

by:al4629740
ID: 39238672
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39238722
For the sample you just posted, please explain how you get from the source data to the expected result, because it is not at all clear.
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:al4629740
ID: 39238735
The code will need to start at June and find all records that have a total > 0 for the month of June.

In the case of my example, I apologize the output I made was not actual.  I have attached the correct one with real results.  For the month of September as an example, there is one.
desired-output.xlsx
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 2000 total points
ID: 39239496
try this one... please send feedback...
With FiscalYear (MonthID, Month) As (
  select 1, 'JULY'
  union all
  select 2, 'AUGUST'
  union all
  select 3, 'SEPTEMBER'
  union all
  select 4, 'OCTOBER'
  union all
  select 5, 'NOVEMEBER'
  union all
  select 6, 'DECEMBER'
  union all
  select 7, 'JANUARY'
  union all
  select 8, 'FEBRUARY'
  union all
  select 9, 'MARCH'
  union all
  select 10, 'APRIL'
  union all
  select 11, 'MAY'
  union all
  select 12, 'JUNE'
)
Select FiscalYear.Month, Count(tblAttendance.Total) As Stundents
From FiscalYear, tblAttendance
Where UPPER(RTRIM(LTRIM(tblAttendance.Month))) = FiscalYear.Month
  And tblAttendance.Total > 0
  And tblAttendance.Fiscal = 2010 -- or whatever you want
Group By FiscalYear.Month
Order By FiscalYear.MonthID;

Open in new window

0
 

Author Comment

by:al4629740
ID: 39240059
Thank you, it looks like it works.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39240568
@bakady "But once they are counted they would not be counted in all the months after.  "
how is this met in your proposal?
(and: no ansi joins?)
0
 

Author Comment

by:al4629740
ID: 39241732
Yes, I went through too quickly and didn't notice it was wrong until after I awarded points
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243001
@al4629740 you may request that a question be re-opened if you make a mistake

You may also close a question if you have not found a solution (or have found it by another method) - but if closing this way some note on why is requested.

And/or there is the 'grade' (A, B, C) e.g.
"C should only be given for an incomplete solution that does not fully address or answer the question" see "How to Grade" - if not awarding a grade of A a note as to why is asked for. e.g. "did not address a key requirement"

i.e. there are several options for you on how to close a question. On the right of every page there is a vertical panel, at the end of this is help - there are many useful articles there, e.g.

Ideally of course, you just tell the responding experts that the proposals haven't worked.

I trust the other related question you opened does provide the solution you seek.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 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