Solved

SQL Function to count the days in a week

Posted on 2013-06-21
7
482 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
  • 3
  • 3
7 Comments
 
LVL 39

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:
ScottPletcher 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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:ScottPletcher
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:ScottPletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now