none
SQL Script Error

    问题

  • Hi, As below is my SQL Script had Error. Kindly Advise. Thank you

    ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select dateadd(day, 1, [The_Date])
    from Calendar

    where [The_Date] < '20121009')

    ,SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB

    from cteDates Cal LEFT JOIN OTH_INV_DETAILS a join oth_INV_QTY_LOC b ON Cal.The_date = CAST(A.Inv_Create_Date as DATE)

    LEFT join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_
     group by Cal.The_DATE,b.INV_QTY,a.INV_USAGE,

     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)

     End OPTION (maxrecursion 0)

      
    2012年7月4日 0:18

答案

  • Hi Naomi, i get the result i want. thank you for u advise.

    As below is my Complete Script

      select ROW_NUMBER() OVER(ORDER BY det.inv_id DESC) as RowNumber, sum.cal_date as Date,det.inv_id as INV_ID,sum.INV_LOCATION as Location,sum.INV_QTY as ONHandbalance,sum.INV_USAGE as Demand,sum.INV_QTY - (sum.INV_USAGE * sum.RowNumber)as DSB
      
      from OTH_INV_DETAILS det
      
      outer apply
      (
      
      select ROW_NUMBER() OVER(ORDER BY det.INV_ID DESC) as RowNumber, date1.cal_date,a.inv_id ,a.INV_USAGE, b.inv_qty , b.INV_LOCATION
      from OTH_INV_DETAILS a 
      join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID 
    
    outer apply
    (
      select CAL_DATE from OTH_INV_CAL
      )date1
      
     
     
     
        where 1=1
        and date1.CAL_DATE >= '2012-07-01' and date1.CAL_DATE <= '2012-07-06'
        and a.INV_ID = 'C5S-CLR-002'
        and b.INV_LOCATION = 'Store'
        
    )sum
    
    outer apply
    (
    select top 1 a.inv_id, a.inv_location ,  b.INV_USAGE, (sum.INV_QTY - b.INV_USAGE)  as a
    from OTH_INV_QTY_LOC a
    join OTH_INV_DETAILS b
    on a.INV_ID = b.INV_ID
    
    outer apply
    (
      select CAL_DATE from OTH_INV_CAL
    )date1
      
    where 1=1
    and a.INV_ID = sum.INV_ID
    and a.INV_LOCATION = sum.INV_LOCATION
    and date1.CAL_DATE < sum.CAL_DATE
    ) countdata
    
    where 1=1
    and det.inv_id = sum.INV_ID
    
    
    


    • 已编辑 Caulson 2012年7月6日 5:41
    • 已标记为答案 Caulson 2012年7月6日 5:53
    2012年7月6日 5:40

