Argthtjtr

I am trying put a case statement, this case statement is based on condition from 2 different sub queries. My problem is, I am not able get it work.

This SQL sub queries is giving a count which I am using in case statement.

Error I am getting is:

Incorrrect syntax near x and y.

(which are my subqueries)

Select 'Days=' as Days,

case

when x.yest >=0 then '1 Day'

when y.DayBefore <=3 then '1 Day'

else '2 Day'

end as Days

from

(

Select count(*) as yest 

from

[Main], [DailyStatus]

where Approved_Date is null

Submitted_Date = cast(LAST_WEEKDAY_DATE as date) x,



(

Select count(*) as DayBefore

 from [Main], [DailyStatus]

where Approved_Date is null

and Submitted_Date = dateadd(dd, -1, cast(LAST_WEEKDAY_DATE as date))

)y

Below are my tables:

Main
Approved_Date
Submitted_Date

DailyStatus
Last_Weekday_date

I highly appreciate any help on this.
Thanks, Shikha

Indent your sql properly and it will make these things easier to spot

Select 

  'Days=' as Days,

  case

    when x.yest >=0 then '1 Day'

    when y.DayBefore <=3 then '1 Day'

    else '2 Day'

  end as Days

from

(

  Select 

    count(*) as yest 

  from

    [Main]

    CROSS JOIN

    [DailyStatus]

  where 

    Approved_Date is nulL AND 

    Submitted_Date = cast(LAST_WEEKDAY_DATE as date) 

) x

CROSS JOIN

(

  Select 

    count(*) as DayBefore

  from 

    [Main]

    CROSS JOIN 

    [DailyStatus]

  where 

    Approved_Date is null and 

    Submitted_Date = dateadd(dd, -1, cast(LAST_WEEKDAY_DATE as date))

)y

Also, doing your joins like that (from table1, table2) fell out of favour about 20 years ago. Please use modern join syntax

Syntax errors edited, as well as some formatting to make it a bit easier to read. PS - not a fan of your implicit joins here. You should be explicit by using <join type> JOIN .... ON...

SELECT

      'Days=' AS [Days]

    , CASE

         WHEN [x].[yest] >= 0 THEN '1 Day'

         WHEN [y].[DayBefore] <= 3 THEN '1 Day'

      ELSE '2 Day'

      END AS     [Days]

FROM

    (

        SELECT

             COUNT(*) AS [yest]

        FROM

            [Main]

          , [DailyStatus]

        WHERE  [Approved_Date] IS NULL

             AND [Submitted_Date] = CAST([LAST_WEEKDAY_DATE] AS DATE)

    ) AS [x]

   ,

    (

        SELECT

             COUNT(*) AS [DayBefore]

        FROM

            [Main]

          , [DailyStatus]

        WHERE  [Approved_Date] IS NULL

             AND [Submitted_Date] = DATEADD([dd], -1, CAST([LAST_WEEKDAY_DATE] AS DATE))

    ) AS [y]