• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

To add row values in the same column on certain criteria.

The below table has all dates in the month of jan and no. of cases on each day. My requirement is, whenever the date is holiday (i have a function to check whether it is holiday or not) or weekend then the case_count of that particular date need to be added to the case_count of next business day. And the row with the holiday can be zero or completely taken off. You can the result table is right at the end of this request. If you copy the two tables in excel you will easily understand requirement. Please assist.

Date                                        Case_Count

2012-01-01 00:00:00.000      10
2012-01-02 00:00:00.000      5
2012-01-03 00:00:00.000      37
2012-01-04 00:00:00.000      40
2012-01-05 00:00:00.000      50
2012-01-06 00:00:00.000      43
2012-01-07 00:00:00.000      1
2012-01-08 00:00:00.000      1
2012-01-09 00:00:00.000      49
2012-01-10 00:00:00.000      41
2012-01-11 00:00:00.000      41
2012-01-12 00:00:00.000      40
2012-01-13 00:00:00.000      50
2012-01-14 00:00:00.000      2
2012-01-15 00:00:00.000      1
2012-01-16 00:00:00.000      37
2012-01-17 00:00:00.000      43
2012-01-18 00:00:00.000      46
2012-01-19 00:00:00.000      44
2012-01-20 00:00:00.000      46
2012-01-21 00:00:00.000      1
2012-01-23 00:00:00.000      52
2012-01-24 00:00:00.000      46
2012-01-25 00:00:00.000      49
2012-01-26 00:00:00.000      50
2012-01-27 00:00:00.000      49
2012-01-28 00:00:00.000      1
2012-01-30 00:00:00.000      53
2012-01-31 00:00:00.000      52

The result table should be

Date                                        Cases_Count

      
2012-01-02 00:00:00.000      15
2012-01-03 00:00:00.000      37
2012-01-04 00:00:00.000      40
2012-01-05 00:00:00.000      50
2012-01-06 00:00:00.000      43
2012-01-09 00:00:00.000      51
2012-01-10 00:00:00.000      41
2012-01-11 00:00:00.000      41
2012-01-12 00:00:00.000      40
2012-01-13 00:00:00.000      50
2012-01-16 00:00:00.000      40
2012-01-17 00:00:00.000      43
2012-01-18 00:00:00.000      46
2012-01-19 00:00:00.000      44
2012-01-20 00:00:00.000      46
2012-01-23 00:00:00.000      53
2012-01-24 00:00:00.000      46
2012-01-25 00:00:00.000      49
2012-01-26 00:00:00.000      50
2012-01-27 00:00:00.000      49
2012-01-30 00:00:00.000      54
2012-01-31 00:00:00.000      52
0
nreich
Asked:
nreich
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check this code:
;with data as ( select t.[date]
, t.Case_Count
 , ( select min(i.[date]) from yourtable i
 where i.[date] >= t.[date] 
     and t.[date] >= dateadd(week, 1, t.[date])
    and dbo.is_holiday(i.[date])= 0
  ) apply_date
  from your_table t
)
select apply_date, sum(case_count)
  from data
group by apply_date
order by apply_date

Open in new window

0
 
nreichAuthor Commented:
Thank you Angel for the quick response.

For some reason apply_date is null. the table looks like below.

apply_date    case_count

NULL           959

But I need the table to look like the result table in the question.
0
 
Scott PletcherSenior DBACommented:
If you can ever have more than 3 holidays in a row(?), simply extend the CASE statement as required.

SELECT
    CASE WHEN dbo.is_holiday(date) = 0 THEN date
        WHEN dbo.is_holiday(DATEADD(DAY, 1, date)) = 0 THEN DATEADD(DAY, 1, date)
        WHEN dbo.is_holiday(DATEADD(DAY, 2, date)) = 0 THEN DATEADD(DAY, 2, date)
        ELSE DATEADD(DAY, 3, date) END AS Cases_Date,
    SUM(Case_Count) AS Cases_Count
FROM dbo.tablename
GROUP BY
    CASE WHEN dbo.is_holiday(date) = 0 THEN date
        WHEN dbo.is_holiday(DATEADD(DAY, 1, date)) = 0 THEN DATEADD(DAY, 1, date)
        WHEN dbo.is_holiday(DATEADD(DAY, 2, date)) = 0 THEN DATEADD(DAY, 2, date)
        ELSE DATEADD(DAY, 3, date) END 
ORDER BY
    1

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
nreichAuthor Commented:
Hi ScottPletcher

It gave me the same table again. Same date column and same case_count.
0
 
nreichAuthor Commented:
I modify my question into ..............

whenever it is a non-working day the case_count should be added to next day's case_count.
(no need of next business day. I think in the iteration, the query checks each day is working day or not.)
0
 
Scott PletcherSenior DBACommented:
>> It gave me the same table again. Same date column and same case_count. <<

Verify that the holiday function is being used correctly.  I was just guessing since you didn't post how to use your actual holiday function.

You also didn't post data where I could easily test the query, so I have to rely on you for testing.
0
 
nreichAuthor Commented:
My 'Is_Holiday' fuction is exactly as yours.
I was testing for the month of June.
And it gave me the same table. (As June has no holidays.)
But for the month January it worked. (As january 1st is a holiday)
Thank you for the solution.

However i can still see the cases on 'weekends'.
0
 
nreichAuthor Commented:
Sorry Scott, i mis understood the sentence.
My 'is_holiday' function only includes 'holidays' but not week ends.
0
 
Scott PletcherSenior DBACommented:
Ahh, OK, that makes sense, I knew something else had to be going on.

The issue is if the date is a weekend date AND Monday [and Tues, etc.] is a holiday.  

I'll have to think a little while about the best way to solve that :-) .
0
 
Scott PletcherSenior DBACommented:
Cleanest would probably be to add a computed column to change the date for weekends to Monday.

But, for now, instead of a computed column, we can do it in a derived table / inner subquery.  Then the standard logic can be used to check that date for a holiday:

SELECT
    CASE WHEN dbo.is_holiday(weekday_date) = 0 THEN weekday_date
        WHEN dbo.is_holiday(DATEADD(DAY, 1, weekday_date)) = 0 THEN DATEADD(DAY, 1, weekday_date)
        WHEN dbo.is_holiday(DATEADD(DAY, 2, weekday_date)) = 0 THEN DATEADD(DAY, 2, weekday_date)
        ELSE DATEADD(DAY, 3, weekday_date) END AS Cases_Date,
    SUM(Case_Count) AS Cases_Count
FROM (
    SELECT
        -- if a weekend day (Sat/Sun), push forward to Monday, else keep the original date
        CASE WHEN DATEDIFF(DAY, 0, date) % 7 < 5 THEN date 
            ELSE DATEADD(DAY, 7 - (DATEDIFF(DAY, 0, date) % 7), date) END AS weekday_date
        case_count
    FROM dbo.tablename
) AS derived
GROUP BY
    CASE WHEN dbo.is_holiday(weekday_date) = 0 THEN weekday_date
        WHEN dbo.is_holiday(DATEADD(DAY, 1, weekday_date)) = 0 THEN DATEADD(DAY, 1, weekday_date)
        WHEN dbo.is_holiday(DATEADD(DAY, 2, weekday_date)) = 0 THEN DATEADD(DAY, 2, weekday_date)
        ELSE DATEADD(DAY, 3, weekday_date) END
ORDER BY
    1

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now