Solved

SQL Function to count the days in a week

Posted on 2013-06-21
7
495 Views
Last Modified: 2013-06-21
Hello, Experts
I have create a function to do following job:
The function is to count the days when the month change occurs during a week.

For example, if the week starts on 2012-12-31 and ends on 2013-01-06,
the function will return 6 days. If the week started 2012-12-28 and ends on 2013-01-03
then the function will return 3 days.
So the function is returning # of days if the days between the start date and end date
have the month of the end date.

Here is the code

- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date, ,>
-- Description:   <Description, ,>
-- =============================================
ALTER FUNCTION fn_BasalBolusDate
(
      @StartDate VARCHAR(10),
      @EndDate VARCHAR(10)
)
--
--EXEC fn_BasalBolusDate ('2012-12-31', '2013-01-07')
 
RETURNS INT
 
AS
 
BEGIN
 
DECLARE @StarDateCvt DATETIME
SET @StarDateCvt = CONVERT(DATETIME, @StartDate)
 
DECLARE @EndDateCvt DATETIME
SET @EndDateCvt = CONVERT(DATETIME, @EndDate)
 
 
DECLARE @LegitDateCount INT
DECLARE @ActDate DATETIME
SET @ActDate = @StarDateCvt
DECLARE @EndDtYrMonth VARCHAR(10)  
SET @EndDtYrMonth =
      CONVERT(VARCHAR(4), DATEPART(YY, @EndDateCvt)) + '-' +
      CASE
      WHEN LEN(CONVERT(VARCHAR, DATEPART(MM, @EndDateCvt))) = 1
      THEN '0' + CONVERT(VARCHAR(1), DATEPART(MM, @EndDateCvt))
      ELSE CONVERT(VARCHAR(2), DATEPART(MM, @EndDateCvt)) END
 
DECLARE @ActDtYrMonth VARCHAR(10)  
SET @ActDtYrMonth =
      CONVERT(VARCHAR(4), DATEPART(YY, @ActDate)) + '-' +
      CASE
      WHEN LEN(CONVERT(VARCHAR, DATEPART(MM, @ActDate))) = 1
      THEN '0' + CONVERT(VARCHAR(1), DATEPART(MM, @ActDate))
      ELSE CONVERT(VARCHAR(2), DATEPART(MM, @ActDate)) END
 
WHILE (@ActDate <= @EndDateCvt)
BEGIN
      IF (@ActDtYrMonth = @EndDtYrMonth)
            BEGIN
                  SET @LegitDateCount = @LegitDateCount + 1
                  SET @ActDate = DATEADD(Day, 1, @ActDate)
            END
      ELSE
            BEGIN
                  SET @ActDate = DATEADD(Day, 1, @ActDate)
            END
     
END
 
RETURN @LegitDateCount
 
END
 
GO

Open in new window


Let me know if you have any question
0
Comment
Question by:kmc10314
[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
  • 3
  • 3
7 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39266308
So what exactly is the question?

And why not just use

set @LegitDateCount
 = datediff(d, @StarDateCvt, @endDate)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39266558
ALTER FUNCTION fn_BasalBolusDate
(
      @StartDate VARCHAR(10),
      @EndDate VARCHAR(10)
)
/* sample executions
SELECT dbo.fn_BasalBolusDate ('2012-12-31', '2013-01-06')
SELECT dbo.fn_BasalBolusDate ('2012-12-28', '2013-01-03')
SELECT dbo.fn_BasalBolusDate ('2012-12-14', '2012-12-20')
*/
 
RETURNS INT
 
AS
 
BEGIN

RETURN (
    SELECT CASE
        WHEN DAY(@EndDate) < DAY(@StartDate)
        THEN DAY(@EndDate)
        ELSE DATEDIFF(DAY, @StartDate, @EndDate) + 1
    END --CASE
)
 
END --FUNCTION
0
 

Author Comment

by:kmc10314
ID: 39266564
There is a while loop in the middle.
The loop loops through the date between start date and end date.
It looks at individual dates between start date and end date and make sure if that date is in same month with the end date

For example,
Let's say the day starts on 2012-12-28 and ends on 2013-01-03.
The loop starts at 2012-12-28 and looks if that date's month is same as the month of end date. Since 2012-12-28's month is December not January, it does not get counted.
It moves to next date (2012-12-28). Still not same... and move on..

So it's looking at basically every date between the begin date and end date and count if the date's month is same as the month of end date
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:kmc10314
ID: 39266638
Scott,
The result that I'm looking for is something like this

If the week starts on 2012-12-28 and ends on 2013-01-03,
then there are 3 days in a week that's in January
(2013-01-01, 2013-01-02, 2013-01-03)
I want the number of days that are in January, so 3

If the week starts on 2013-01-29 and ends on 2013-02-04,
then there are 4 days in a week that's in January
(2013-02-01, 2013-02-02, 2013-02-03, 2013-02-04)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39266646
I believe that's what the function I wrote gives you.

Have you tested it yet??
0
 

Author Comment

by:kmc10314
ID: 39266729
Scott,
Thank you for your help.
It did work... I guess I just did not fully understand the text as I thought you need the loo
However, if you don't mind, can you explain how it worked?

Thanks!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39266915
It checks to see if the end day is less than the starting day -- which means the end day must be in a later month.

If so, it returns the ending day -- i.e., if it's the 1st of the month it returns 1, the 2nd it returns 2, etc..

Otherwise, it does a normal DATEDIFF to calc the number of days.

This always work reliably for a single week only, which is what you indicated you needed in the q :-) .
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

687 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