Help need to get the previous dates dynamically in SQL

bhadriau

SOLVED CLOSED ACTIVE ANSWERED
User: bhadriau
Posted: on Jun 29, 2016 10:02 AM
Forum: SQL Server
Answer: 1
Views: 2794

Dear All,

Could you please help me with the below query :-

Here I am not understaning how to get the Date values AS shows below

This query is scheduled to run on every day Night at 12AM - Monday to Friday. If the query Runs on Monday night than I should get the data for the Friday, Saturday and Sunday dates, however other wise just get the data for the prious date like, If the query runs on Tuesday night than I should get the Monday data and If the Query runs on Wednesday night than i should get the Tuesday data.. similar way it goes on till Friday.

We should not hardcode any dates here

Create Table #Users1
(
UniQueNumber Varchar(50),
FName varchar (50),
LName Varchar (50),
CreateDate Datetime
)
  
INSERT INTO #Users1 Values ('44354','Kumar','Shruthi-hh','06-21-2016 00:10:000')
INSERT INTO #Users1 Values ('44254','Shankar','Raj','06-22-2016 01:18:000')
INSERT INTO #Users1 Values ('34154','Babu','Swamy-k','06-23-2016 06:10:000')
INSERT INTO #Users1 Values ('35054','Mani','Kanta','06-24-2016 06:10:000')
INSERT INTO #Users1 Values ('35055','dsfdf','ett','06-25-2016 06:10:000')
INSERT INTO #Users1 Values ('35056','sss','sfd','06-26-2016 06:10:000')
INSERT INTO #Users1 Values ('35057','jkhk','sdd','06-27-2016 06:10:000')
INSERT INTO #Users1 Values ('35058','eer','s','06-30-2016 06:10:000')
INSERT INTO #Users1 Values ('35059','er','j','07-01-2016 06:10:000')

We should not hardcode any dates here 

If the query runs on Monday (27th June) then I should get the result below:-

UniQueNumber     	Fname         LName     Date 
  35054   	        Mani	     Kanta      06-24-2016 06:10:000
  35055			dsfdf	     ett	06-25-2016 06:10:000
  35056			sss	     sfd        06-26-2016 06:10:000

If the query runs on Tuesday (28th June) then I should get the result below:-
UniQueNumber     	Fname         LName     Date 
  35057                   jkhk         sdd      06-27-2016 06:10:000


If the query runs on Friday(1st July) then I should get the result below:-
UniQueNumber     	Fname         LName     Date 
   35058                  eer            s      06-30-2016 06:10:000


If the query runs on Monday(4th July) then I should get the result below:-
UniQueNumber     	Fname         LName     Date 
   35059		er		j       07-01-2016 06:10:000


Could you please help me, As I am new to SQL , please help,

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on Jun 29, 2016 10:03 AM
Report

Please refer below code.

SQL

Create Table #Users1
(
UniQueNumber Varchar(50),
FName varchar (50),
LName Varchar (50),
CreateDate Datetime
)
   
INSERT INTO #Users1 Values ('44354','Kumar','Shruthi-hh','06-21-2016 00:10:000')
INSERT INTO #Users1 Values ('44254','Shankar','Raj','06-22-2016 01:18:000')
INSERT INTO #Users1 Values ('34154','Babu','Swamy-k','06-23-2016 06:10:000')
INSERT INTO #Users1 Values ('35054','Mani','Kanta','06-24-2016 06:10:000')
INSERT INTO #Users1 Values ('35055','dsfdf','ett','06-25-2016 06:10:000')
INSERT INTO #Users1 Values ('35056','sss','sfd','06-26-2016 06:10:000')
INSERT INTO #Users1 Values ('35057','jkhk','sdd','06-27-2016 06:10:000')
INSERT INTO #Users1 Values ('35057','jkhk','sdd','06-28-2016 06:10:000')
INSERT INTO #Users1 Values ('35058','eer','s','06-30-2016 06:10:000')
INSERT INTO #Users1 Values ('35059','er','j','07-01-2016 06:10:000')

SELECT * FROM #Users1 
WHERE CONVERT(VARCHAR,CreateDate,103) 
BETWEEN (CASE WHEN DATENAME(weekday,GETDATE()) = 'Monday' THEN CONVERT(VARCHAR,DATEADD(DAY,-3,GETDATE()),103) ELSE CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103) END) 
AND  CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103)

DROP TABLE #Users1

I hope this will help you out.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Help need to get the previous dates dynamically in SQL

Company

Explore

Follow us

Contact Us