SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Quick help in a query
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/16/2014 :  02:58:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/16/2014 :  05:12:25  Show Profile  Reply with Quote
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

Edited by - visakh16 on 01/16/2014 05:12:43
Go to Top of Page

RonnieRahman
Starting Member

United Kingdom
6 Posts

Posted - 01/16/2014 :  05:15:06  Show Profile  Reply with Quote
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;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/16/2014 :  07:19:22  Show Profile  Reply with Quote
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

Edited by - visakh16 on 01/16/2014 07:19:45
Go to Top of Page

RonnieRahman
Starting Member

United Kingdom
6 Posts

Posted - 01/16/2014 :  07:46:24  Show Profile  Reply with Quote
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;
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/16/2014 :  08:05:40  Show Profile  Reply with Quote
Thanks for the help ... it works
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/16/2014 :  23:08:43  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/17/2014 :  02:39:36  Show Profile  Reply with Quote
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

Edited by - visakh16 on 01/17/2014 02:47:50
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/17/2014 :  03:03:52  Show Profile  Reply with Quote
But I want it to return me 2nd January till 2nd Feb, on 3rd Feb it should be change. So I may do that?
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/17/2014 :  03:07:50  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/17/2014 :  03:26:28  Show Profile  Reply with Quote
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 currentprevious monthto 02 day of nextcurrent month bracket.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/17/2014 :  04:29:08  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/17/2014 :  06:59:12  Show Profile  Reply with Quote
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
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/17/2014 :  07:09:54  Show Profile  Reply with Quote
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.


Go to Top of Page

LopakaB
Starting Member

USA
22 Posts

Posted - 01/17/2014 :  14:49:24  Show Profile  Reply with Quote
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

Edited by - LopakaB on 01/17/2014 14:50:58
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/20/2014 :  02:15:59  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/20/2014 :  06:44:03  Show Profile  Reply with Quote
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

Edited by - visakh16 on 01/20/2014 06:45:05
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/20/2014 :  07:02:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/20/2014 :  07:15:51  Show Profile  Reply with Quote
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
Go to Top of Page

Starlet_GT
Yak Posting Veteran

Pakistan
81 Posts

Posted - 01/20/2014 :  07:58:29  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/20/2014 :  08:08:28  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000