Sql query with date approach

Transact-SQL
1

I need a Column where condition is Target*X / Y

where I need x and y should come automatically from query.I don't want to manually i enter the numbers X and Y daily.

Y=Total Number of working days in month ,Excluding Sunday count all i.e for Jan, 27 days are working days.
X=Working days till yesterday's date i.e 23(Minus Sunday, 1 to 26 = 26-3=23)

final Result = traget * 23 /27

There is a second approach also
X= Total working days i.e 31 for January Including Sunday
Y=till date working days Including Sunday
26/31

From both Any approach is Good for me,I need the Query for this ..I have date column,Month column.
Using SQL SERVER

2

Please post create table script with inserts of sample data. desired result and what you've tried.

3

Hi

I am working on it

Please give me 1 day

I will have your answer by tomorrow this time

Thanks
Harish

4

@harishgg1 ..what if you i use calendar table..because assume there is Eid ,Diwali holiday so how i will manage that..please give that approach also

5

Hi

This is what I have for Now

Please check the query

Query

-- Date Variable
DECLARE @Test AS DATETIME

SET @Test = '2011-02-15'

-- Yesterdays Date
SELECT Dateadd(dd, -1, @test)

-- whole month
-- WeekDays
-- X
SELECT ( Datediff(dd, (SELECT Dateadd(month, Datediff(month, 0, @Test), 0)), (
SELECT
Dateadd(s, -1, Dateadd(mm, Datediff(m, 0, @Test)+1, 0))))
+ 1 ) - ( Datediff(wk, (SELECT Dateadd(month, Datediff(month, 0, @Test)
, 0)),
(SELECT
Dateadd(s, -1, Dateadd(mm, Datediff(m, 0, @Test)
+ 1, 0)
))) * 2 ) - ( CASE
WHEN Datename(dw, (SELECT
Dateadd(month, Datediff(month, 0,
@Test), 0))) =
'Sunday' THEN 1
ELSE 0
END ) - ( CASE
WHEN
Datename(dw, (SELECT Dateadd(s, -1, Dateadd(mm, Datediff(m, 0,
@Test) +
1
, 0))))
= 'Saturday' THEN 1
ELSE 0
END )

-- yesterdays date
-- Weekdays
-- Y
SELECT ( Datediff(dd, (SELECT Dateadd(month, Datediff(month, 0, @Test), 0)), (
SELECT
Dateadd(dd, -1, @test)))
+ 1 ) - ( Datediff(wk, (SELECT Dateadd(month, Datediff(month, 0, @Test)
, 0)),
(SELECT
Dateadd(dd, -1, @test))) * 2 ) - ( CASE
WHEN
Datename(dw, (SELECT Dateadd(month, Datediff(month, 0, @Test), 0))
) =
'Sunday' THEN 1
ELSE 0
END ) - ( CASE
WHEN
Datename(dw, (SELECT Dateadd(dd, -1, @test))) = 'Saturday' THEN 1
ELSE 0
END
)

-- final Result = target * X /Y
SELECT 'Target' * ( Datediff(dd, (SELECT Dateadd(month, Datediff(month, 0, @Test
), 0)),
(SELECT
Dateadd(s, -1, Dateadd(mm, Datediff(m, 0,
@Test)+
1, 0))))
+ 1 ) - ( Datediff(wk, (SELECT Dateadd(month,
Datediff(month, 0,
@Test), 0)), (SELECT
Dateadd(s, -1,
Dateadd(mm, Datediff(m, 0,
@Test)
+ 1, 0
))
)) * 2 ) - ( CASE
WHEN
Datename(dw, (SELECT Dateadd(month, Datediff(month, 0, @Test), 0))
) =
'Sunday' THEN 1
ELSE 0
END ) -
( CASE
WHEN
Datename(dw, (SELECT Dateadd(s, -1, Dateadd(mm, Datediff(m, 0,
@Test) +
1
, 0))))
= 'Saturday' THEN 1
ELSE 0
END
) /
( Datediff(dd, (SELECT Dateadd(month, Datediff(month, 0, @Test), 0
)), (
SELECT
Dateadd(dd, -1, @test)))
+ 1 ) - ( Datediff(wk, (SELECT Dateadd(month, Datediff(month, 0,
@Test)
, 0)), (SELECT
Dateadd(dd, -1, @test))) * 2 ) - (
CASE
WHEN
Datename(dw, (SELECT Dateadd(month, Datediff(month, 0, @Test), 0))
) =
'Sunday' THEN 1
ELSE
0
END )
- ( CASE
WHEN Datename(dw, (SELECT Dateadd(dd, -1, @test))) = 'Saturday'
THEN 1
ELSE 0
END )

I will let you know the approach for EID etc tomorrow
Please wait if you can

6

Hi

You will have to exclude those holidays
Example
Day
1 Holiday EiD ( Monday )
2 Tuesday
3 Wednesday

In your select
select day where day not in (1)

Please let me know if you understand

We can chat online
I can clear your doubts live

-- Also were you able to understand my query

7

then i have to make a join with calendar table where i will exclude those holidays

8

Exactly .. you got it

Njoy :slight_smile: