Quick help in a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Quick help in a query

Author

Topic

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-16 : 02:58:10

Hi there,

I want to extract data from a table where date should be equal to & greater than 02 day of every month. And should be less than 03 of every next month.

it's mean I need data from 02 day of current month to 02 day of next month bracket.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 05:12:25

use a filter like below


WHERE datefield >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),2)
AND datefield < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,3)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

RonnieRahman
Starting Member

6 Posts

Posted - 2014-01-16 : 05:15:06

Hiya
See my code below - I hope this will help to get started.
Cheers
Ronnie


-- Create a Tep table for date demo
CREATE TABLE #PlayWithDate (
			Column1  VARCHAR(10)
			,UpdateDate DATE
			)
GO
-- INSERT sample Data into above temp table
INSERT INTO #PlayWithDate (Column1,UpdateDate)
SELECT 'Test01',GETDATE()
UNION ALL
SELECT 'Test02','20140101'
UNION ALL
SELECT 'Test03','20140102'
UNION ALL
SELECT 'Test04','20140103'
UNION ALL
SELECT 'Test05','20140104'
UNION ALL
SELECT 'Test06','20140204'
UNION ALL
SELECT 'Test06','20140201'
UNION ALL
SELECT 'Test06','20140217'
GO

SELECT 
	 Column1
	,UpdateDate
FROM #PlayWithDate
GROUP BY 
		 Column1
		,UpdateDate
HAVING UpdateDate >= DATEADD(dd,1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) -- second day of current month
	AND UpdateDate < DATEADD(dd,33,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) -- third day of next month
	ORDER BY UpdateDate;
	GO
-- DROP Temp table
DROP TABLE #PlayWithDate;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 07:19:22

quote:

Originally posted by RonnieRahman

Hiya
See my code below - I hope this will help to get started.
Cheers
Ronnie


-- Create a Tep table for date demo
CREATE TABLE #PlayWithDate (
			Column1  VARCHAR(10)
			,UpdateDate DATE
			)
GO
-- INSERT sample Data into above temp table
INSERT INTO #PlayWithDate (Column1,UpdateDate)
SELECT 'Test01',GETDATE()
UNION ALL
SELECT 'Test02','20140101'
UNION ALL
SELECT 'Test03','20140102'
UNION ALL
SELECT 'Test04','20140103'
UNION ALL
SELECT 'Test05','20140104'
UNION ALL
SELECT 'Test06','20140204'
UNION ALL
SELECT 'Test06','20140201'
UNION ALL
SELECT 'Test06','20140217'
GO

SELECT 
	 Column1
	,UpdateDate
FROM #PlayWithDate
GROUP BY 
		 Column1
		,UpdateDate
HAVING UpdateDate >= DATEADD(dd,1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) -- second day of current month
	AND UpdateDate < DATEADD(dd,33,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) -- third day of next month
	ORDER BY UpdateDate;
	GO
-- DROP Temp table
DROP TABLE #PlayWithDate;

what if month is Jan or Feb?
Jan has 31 days, Feb may have 28/29 days depending on year so how can you generalize and add 33 days always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

RonnieRahman
Starting Member

6 Posts

Posted - 2014-01-16 : 07:46:24

quote:
Originally posted by visakh16

quote:
Originally posted by RonnieRahman

Hiya
See my code below - I hope this will help to get started.
Cheers
Ronnie

-- Create a Tep table for date demo
CREATE TABLE #PlayWithDate (
Column1 VARCHAR(10)
,UpdateDate DATE
)
GO
-- INSERT sample Data into above temp table
INSERT INTO #PlayWithDate (Column1,UpdateDate)
SELECT 'Test01',GETDATE()
UNION ALL
SELECT 'Test02','20140101'
UNION ALL
SELECT 'Test03','20140102'
UNION ALL
SELECT 'Test04','20140103'
UNION ALL
SELECT 'Test05','20140104'
UNION ALL
SELECT 'Test06','20140204'
UNION ALL
SELECT 'Test06','20140201'
UNION ALL
SELECT 'Test06','20140217'
GO

SELECT
Column1
,UpdateDate
FROM #PlayWithDate
GROUP BY
Column1
,UpdateDate
HAVING UpdateDate >= DATEADD(dd,1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) -- second day of current month
AND UpdateDate < DATEADD(dd,33,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) -- third day of next month
ORDER BY UpdateDate;
GO
-- DROP Temp table
DROP TABLE #PlayWithDate;

