Challenging Query - Sum/CASE/Count

[cfcProgrammer]

Hi,

I have a query that is a little confusing but for the expert it may not be too bad.
What I am trying to do is break down my data by fiscal year and display.

I have some logic not right in my query and I've looked at it so long now that I can't seem to figure out where the issue lies.

When I run the select statement alone for the specified date I am getting a result for OTotal of 142 but when I run the entire sql statement I am getting 855... I am thinking it has to do with how I am calculating the months out and adding OTotal but I need some help in identifying that and coming up with a solution.

As always.. any help is greatly appreciated.
Thank you

Here is the sql;

SELECT  
        SUM(totals.[Fiscal01]) AS [2001/02], 
        SUM(totals.[Fiscal02]) AS [2002/03], 
        SUM(totals.[Fiscal03]) AS [2003/04], 
        SUM(totals.[Fiscal04]) AS [2004/05], 
        SUM(totals.[Fiscal05]) AS [2005/06],
        SUM(totals.[Fiscal06]) AS [2006/07], 
        SUM(totals.[Fiscal07]) AS [2007/08]
FROM    (
         --this query splits each Month to its own column.            
         --SUM the results at the outside step
         SELECT  
                 CASE WHEN (I.mm >= '04' and I.yr = '2001') or (I.mm <='03' and I.yr = '2002') THEN I.OTotal ELSE 0 END AS [Fiscal01], 
                 CASE WHEN (I.mm >= '04' and I.yr = '2002') or (I.mm <='03' and I.yr = '2003') THEN I.OTotal ELSE 0 END AS [Fiscal02], 
                 CASE WHEN (I.mm >= '04' and I.yr = '2003') or (I.mm <='03' and I.yr = '2004') THEN I.OTotal ELSE 0 END AS [Fiscal03], 
                 CASE WHEN (I.mm >= '04' and I.yr = '2004') or (I.mm <='03' and I.yr = '2005') THEN I.OTotal ELSE 0 END AS [Fiscal04], 
                 CASE WHEN (I.mm >= '04' and I.yr = '2005') or (I.mm <='03' and I.yr = '2006') THEN I.OTotal ELSE 0 END AS [Fiscal05], 
                 CASE WHEN (I.mm >= '04' and I.yr = '2006') or (I.mm <='03' and I.yr = '2007') THEN I.OTotal ELSE 0 END AS [Fiscal06], 
                 CASE WHEN (I.mm >= '04' and I.yr = '2007') or (I.mm <='03' and I.yr = '2008') THEN I.OTotal ELSE 0 END AS [Fiscal07]
                 	FROM    (
select f.mn as mm,f.yr as yr, count(*) as OTotal
from edt e join mission m on e.mseq=m.mseq join flt f on e.mseq=f.mseq
where e.tcc IN('V2-','V5-','V--','VH-','VI-','VL-','VO-','VP-','VU-','VW-','VX-','VY-')
and (e.call <> '' or e.name <>'' or e.side<>'')
and e.src<>'U'
and convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '04/01/2007 ' and '03/31/2008'
group by f.yr,f.mn)I
)totals

If you need me to send over some test data let me know.
Again thank you


cfcProgrammer

 

[gmmastros]

Try this...

Select Sum(Case When I.FiscalMonth >= '20010401' And I.FiscalMonth < '20020301' Then I.OTotal Else 0 End) As [2001/02],
       Sum(Case When I.FiscalMonth >= '20020401' And I.FiscalMonth < '20030301' Then I.OTotal Else 0 End) As [2002/03],
       Sum(Case When I.FiscalMonth >= '20030401' And I.FiscalMonth < '20040301' Then I.OTotal Else 0 End) As [2003/04],
       Sum(Case When I.FiscalMonth >= '20040401' And I.FiscalMonth < '20050301' Then I.OTotal Else 0 End) As [2004/05],
       Sum(Case When I.FiscalMonth >= '20050401' And I.FiscalMonth < '20060301' Then I.OTotal Else 0 End) As [2005/06],
       Sum(Case When I.FiscalMonth >= '20060401' And I.FiscalMonth < '20070301' Then I.OTotal Else 0 End) As [2006/07],
       Sum(Case When I.FiscalMonth >= '20070401' And I.FiscalMonth < '20080301' Then I.OTotal Else 0 End) As [2007/08]
From   (
         Select Convert(DateTime, Convert(VarChar(4), f.yr) + Convert(VarChar(2), f.mn) + '01') as FinscalMonth, 
                count(*) as OTotal
         from   edt e 
                join mission m 
                   on e.mseq=m.mseq 
                join flt f 
                   on e.mseq=f.mseq
         where  e.tcc IN('V2-','V5-','V--','VH-','VI-','VL-','VO-','VP-','VU-','VW-','VX-','VY-')
                and (e.call <> '' or e.name <>'' or e.side<>'')
                and e.src<>'U'
                and convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
                   between '04/01/2007 ' and '03/31/2008'
         group by f.yr,f.mn
         ) As I

