locked
SQL query yesterday date RRS feed

  • 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 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

    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