Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Function to count the days in a week

Posted on 2013-06-21
7
Medium Priority
?
502 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 41

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 70

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 70

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

610 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