If this works for you, I will explain why I changed the things I did.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cfcProgrammer]

Hi George... you've helped save me in the past.. thanks

I had to change the statement just a bit... i was getting an error on the '01' so I changed it to

+ convert(VarChar(2), f.dy)

and then I had to add the f.dy to the group by clause.

here is the full sql again...

Select Sum(Case When I.FiscalMonth >= '20010401' And I.FiscalMonth < '20020301' Then I.OTotal Else 0 End) As [2001/02],
       Sum(Case When I.FiscalMonth >= '20020401' And I.FiscalMonth < '20030301' Then I.OTotal Else 0 End) As [2002/03],
       Sum(Case When I.FiscalMonth >= '20030401' And I.FiscalMonth < '20040301' Then I.OTotal Else 0 End) As [2003/04],
       Sum(Case When I.FiscalMonth >= '20040401' And I.FiscalMonth < '20050301' Then I.OTotal Else 0 End) As [2004/05],
       Sum(Case When I.FiscalMonth >= '20050401' And I.FiscalMonth < '20060301' Then I.OTotal Else 0 End) As [2005/06],
       Sum(Case When I.FiscalMonth >= '20060401' And I.FiscalMonth < '20070301' Then I.OTotal Else 0 End) As [2006/07],
       Sum(Case When I.FiscalMonth >= '20070401' And I.FiscalMonth < '20080301' Then I.OTotal Else 0 End) As [2007/08]
From   (
         Select convert(DateTime, convert(VarChar(4), f.yr) + convert(VarChar(2), f.mn) + convert(VarChar(2), f.dy)) as FiscalMonth,
                count(*) as OTotal
         from   edt e
                join mission m
                   on e.mseq=m.mseq
                join flt f
                   on e.mseq=f.mseq
         where  e.tcc IN('V2-','V5-','V--','VH-','VI-','VL-','VO-','VP-','VU-','VW-','VX-','VY-')
                and (e.call <> '' or e.name <>'' or e.side<>'')
                and e.src<>'U'
                and convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
                   between '04/01/2007 ' and '03/31/2008'
         group by f.yr,f.mn,f.dy
         ) As I

However, I am getting this message....

[highlight][COLOR=blue white]Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[/color][/highlight]

When we get this working I would like you to walk me through why you had to change the stuff you did. That way I'll understand it and be able to use it again in the future. I just want to say again. I do appreciate the help... thanks!!



cfcProgrammer

 

[gmmastros]

Well.... you really want to report on fiscal years, so you don't care about the day, which is why I originally used '01'. This allows us to count the rows for the month/year combination. Unfortunately, I missed a little step. You see, I am converting the month to varchar, but not preserving any leading 0's, which is important.

Anyway, try this...

Select Sum(Case When I.FiscalMonth >= '20010401' And I.FiscalMonth < '20020301' Then I.OTotal Else 0 End) As [2001/02],
       Sum(Case When I.FiscalMonth >= '20020401' And I.FiscalMonth < '20030301' Then I.OTotal Else 0 End) As [2002/03],
       Sum(Case When I.FiscalMonth >= '20030401' And I.FiscalMonth < '20040301' Then I.OTotal Else 0 End) As [2003/04],
       Sum(Case When I.FiscalMonth >= '20040401' And I.FiscalMonth < '20050301' Then I.OTotal Else 0 End) As [2004/05],
       Sum(Case When I.FiscalMonth >= '20050401' And I.FiscalMonth < '20060301' Then I.OTotal Else 0 End) As [2005/06],
       Sum(Case When I.FiscalMonth >= '20060401' And I.FiscalMonth < '20070301' Then I.OTotal Else 0 End) As [2006/07],
       Sum(Case When I.FiscalMonth >= '20070401' And I.FiscalMonth < '20080301' Then I.OTotal Else 0 End) As [2007/08]
From   (
         Select convert(DateTime, convert(VarChar(4), f.yr) + Right('00' + convert(VarChar(2), f.mn), 2) + '01') as FiscalMonth,
                count(*) as OTotal
         from   edt e
                join mission m
                   on e.mseq=m.mseq
                join flt f
                   on e.mseq=f.mseq
         where  e.tcc IN('V2-','V5-','V--','VH-','VI-','VL-','VO-','VP-','VU-','VW-','VX-','VY-')
                and (e.call <> '' or e.name <>'' or e.side<>'')
                and e.src<>'U'
                and convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
                   between '04/01/2007 ' and '03/31/2008'
         group by f.yr,f.mn
         ) As I

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom