SQL query yesterday date

Question

User1238961430 posted

Dear community. 

I have the following query

;WITH X as 
(      SELECT EwonId, Name, CAST(TagDate as Date) Datum, MAX(TagDate) TidPunkt 
       FROM [Talk2M].[FlatTag] with (nolock) 
       WHERE Name In ('RunConduct') and TagDate >= '2020-11-17' and TagDate <= '2020-11-30'
       GROUP BY EwonId, Name, Cast(TagDate as Date)
) 
SELECT DISTINCT FE.EwonName, X.TidPunkt, X.Name, FT.Value
From X 
JOIN [Talk2M].[FlatTag] FT With (Nolock) ON FT.Name = X.Name and FT.TagDate = X.TidPunkt and FT.EwonId = X.EwonId
JOIN [Talk2M].[FlatEwon] FE ON X.[EwonId] = FE.EwonId 
WHERE X.Name In ('RunConduct') 
OPTION (HASH GROUP)


;WITH X as 
(      SELECT EwonId, Name, CAST(TagDate as Date) Datum, MAX(TagDate) TidPunkt 
       FROM [Talk2M].[FlatTag] with (nolock) 
       WHERE Name In ('TotalVolume') and TagDate >= '2020-11-17' and TagDate <= '2020-11-30'
       GROUP BY EwonId, Name, Cast(TagDate as Date)
) 
SELECT DISTINCT FE.EwonName, X.TidPunkt, X.Name, FT.Value
From X 
JOIN [Talk2M].[FlatTag] FT With (Nolock) ON FT.Name = X.Name and FT.TagDate = X.TidPunkt and FT.EwonId = X.EwonId
JOIN [Talk2M].[FlatEwon] FE ON X.[EwonId] = FE.EwonId 
WHERE X.Name In ('TotalVolume') 
OPTION (HASH GROUP)

I want when this query runs get yesterday data. 

How do i change 

and TagDate >= '2020-11-17' and TagDate <= '2020-11-30'

To return only yesterday data?

Thank you

Answer 1

User452040443 posted

Hi,

If the column contains only date try:

and TagDate = cast(dateadd(day, -1, current_timestamp) as date)

If the column contains date and time try:

and TagDate >= cast(dateadd(day, -1, current_timestamp) as date) 
and TagDAte < cast(current_timestamp as date) 

Hope this help

Answer 2

User1238961430 posted

Hello And thank you. 

I am sorry to say that the query just times out when i use your code 

;WITH X as 
(      SELECT EwonId, Name, CAST(TagDate as Date) Datum, MAX(TagDate) TidPunkt 
       FROM [Talk2M].[FlatTag] with (nolock) 
       WHERE Name In ('TankConductivity') and TagDate >= cast(dateadd(day, -1, current_timestamp) as date) 
and TagDAte < cast(current_timestamp as date) 
       GROUP BY EwonId, Name, Cast(TagDate as Date)
) 
SELECT DISTINCT FE.EwonName, X.TidPunkt, X.Name, FT.Value
From X 
JOIN [Talk2M].[FlatTag] FT With (Nolock) ON FT.Name = X.Name and FT.TagDate = X.TidPunkt and FT.EwonId = X.EwonId
JOIN [Talk2M].[FlatEwon] FE ON X.[EwonId] = FE.EwonId 
WHERE X.Name In ('TankConductivity') 
OPTION (HASH GROUP)

;WITH X as 
(      SELECT EwonId, Name, CAST(TagDate as Date) Datum, MAX(TagDate) TidPunkt 
       FROM [Talk2M].[FlatTag] with (nolock) 
       WHERE Name In ('TotalPermeateVolume') and TagDate >= cast(dateadd(day, -1, current_timestamp) as date) 
and TagDAte < cast(current_timestamp as date) 
       GROUP BY EwonId, Name, Cast(TagDate as Date)
) 
SELECT DISTINCT FE.EwonName, X.TidPunkt, X.Name, FT.Value
From X 
JOIN [Talk2M].[FlatTag] FT With (Nolock) ON FT.Name = X.Name and FT.TagDate = X.TidPunkt and FT.EwonId = X.EwonId
JOIN [Talk2M].[FlatEwon] FE ON X.[EwonId] = FE.EwonId 
WHERE X.Name In ('TotalPermeateVolume') 
OPTION (HASH GROUP)

Any other ideas? 

Answer 3

User1535942433 posted

Hi Emil2,

I am sorry to say that the query just times out when i use your code 

Accroding to your description,I have created a test and I have successful.Could you post your data structure to us.

Since you don't post more details to us,my query just like this:

create table FlatTag(
 EwonId int,
 Name   varchar(255),
 TagDate  date,
 Value varchar(255)
);
create table FlatEwon(
  EwonId int,
  EwonName varchar(255)
);

Insert into FlatTag (EwonId,Name,TagDate,Value) values (1,'A','2020/11/20','A');
Insert into FlatTag (EwonId,Name,TagDate,Value) values (2,'B','2020/11/21','B');
Insert into FlatTag (EwonId,Name,TagDate,Value) values (3,'C','2020/11/22','C');
Insert into FlatTag (EwonId,Name,TagDate,Value) values (4,'D','2020/11/23','D');

Insert into FlatEwon(EwonId,EwonName) values(1,'A');
Insert into FlatEwon(EwonId,EwonName) values(2,'B');
Insert into FlatEwon(EwonId,EwonName) values(3,'C');
Insert into FlatEwon(EwonId,EwonName) values(4,'D');


;WITH X as 
(      SELECT EwonId, Name, CAST(TagDate as Date) Datum, MAX(TagDate) TidPunkt 
       FROM FlatTag with (nolock) 
       WHERE Name In ('A','B','C','D') and TagDate >= cast(dateadd(day, -1, current_timestamp) as date) and TagDAte < cast(current_timestamp as date) 
       GROUP BY EwonId, Name, Cast(TagDate as Date)
) 
SELECT DISTINCT FE.EwonName, X.TidPunkt, X.Name, FT.Value
From X 
JOIN FlatTag FT With (Nolock) ON FT.Name = X.Name and FT.TagDate = X.TidPunkt and FT.EwonId = X.EwonId
JOIN FlatEwon FE ON X.EwonId = FE.EwonId 
WHERE X.Name In ('A','B','C','D') 
OPTION (HASH GROUP)

Result:

Best regards,

Yijing Sun

Answer 4

User1238961430 posted

Hello. 

I have not been able to confirm this yet. i do not have access to the database. 

However i have consulted a SQL firm that will create a view that give me the data i want. So question can be closed. 

thank you so much for your help.

Answer 5

User1535942433 posted

Hi Emil2,

If you have solved your problems,you could mark these answers which helpful to you .Also,you could post new thread of more question to us.

Best regards,

Yijing Sun

Answer 6

User1238961430 posted

Here is how it was solved for others information 

WITH X AS (SELECT        EwonId, Name, CAST(TagDate AS DATE) AS Datum, MAX(TagDate) AS TidPunkt
                          FROM            Talk2M.FlatTag WITH (NOLOCK)
                          AND (TagDate >= CAST(DATEADD(dd, - 1, CAST(GETDATE() AS DATE)) AS VARCHAR(50))) AND (TagDate < CAST(CAST(GETDATE() AS DATE) AS VARCHAR(50)))