locked
CTE Join with other table in sql RRS feed

  • Question

  • User1152553138 posted
    WITH mycte AS
     (
       SELECT CAST('2018-04-01' AS DATETIME) DateValue
       UNION ALL
       SELECT  DateValue + 1
       FROM    mycte   
       WHERE   DateValue + 1 < '2018-09-30'
     )
    
     SELECT  DATENAME(MONTH,(a.DateValue)) as [Months] ,count( a.DateValue) as BalWorkingDays FROM mycte a Left Join Holidays b on a.DateValue=b.OffDate 
     WHERE a.DateValue>GetDate() AND NOT (DATEPART(dw, a.DateValue)=1 or a.DateValue IN (Select Distinct OffDate from Holidays)) 
     Group By DATENAME(MONTH,(a.DateValue))
     OPTION (MAXRECURSION 0);

    I would to Join This with my Order Table as Left Join

    Select OrderNo,OrderDate, OrderQty from Orders.

    On OrderDate with a.DateValue like this i have some 5 tables based on date i would like to bind how to do so?

    Tuesday, April 10, 2018 11:51 AM

All replies

  • User753101303 posted

    Hi,

    My understanding is that you want to build a first CTE, then another one and then use that in a final SQL statement ? You can  perfectly define multiple expressions in a single WITH statement. See example C at https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

    Tuesday, April 10, 2018 2:11 PM
  • User1152553138 posted
    WITH mycte AS
     (
       SELECT CAST('2018-04-01' AS DATETIME) DateValue
       UNION ALL
       SELECT  DateValue + 1
       FROM    mycte   
       WHERE   DateValue + 1 < '2018-09-30'
     )
    
     SELECT  DATENAME(MONTH,(a.DateValue)) as [Months] ,count( a.DateValue) as BalWorkingDays FROM mycte a Left Join [ShoeMIS].[dbo].[HolidayTable] b on a.DateValue=b.OffDate 
     WHERE a.DateValue>GetDate() AND NOT (DATEPART(dw, a.DateValue)=1 or a.DateValue IN (Select Distinct OffDate from [ShoeMIS].[dbo].[HolidayTable])) 
     Group By DATENAME(MONTH,(a.DateValue))
     OPTION (MAXRECURSION 0);
     
     I would like to join the below table with the above CTE Let me know how to do so?
     
     Select OrderNo, OrderDate, OrderQty from OrderTable
     Union All
     Select OrderNo, DelDate,   DelQty   from DeliveryTable

    I would like to join the below table with the above CTE Let me know how to do so?

    Saturday, April 14, 2018 6:18 AM
  • User753101303 posted

    For now it looks like a logic issue. You want to join DATENAME(MONTH,(a.DateValue)) with OrderDate ??You can use a ququery by using it inside parenthesis and using an alias (but for now I believe the problem is not about the syntax).

    Saturday, April 14, 2018 11:43 AM
  • User1152553138 posted
    Thanks for the reply.
    How can I join Order table and delivery date with about CTE query using date
    Saturday, April 14, 2018 6:33 PM
  • User475983607 posted

     
    How can I join Order table and delivery date with about CTE query using date

    It is not possible unless the dates are unique which is unlikely.  If you try to JOIN on a date, you'll end up with a cross join behavior.

    Having read your other posts it seems you have logical design issues.  You'll need to rethink your design approach and most likely fix the DB schema.

    Saturday, April 14, 2018 7:22 PM