全部回复

  • Where did you get that script - I posted you the correct script and you somehow changed a few lines as well as lost a few.

    Can you show your original script without dates? 

    Also, why do you start more and more new threads and not close your other threads?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月4日 1:05
    版主
  • Hi Naomi, yes. the script is your posted but i try it got error. Sorry for any inconvenient here, because is really urgent to done my project. I'm really  stack it on this part to count with dates. Hope can get your advise. thank you

    As below is my original  SQL script.

    Select A.INV_CREATE_DATE AS Date, b.INV_QTY AS OnHand, SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    Case When A.INV_USAGE = 0
    Then 0
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int)

    End As DSB

    from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID


    group by a.INV_CREATE_DATE,b.INV_QTY,a.INV_USAGE,Case When A.INV_USAGE = 0
    Then 0

    Else CAST((b.INV_QTY) / a.INV_USAGE AS int)

    End

    2012年7月4日 1:26
  • Try

    ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select dateadd(day, 1, [The_Date])
    from Calendar

    where CONVERT(varchar,[The_Date],112) < '20121009')

    ,SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB

    from cteDates Cal LEFT JOIN OTH_INV_DETAILS a join oth_INV_QTY_LOC b ON Cal.The_date = CAST(A.Inv_Create_Date as DATE)

    LEFT join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_
     group by Cal.The_DATE,b.INV_QTY,a.INV_USAGE,

     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)

     End OPTION (maxrecursion 0)


    Many Thanks & Best Regards, Hua Min

    2012年7月4日 1:33
  • Assuming that this script works and gives you the desired result:

    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date],

    b.INV_QTY AS OnHand, SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance, Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End), cteDates AS select cast('20120701' as date) as [The_Date] UNION ALL select dateadd(day, 1, [The_Date]) from cteDates

    where [The_Date] < '20121009') select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB from cteDates Cal LEFT JOIN cte ON Cal.The_Date = cte.[Date]

    OPTION (MAXRECURSION 0)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    2012年7月4日 1:39
    版主
  • HI Naomi, it had error

    on this two part

    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from cteDates Cal LEFT JOIN cte ON Cal.The_Date = cte.[Date]

    select cast('20120701' as date) as [The_Date] 
    UNION ALL select dateadd(day, 1, [The_Date])
    from cteDateswhere [The_Date] < '20121009')


    2012年7月4日 2:02
  • HI Hua MIn,

    it had an erroron this part, kindly advise

    ,SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB

    2012年7月4日 2:03
  • Show the complete error please

    Many Thanks & Best Regards, Hua Min

    2012年7月4日 2:09
  • ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select dateadd(day, 1, [The_Date])
    from Calendar
    
    where CONVERT(varchar,[The_Date],112) < '20121009')
    
    ,SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB
    
    from cteDates Cal LEFT JOIN OTH_INV_DETAILS a join oth_INV_QTY_LOC b
    
     ON Cal.The_date = CAST(A.Inv_Create_Date as DATE)
    
    LEFT join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_
    
     group by Cal.The_DATE,b.INV_QTY,a.INV_USAGE,
    
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
     End OPTION (maxrecursion 0)

    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near '.'.

    2012年7月4日 2:14
  • Try

    ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select dateadd(day, 1, [The_Date])
    from Calendar
    
    where CONVERT(varchar,[The_Date],112) < '20121009')
    
    ,SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB
    
    from cteDates Cal LEFT JOIN OTH_INV_DETAILS a
    
     ON Cal.The_date = CAST(A.Inv_Create_Date as DATE)
    
    LEFT join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_
    
     group by Cal.The_DATE,b.INV_QTY,a.INV_USAGE,
    
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
     End OPTION (maxrecursion 0)


    Many Thanks & Best Regards, Hua Min

    2012年7月4日 2:18
  • Still the same Error
    2012年7月4日 2:27
  • Show the complete error here

    Many Thanks & Best Regards, Hua Min

    2012年7月4日 2:34
  • ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select dateadd(day, 1, [The_Date])
    from Calendar
    
    where CONVERT(varchar,[The_Date],112) < '20121009')
    
    ,SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB
    
    from cteDates Cal LEFT JOIN OTH_INV_DETAILS a
    
     ON Cal.The_date = CAST(A.Inv_Create_Date as DATE)
    
    LEFT join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_
    
     group by Cal.The_DATE,b.INV_QTY,a.INV_USAGE,
    
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
     End OPTION (maxrecursion 0)

    2012年7月4日 2:40
  • I need the error

    Many Thanks & Best Regards, Hua Min

    2012年7月4日 2:46
  • ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select dateadd(day, 1, [The_Date])
    from Calendar
    
    where CONVERT(varchar,[The_Date],112) < '20121009')
    
    ,SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB
    
    from cteDates Cal LEFT JOIN OTH_INV_DETAILS a
    
     ON Cal.The_date = CAST(A.Inv_Create_Date as DATE)
    
    LEFT join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_
    
     group by Cal.The_DATE,b.INV_QTY,a.INV_USAGE,
    
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
     End OPTION (maxrecursion 0)

    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near '.'.

    2012年7月4日 2:49
  • Try

    ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select cast('20120702' as date)
    from Calendar

    where CONVERT(varchar,[The_Date],112) < '20121009'))

    select SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB

    from cteDates Cal LEFT JOIN OTH_INV_DETAILS a

     ON Cal.The_date = CAST(A.Inv_Create_Date as DATE)

    LEFT join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_

     group by Cal.The_DATE,b.INV_QTY,a.INV_USAGE,

     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)

     End OPTION (maxrecursion 0)


    Many Thanks & Best Regards, Hua Min

    2012年7月4日 3:05
  • i tried it. The result is NULL

    ;with cteDates as (select cast('20120701' as date) as [The_Date] UNION ALL select cast('20120702' as date)
    from Calendar

    where CONVERT(varchar,[The_Date],112) < '20121009')

    select SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End As DSB

    from cteDates left JOIN OTH_INV_DETAILS a

     ON The_date = CAST(A.Inv_Create_Date as DATE)

     left join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID

     group by The_DATE,b.INV_QTY,a.INV_USAGE,

     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int)

     End OPTION (maxrecursion 0)

    2012年7月4日 3:18
  • Please re-try, I corrected the missing space. You should have figured this out by yourself.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月4日 3:38
    版主
  • What dates do you see if you run your original script from Inventory? In any case, since you're using LEFT JOIN, there should be rows returned.

    Add Cal.The_Date to the SELECT list.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月4日 3:41
    版主
  • Hi Naomi, i try it.. the SQL script still had Error. please advise.
    2012年7月4日 5:58
  • Show your recent query and error

    Many Thanks & Best Regards, Hua Min

    2012年7月4日 6:08
  • ;with cte as (Select CAST(a.INV_CREATE_DATE AS Date) as [Date],
    
    b.INV_QTY AS OnHand, 
    
    SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) 
     
     End As DSB 
     
     from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID 
     
     group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,
     
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End), 
     
     cte AS 
     
     select cast('20120701' as date) as [The_Date]
     
      UNION ALL select dateadd(day, 1, [The_Date]) from Calendar cal
    
    where [The_Date] < '20121009') select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    
     from Calendar cal left JOIN cte ON Cal.The_Date = cte.[Date]
    
    OPTION (MAXRECURSION 0)
    

    select cast('20120701' as date) as [The_Date]
     
      UNION ALL select dateadd(day, 1, [The_Date]) from Calendar cal

    where [The_Date] < '20121009')

    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB

     from Calendar cal left JOIN cte ON Cal.The_Date = cte.[Date]

    2012年7月4日 6:13
  • Where is the error?


    Many Thanks & Best Regards, Hua Min

    2012年7月4日 6:18
  • ;with cte as (Select CAST(a.INV_CREATE_DATE AS Date) as [Date],
    
    b.INV_QTY AS OnHand, 
    
    SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) 
     
     End As DSB 
     
     from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID 
     
     group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,
     
     Case When A.INV_USAGE = 0 Then 0 Else CAST((b.INV_QTY) / a.INV_USAGE AS int) End), 
     
     cte AS 
     
     select cast('20120701' as date) as [The_Date]
     
      UNION ALL select dateadd(day, 1, [The_Date]) from Calendar cal
    
    where [The_Date] < '20121009') select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    
     from Calendar cal left JOIN cte ON Cal.The_Date = cte.[Date]
    
    OPTION (MAXRECURSION 0)
    HI, the script i underline is an error
    2012年7月4日 6:22
  • Since my previous script above is having no error, can you please check by yourself why there is no data?

    Many Thanks & Best Regards, Hua Min

    2012年7月4日 6:28
  • Why do you keep inventing something that I didn't write? Do you have Calendar table in your database or not? If you do, you don't need to create cteDates on the fly.

    Once again, the script I posted to you and it should work:

    -- First cte is the original query from Inventory
    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date], 
    b.INV_QTY AS OnHand, SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    Case When A.INV_USAGE = 0
    Then 0 
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int) 
    
    End As DSB
    
    from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID
    
    
    group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,Case When A.INV_USAGE = 0
    Then 0 
    
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
    End), 
    
    
    -- cteDates is a recursive CTE to produce date ranges
    -- It is better to use permanent Calendar table instead
    cteDates AS 
    select cast('20120701' as date) as [The_Date] -- anchor 
    UNION ALL select dateadd(day, 1, [The_Date])
    from cteDates -- recursive part referencing the same cteDates
    where [The_Date] < '20121009')
    
    
    -- Now we use two CTEs we defined and JOIN them based on the date
    
    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from cteDates Cal LEFT JOIN cte ON Cal.The_Date = cte.[Date]
    OPTION (MAXRECURSION 0)

    Please run this exact code and don't try to change it again into something non-readable and completely wrong.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月4日 13:19
    版主
  • If you have permanent Calendar table in the database, the script becomes:

    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date], 
    b.INV_QTY AS OnHand, SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    Case When A.INV_USAGE = 0
    Then 0 
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int) 
    
    End As DSB
    
    from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID
    
    
    group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,Case When A.INV_USAGE = 0
    Then 0 
    
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
    End)
    
    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from Calendar Cal LEFT JOIN cte ON Cal.The_Date = cte.[Date]
    WHERE Cal.[The_Date] between '20120701' and '20121009'


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月5日 1:53
    版主
  • Hi Naomi, thank you for your advise again,  i find out the previous script Error u post. it is missing sign there. :). Now i have a next problem is the data  as below image show NULL. is it possible to show all the result without null. Can i use FUll JOIN ?

    thank you.

    -- First cte is the original query from Inventory
    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date], 
    b.INV_QTY AS OnHand, SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    Case When A.INV_USAGE = 0
    Then 0 
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int) 
    
    End As DSB
    
    from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID
    
    
    group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,Case When A.INV_USAGE = 0
    Then 0 
    
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
    End), 
    
    
    -- cteDates is a recursive CTE to produce date ranges
    -- It is better to use permanent Calendar table instead
    cteDates AS 
    (select cast('20120701' as date) as [The_Date] -- anchor 
    UNION ALL select dateadd(day, 1, [The_Date])
    from cteDates -- recursive part referencing the same cteDates
    where [The_Date] < '20121009')
    
    
    -- Now we use two CTEs we defined and JOIN them based on the date
    
    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from cteDates Cal left JOIN cte ON Cal.The_Date = cte.[Date]
    OPTION (MAXRECURSION 0)


    • 已编辑 Caulson 2012年7月5日 3:35
    2012年7月5日 3:30
  • Try

    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date],
    isnull(b.INV_QTY,0) AS OnHand, SUM(isnull(a.INV_USAGE,0)) AS Demand,(isnull(b.INV_QTY,0)-isnull(a.INV_USAGE,0)) AS Balance,
    Case When isnull(a.INV_USAGE,0) = 0
    Then 0
    Else CAST((isnull(b.INV_QTY,0)) / isnull(a.INV_USAGE,0) AS int)

    End As DSB

    from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID


    group by CAST(a.INV_CREATE_DATE as Date),isnull(b.INV_QTY,0),isnull(a.INV_USAGE,0),Case When isnull(a.INV_USAGE,0) = 0
    Then 0

    Else CAST((isnull(b.INV_QTY,0)) / isnull(a.INV_USAGE,0) AS int)

    End),


    -- cteDates is a recursive CTE to produce date ranges
    -- It is better to use permanent Calendar table instead
    cteDates AS
    (select cast('20120701' as date) as [The_Date] -- anchor
    UNION ALL select dateadd(day, 1, [The_Date])
    from cteDates -- recursive part referencing the same cteDates
    where [The_Date] < '20121009')


    -- Now we use two CTEs we defined and JOIN them based on the date

    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from cteDates Cal left JOIN cte ON Cal.The_Date = cte.[Date]
    OPTION (MAXRECURSION 0)


    Many Thanks & Best Regards, Hua Min


    2012年7月5日 3:38
  • It means you don't have records in your inventory for those dates. What do you want to show instead NULL - 0? Or may be you only want to show real data (in this case use INNER JOIN instead of the LEFT JOIN).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月5日 4:12
    版主
  • Hi Hua Min, the result still the same is NULL
    2012年7月5日 6:30
  • Hi Naomi, for the inventory for those date is in OTH_INV_DETAILS a table, for the record data QTY is in OTH_INV_QTY_LOC b, the result i want show is all data. any suggest ?

    OTH_INV_QTY_LOC TAble

    OTH_INV_DETAILS

    • 已编辑 Caulson 2012年7月5日 6:41
    2012年7月5日 6:39
  • I can't see the relevant INV_ID in the 2nd table above.

    Many Thanks & Best Regards, Hua Min

    2012年7月5日 7:09
  • 2012年7月5日 7:14
  • Try this

    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date],
    isnull(b.INV_QTY,0) AS OnHand, SUM(isnull(a.INV_USAGE,0)) AS Demand,(isnull(b.INV_QTY,0)-isnull(a.INV_USAGE,0)) AS Balance,
    Case When isnull(a.INV_USAGE,0) = 0
    Then 0
    Else CAST((isnull(b.INV_QTY,0)) / isnull(a.INV_USAGE,0) AS int)

    End As DSB

    from OTH_INV_DETAILS a left join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID


    group by CAST(a.INV_CREATE_DATE as Date),isnull(b.INV_QTY,0),isnull(a.INV_USAGE,0),Case When isnull(a.INV_USAGE,0) = 0
    Then 0

    Else CAST((isnull(b.INV_QTY,0)) / isnull(a.INV_USAGE,0) AS int)

    End),


    -- cteDates is a recursive CTE to produce date ranges
    -- It is better to use permanent Calendar table instead
    cteDates AS
    (select cast('20120701' as date) as [The_Date] -- anchor
    UNION ALL select dateadd(day, 1, [The_Date])
    from cteDates -- recursive part referencing the same cteDates
    where [The_Date] < '20121009')


    -- Now we use two CTEs we defined and JOIN them based on the date

    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from cteDates Cal left JOIN cte ON Cal.The_Date = cte.[Date]
    OPTION (MAXRECURSION 0)

    In above, I do use a left join and Naomi already told you this above.


    Many Thanks & Best Regards, Hua Min

    2012年7月5日 7:21
  • Hi, yes, i try it, but the result is NULL. i want show all the result. thank you
    2012年7月5日 7:26
  • Try

    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date],
    isnull(b.INV_QTY,0) AS OnHand, SUM(isnull(a.INV_USAGE,0)) AS Demand,(isnull(b.INV_QTY,0)-isnull(a.INV_USAGE,0)) AS Balance,
    Case When isnull(a.INV_USAGE,0) = 0
    Then 0
    Else CAST((isnull(b.INV_QTY,0)) / isnull(a.INV_USAGE,0) AS int)

    End As DSB

    from OTH_INV_DETAILS a left join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID


    group by CAST(a.INV_CREATE_DATE as Date),isnull(b.INV_QTY,0),isnull(a.INV_USAGE,0),Case When isnull(a.INV_USAGE,0) = 0
    Then 0

    Else CAST((isnull(b.INV_QTY,0)) / isnull(a.INV_USAGE,0) AS int)

    End),


    -- cteDates is a recursive CTE to produce date ranges
    -- It is better to use permanent Calendar table instead
    cteDates AS
    (select cast('20120701' as date) as [The_Date] -- anchor
    UNION ALL select dateadd(day, 1, [The_Date])
    from cteDates -- recursive part referencing the same cteDates
    where convert(varchar,[The_Date],112) < '20121009')


    -- Now we use two CTEs we defined and JOIN them based on the date

    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from cteDates Cal left JOIN cte ON Cal.The_Date = cte.[Date]
    OPTION (MAXRECURSION 0)


    Many Thanks & Best Regards, Hua Min

    2012年7月5日 8:09
  • Also show all your records with INV_CREATE_DATE falls from 2012-07-01 to 2012-10-09

    Many Thanks & Best Regards, Hua Min

    2012年7月5日 8:12
  • Please give the output of

    select MIN(Inv_Create_Date) as StartDate, MAX(Inv_Create_Date) as EndDate
    
    from OTH_INV_DETAILS

    Obviously, if you select one range of dates from the Calendar and have completely different dates in your table, you will not be able to match them. You may want to filter dates based on the more realistic date range. In other words,

    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date], 
    b.INV_QTY AS OnHand, SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    Case When A.INV_USAGE = 0
    Then 0 
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int) 
    
    End As DSB
    
    from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID
    
    
    group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,Case When A.INV_USAGE = 0
    Then 0 
    
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
    End)
    
    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from Calendar Cal LEFT JOIN cte ON Cal.The_Date = cte.[Date]
    WHERE Cal.[The_Date] 
    between '20120701' and '20121009'

    At the very bottom:

    between '20120701' and '20121009'

    Instead of July 1 2012 till October 1st 2012 you may want to use 20110701 and 20120701 to show information for the whole last year from 1st of July 2011 till 1st of July 2012.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月5日 13:32
    版主
  • HI Naomi, As below is the output image.

    2012年7月6日 0:22
  • In this case, try:

    ;with cte as (Select CAST(A.INV_CREATE_DATE AS Date) as [Date], 
    b.INV_QTY AS OnHand, SUM(a.INV_USAGE) AS Demand,(b.INV_QTY-a.INV_USAGE) AS Balance,
    Case When A.INV_USAGE = 0
    Then 0 
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int) 
    
    End As DSB
    
    from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID
    
    
    group by CAST(a.INV_CREATE_DATE as Date),b.INV_QTY,a.INV_USAGE,Case When A.INV_USAGE = 0
    Then 0 
    
    Else CAST((b.INV_QTY) / a.INV_USAGE AS int)
    
    End)
    
    select Cal.The_Date AS [Date], cte.OnHand, cte.Demand, cte.Balance, cte.DSB
    from Calendar Cal LEFT JOIN cte ON Cal.The_Date = cte.[Date]
    WHERE Cal.[The_Date] 
    between '20120228' and '20120701'

    This should produce data (except for June 30 and July 1)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 0:37
    版主
  • Hi Naomi, i get the result i want. thank you for u advise.

    As below is my Complete Script

      select ROW_NUMBER() OVER(ORDER BY det.inv_id DESC) as RowNumber, sum.cal_date as Date,det.inv_id as INV_ID,sum.INV_LOCATION as Location,sum.INV_QTY as ONHandbalance,sum.INV_USAGE as Demand,sum.INV_QTY - (sum.INV_USAGE * sum.RowNumber)as DSB
      
      from OTH_INV_DETAILS det
      
      outer apply
      (
      
      select ROW_NUMBER() OVER(ORDER BY det.INV_ID DESC) as RowNumber, date1.cal_date,a.inv_id ,a.INV_USAGE, b.inv_qty , b.INV_LOCATION
      from OTH_INV_DETAILS a 
      join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID 
    
    outer apply
    (
      select CAL_DATE from OTH_INV_CAL
      )date1
      
     
     
     
        where 1=1
        and date1.CAL_DATE >= '2012-07-01' and date1.CAL_DATE <= '2012-07-06'
        and a.INV_ID = 'C5S-CLR-002'
        and b.INV_LOCATION = 'Store'
        
    )sum
    
    outer apply
    (
    select top 1 a.inv_id, a.inv_location ,  b.INV_USAGE, (sum.INV_QTY - b.INV_USAGE)  as a
    from OTH_INV_QTY_LOC a
    join OTH_INV_DETAILS b
    on a.INV_ID = b.INV_ID
    
    outer apply
    (
      select CAL_DATE from OTH_INV_CAL
    )date1
      
    where 1=1
    and a.INV_ID = sum.INV_ID
    and a.INV_LOCATION = sum.INV_LOCATION
    and date1.CAL_DATE < sum.CAL_DATE
    ) countdata
    
    where 1=1
    and det.inv_id = sum.INV_ID
    
    
    


    • 已编辑 Caulson 2012年7月6日 5:41
    • 已标记为答案 Caulson 2012年7月6日 5:53
    2012年7月6日 5:40
  • Hi, HUa MIn, i get the result output.. thank for help.
    2012年7月6日 5:42
  • Hi, HUa MIn, i get the result output.. thank for help.

    Please close this

    Many Thanks & Best Regards, Hua Min

    2012年7月6日 5:43
  • Mean? How to close ?

    2012年7月6日 5:46
  • Mark any helpful/answered replies.

    Many Thanks & Best Regards, Hua Min

    2012年7月6日 5:48
  • This seems to be something completely different than what we discussed the whole thread. Why did you waste our time then?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 12:58
    版主