Gaurav Maggoo Ответов: 1

Как можно привести несвязанную часть запроса в подзапрос жирной части в SQL

How can bring unbold part of query in to subquery of bold part in sql

когда я пытаюсь это сделать я получаю ошибку как
Msg 116, Level 16, State 1, Line 23
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Что я уже пробовал:

Declare @DateFrom DateTime
Set @DateFrom='2018-08-01'
Declare @DateTo DateTime
Set @DateTo='2018-08-31'
   
  Select( 

    SELECT [Date],
       t.[Quantity],
       sum(t.[Quantity]) OVER(ORDER BY [Posting Date]) AS RunningTotal
FROM
  (SELECT( Select distinct [Date] from Calender where [Date]=ord.[Posting Date] )[Date], sum(ord.[Quantity]) [Quantity]
   FROM [Snowman Logistics Limited$Item Ledger Entry] ord
   INNER JOIN [Snowman Logistics Limited$Item] prod ON ord.[Item No_]=prod.No_ 
   where prod.[No_]='H1023038'
   and [Posting Date] <  @DateTo
   GROUP BY prod.No_,[Posting Date]  ) t 
   )
   from [Snowman Logistics Limited$Item Ledger Entry] where [Posting Date]between @DateFrom and @DateTo
   order By [Posting Date]

Santosh kumar Pithani

Привет,вы не дали вторую производную ссылку на таблицу ')' вместо ') как ff'

1 Ответов

Рейтинг:
1

Santosh kumar Pithani 

--I hope your trying  below query format..

;WITH CTE AS(
  SELECT
  (Select TOP(1) [Date] from Calender where [Date]=ord.[Posting Date] )AS  [Date],
   [Posting Date], 
  sum(ord.[Quantity])OVER(PARTITION BY prod.No_,[Posting Date] ORDER BY [Posting Date]) AS [Quantity]
   
   FROM [Snowman Logistics Limited$Item Ledger Entry] ord
   INNER JOIN [Snowman Logistics Limited$Item] prod 
     ON ord.[Item No_]=prod.No_ where prod.[No_]='H1023038'and [Posting Date] <  @DateTo )

SELECT * FROM CTE
 where [Posting Date]  between @DateFrom and @DateTo
     order By [Posting Date]