Calculation based on Month 1

[ter79]

I'm trying to do calculation on two columns within the table.. (DDL for
creating and inserting test data below)

I need to get the difference between the last entry and first entry on the
columns "E" and "M" and it must be grouped by each month. I also need to
ignore the rows where the Total is equal or less than 0 (zero).

How can I achieve this?

create table [tblWeek](
[E] [int] null,
[M] [int] null,
[Total] [int] null,
[Date] [date] null,
[LastTotal] [int] null,
) on [PRIMARY]

go

insert into tblWeek (E,M,Total, Date, LastTotal)
values (967652,1086346,2053998,'2010-09-20', 10765)

insert into tblWeek (E,M,Total, Date, LastTotal)
values (962932,1080301,2043233,'2010-09-13',0)

insert into tblWeek (E,M,Total, Date, LastTotal)
values (962932,080301,2043233,'2010-09-06',5490)

insert into tblWeek (E,M,Total, Date, LastTotal)
values (960808,1076935,2037743,'2010-08-30',7692)

insert into tblWeek (E,M,Total, Date, LastTotal)
values (958864,1071187,2030051,'2010-08-23',15336)

insert into tblWeek (E,M,Total, Date, LastTotal)
values (954219,1060496,2014715,'2010-08-09',6715)

insert into tblWeek (E,M,Total, Date, LastTotal)
values (952196,1055804,2008000,'2010-08-02',6457)

insert into tblWeek (E,M,Total, Date, LastTotal)
values (949797,1051746,2001543,'2010-07-26',-31294)

 

[gmmastros]

Based on your sample data, what is the expected results?

-George

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

 

[ter79]

Taking the entries for the month of September as the example of the results.

values (967652,1086346,2053998,'2010-09-20', 10765)
values (962932,1080301,2043233,'2010-09-13',0)
values (962932,1080301,2043233,'2010-09-06',5490)

Returned results:

"E", "M ", Month
4720, 6045, September

and so forth for each month that I have data for. I will not have data for more than 9 months, therefore I will not have the same month more than once

 

[ter79]

I should have added that it's a subtraction calculation that I need to do. Sorry for the confusion

 

[gmmastros]

Hmmmm.....

I successfully constructed a query that returns the values you are looking for. This query assumes you are using SQL Server 2005 or newer because it uses the row number function in order to get the first and last entry for each month. My solution also uses a common table expression which was first introduced in SQL2005.

There are a couple things you should be aware of.

1. If there is only one qualifying row in a month, it will be the first row and the last row. When you do the calculations, the difference in E and M will always be zero (because any number subtracted from itself will always be 0.

2. If there are qualifying rows that have the same date (but different E and M values) and that row happens to be the first or last, there is no guarantee which row will be used.

To construct the query, I first thought about how I would get the first and last row for each month. To accomplish this, I used the Row_Number function where I partitioned the data in to months and ordered by the date. This essentially gives me a sequential number starting at 1 that repeats each month. For example:

Select E, M, Month(Date) As MonthNumber,
       Row_Number() Over (Partition By Month(Date) Order BY Date) As FirstRow
From   tblWeek
Where  LastTotal > 0

Notice how the first row column is a sequential number that resets itself each month. Also notice that I can use the "1's" for the first row. To find the last row for each month, I can do the same thing but order the date descending, like this:

Select E, M, Month(Date) As MonthNumber,
       Row_Number() Over (Partition By Month(Date) Order BY Date) As FirstRow,
       Row_Number() Over (Partition By Month(Date) Order BY Date [!]DESC[/!]) As LastRow
From   tblWeek
Where  LastTotal > 0

If FirstRow = 1, that's the first entry for the month. If LastRow = 1, that's the last entry for the month.

Next, we can filter the data for just those 2 rows, like this:

;With cte As
(
  Select E, M,
         Month(Date) As MonthNumber,
         Row_Number() Over (Partition BY Month(Date) Order By Date) As FirstRow,
         Row_Number() Over (Partition By Month(Date) Order By Date DESC) As LastRow
  From   tblWeek
  Where  LastTotal > 0
)
Select  *
From    cte
Where   FirstRow = 1 or LastRow = 1

Now that we have identified the data we need to calculate, the last and final step is to actually perform the calculation. Ordinarily, you would think that a subtraction would be problematic, especially since we have the values on different rows. But, since we are guaranteed to have 2 rows, we can multiply one of them by -1 and then add them using the SUM aggregate function, like this:

;With cte As
(
  Select E, M,
         Month(Date) As MonthNumber,
         Row_Number() Over (Partition BY Month(Date) Order By Date) As FirstRow,
         Row_Number() Over (Partition By Month(Date) Order By Date DESC) As LastRow
  From   tblWeek
  Where  LastTotal > 0
)
Select  Sum(Case When FirstRow = 1 Then -E Else E End) As E_Calculation,
        Sum(Case When FirstRow = 1 Then -M Else M End) As M_Calculation,
        MonthNumber 
From    cte
Where   FirstRow = 1 or LastRow = 1
Group By MonthNumber

Hopefully my explanation makes sense. There are several semi-advanced concepts involved here. If anything doesn't make sense, please let me know.

-George

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

 

[ter79]

I understand what you are doing, just one more thing. Can I use DATEDIFF(mm,Date, GETDATE()) <= 9 to make sure that I only get the last 9 months in the where clause.

 

[gmmastros]

Can I use DATEDIFF(mm,Date, GETDATE()) <= 9 to make sure that I only get the last 9 months in the where clause.

Sure. If you do this, make sure you put it in the cte part (where we filter for LastTotal > 0).



-George

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

 

[markros]

Use Date > dateadd(month,-9 + datediff(month, '19010101', getdate()),'19010101') instead of datediff check to make the query sargable.

PluralSight Learning Library