Solved

SQL Function to count the days in a week

Posted on 2013-06-21
7
493 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

737 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