Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

SQL Function to count the days in a week

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
kmc10314
Asked:
kmc10314
  • 3
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
So what exactly is the question?

And why not just use

set @LegitDateCount
 = datediff(d, @StarDateCvt, @endDate)
0
 
Scott PletcherSenior DBACommented:
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
 
kmc10314Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
kmc10314Author Commented:
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
 
Scott PletcherSenior DBACommented:
I believe that's what the function I wrote gives you.

Have you tested it yet??
0
 
kmc10314Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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