what if month is Jan or Feb?
Jan has 31 days, Feb may have 28/29 days depending on year so how can you generalize and add 33 days always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thank you ever so much for pointing this - in my previous reply: I did something quick and dirty. I have adopted your code and revised the SELECT statement below. Many Thanks
-- Ronnie


SELECT 
	 Column1
	,UpdateDate
FROM #PlayWithDate
GROUP BY 
		 Column1
		,UpdateDate
HAVING UpdateDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1) -- second day of current month
	AND UpdateDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,2) --  third day of next month
	ORDER BY UpdateDate;

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-16 : 08:05:40

Thanks for the help ... it works

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-16 : 23:08:43

Guys ... there is a confusion. What happened if the month changed? today if i run this query it works fine but if i execute it on 1st Feb than what happened? I have to accumulate the data till 2nd Feb, after that I want it to reset it self and start from 2nd Feb to 2nd March.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 02:39:36

quote:
Originally posted by Starlet_GT

Guys ... there is a confusion. What happened if the month changed? today if i run this query it works fine but if i execute it on 1st Feb than what happened? I have to accumulate the data till 2nd Feb, after that I want it to reset it self and start from 2nd Feb to 2nd March.

if you execute it on feb 2nd GETDATE will return 2014-02-02 so the date filter will be like below


...
WHERE datefield >= '2014-02-02'
AND datefield <'2014-03-03'
..

as DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1) will return '2014-02-02' and
DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,2) will return '2014-03-03'
so it will return data from 2nd Feb 2014 to up and until 3rd Mar 2014 midnight (start of day)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-17 : 03:03:52

But I want it to return me 2nd January till 2nd Feb, on 3rd Feb it should be change. So I may do that?

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-17 : 03:07:50

Actually I am doing billing of customers, I have to accumulate whole month bill by summing up transactions. my bill day for each customer is fixed i.e. 2nd of every month. The system is running on real time basis so when ever a customer request it show all the transactions from 02 day of every month.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 03:26:28

quote:
Originally posted by Starlet_GT

But I want it to return me 2nd January till 2nd Feb, on 3rd Feb it should be change. So I may do that?

then you need to modify suggestion as below


SELECT 
	 Column1
	,UpdateDate
FROM #PlayWithDate
GROUP BY 
		 Column1
		,UpdateDate
HAVING UpdateDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,1) -- second day of current month
	AND UpdateDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),2) --  third day of next month
	ORDER BY UpdateDate;

and should have put your question as

it's mean I need data from 02 day of ~~current~~previous monthto 02 day of ~~next~~current month bracket.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-17 : 04:29:08

Thanks for the response & sorry for my poor english.

Well I have to start my calculation from 2nd of each month & have to accumulate it till 2nd of next month. If I execute query which you provided my earlier, it is working fine today, but if month change this wont work. So how it is possible to run query with same conditions when month changes. After 2nd of next month, the billing cycle should be changed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 06:59:12

quote:
Originally posted by Starlet_GT

Thanks for the response & sorry for my poor english.

Well I have to start my calculation from 2nd of each month & have to accumulate it till 2nd of next month. If I execute query which you provided my earlier, it is working fine today, but if month change this wont work. So how it is possible to run query with same conditions when month changes. After 2nd of next month, the billing cycle should be changed.

I didnt understand this
The logic is based on GETDATE() so it should work fine for all months
Can you explain what you mean by it doesnt work with an example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-17 : 07:09:54

If I use following in my where clause it will work fine until month changes

where UpdateDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,1)
AND UpdateDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),2)

Assume today's date ... today is 17th January, so if I run this query it will work fine for me. It will accumulate billing starting from 2nd January till to date.

Now If I run this query on 1st Feb, what will happened? as month changes it will start returning me as follows.

where UpdateDate >= '2014-02-02'
AND UpdateDate < '2014-03-03'

whereas I am still expecting my query to fetch me records between 2nd January to 2nd Feb. After 2nd Feb it should change month.

if still my logic is un-understandable please let me know, I will try to explain it again.

LopakaB
Starting Member

22 Posts

Posted - 2014-01-17 : 14:49:24

Change having to:
having UpdateDate >= DATEADD(dd,1,DATEADD(mm,DATEDIFF(mm,0,(CASE WHEN DATEPART(d, getdate()) = 1 THEN DATEADD(d, -1, getdate()) ELSE getdate() END)),0)) -- second day of current month
AND UpdateDate < DATEADD(dd,33,DATEADD(mm,DATEDIFF(mm,0,(CASE WHEN DATEPART(d, getdate()) = 1 THEN DATEADD(d, -1, getdate()) ELSE getdate() END)),0)) -- third day of next month

Lopaka

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-20 : 02:15:59

