Answered by:
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
Friday, November 20, 2020 4:00 PM
Answers
-
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)))
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, December 3, 2020 7:13 AM
All replies
-
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
Friday, November 20, 2020 7:42 PM -
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?
Saturday, November 21, 2020 7:43 AM -
User1535942433 posted
Hi Emil2,
I am sorry to say that the query just times out when i use your codeAccroding 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
Monday, November 23, 2020 9:32 AM -
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.
Friday, November 27, 2020 8:42 AM -
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
Monday, November 30, 2020 9:30 AM -
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)))
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, December 3, 2020 7:13 AM