Lopaka, currently this query is working fine, can you elaborate how it is working? What is 1 & -1 means in this statement. What happened when it will be march or april?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 06:44:03

quote:
Originally posted by Starlet_GT

If I use following in my where clause it will work fine until month changes

where UpdateDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,1)
AND UpdateDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),2)

Assume today's date ... today is 17th January, so if I run this query it will work fine for me. It will accumulate billing starting from 2nd January till to date.

Now If I run this query on 1st Feb, what will happened? as month changes it will start returning me as follows.

where UpdateDate >= '2014-02-02'
AND UpdateDate < '2014-03-03'

whereas I am still expecting my query to fetch me records between 2nd January to 2nd Feb. After 2nd Feb it should change month.

if still my logic is un-understandable please let me know, I will try to explain it again.

Nope thats wrong
see this illsutration
just replace the GETDATE() with your date and see



DECLARE @dt datetime = '2014-02-01' 

SELECT DATEADD(mm,DATEDIFF(mm,0,@dt)-1,1), DATEADD(mm,DATEDIFF(mm,0,@dt),2) --same logic replacing GETDATE by date you wanted

output
-------------
2014-01-02 00:00:00.000	2014-02-03 00:00:00.000

it returns you range as from 2nd Jan 2014 to 3rd Feb 2014 which was what you were after!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-20 : 07:02:42

Well, if you change as follows:

DECLARE @dt datetime = '2014-02-02'

SELECT DATEADD(mm,DATEDIFF(mm,0,@dt)-1,1), DATEADD(mm,DATEDIFF(mm,0,@dt),2) --same logic replacing GETDATE by date you wanted

output
-------------
2014-01-02 00:00:00.000 2014-02-03 00:00:00.000

desired output
--------------
2014-02-02 00:00:00.000 2014-03-03 00:00:00.000

how we can achieve this. As after every 02 day of each month, billing cycle will be reset.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 07:15:51

quote:
Originally posted by Starlet_GT

Well, if you change as follows:

DECLARE @dt datetime = '2014-02-02'

SELECT DATEADD(mm,DATEDIFF(mm,0,@dt)-1,1), DATEADD(mm,DATEDIFF(mm,0,@dt),2) --same logic replacing GETDATE by date you wanted

output
-------------
2014-01-02 00:00:00.000 2014-02-03 00:00:00.000

desired output
--------------
2014-02-02 00:00:00.000 2014-03-03 00:00:00.000

how we can achieve this. As after every 02 day of each month, billing cycle will be reset.

Sorry I didnt get that
wasnt your requirement to get from 2nd of previous month to 3rd of current month ALWAYS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2014-01-20 : 07:58:29

On the other hand this is working close to desire result but days are changing in other criteria

DECLARE @dt datetime = '2014-01-10'

SELECT TOP 1
DATEADD(dd,1,DATEADD(mm,DATEDIFF(mm,0,(CASE WHEN DATEPART(d, @dt) = 1 THEN DATEADD(d, -1, @dt) ELSE @dt END)),0)) AS STARTDATE,
DATEADD(dd,33,DATEADD(mm,DATEDIFF(mm,0,(CASE WHEN DATEPART(d, @dt) = 1 THEN DATEADD(d, -1, @dt) ELSE @dt END)),0)) AS ENDDATE
FROM TABLE

output 1
-------------------
STARTDATE ENDDATE
2014-01-02 00:00:00.000 2014-02-03 00:00:00.000

In next month results are slightly different as follows:

DECLARE @dt datetime = '2014-02-10'

SELECT TOP 1
DATEADD(dd,1,DATEADD(mm,DATEDIFF(mm,0,(CASE WHEN DATEPART(d, @dt) = 1 THEN DATEADD(d, -1, @dt) ELSE @dt END)),0)) AS STARTDATE,
DATEADD(dd,33,DATEADD(mm,DATEDIFF(mm,0,(CASE WHEN DATEPART(d, @dt) = 1 THEN DATEADD(d, -1, @dt) ELSE @dt END)),0)) AS ENDDATE
FROM TABLE

output
-----------------
STARTDATE ENDDATE
2014-02-02 00:00:00.000 2014-03-06 00:00:00.000

In third month required results returned:

DECLARE @dt datetime = '2014-03-10'

output
--------------------------
STARTDATE ENDDATE
2014-03-02 00:00:00.000 2014-04-03 00:00:00.000

I am unable to understand why dates are changing in ENDDATE column. Can we control it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 08:08:28

Can you CLEARLY explain what your exact requirement is? tells us with few examples what all dates you want query to run for what all date range.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources