SQL Script Error
-
2012年7月4日 0:18
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日 1:05版主
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:26
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:33
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:39版主
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- 已编辑 Naomi NMicrosoft Community Contributor, Moderator 2012年7月4日 3:38
-
2012年7月4日 2:02
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:03
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:09Show the complete error please
Many Thanks & Best Regards, Hua Min
-
2012年7月4日 2:14
;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:18
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:27Still the same Error
-
2012年7月4日 2:34Show the complete error here
Many Thanks & Best Regards, Hua Min
-
2012年7月4日 2:40
;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:46I need the error
Many Thanks & Best Regards, Hua Min
-
2012年7月4日 2:49
;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日 3:05
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:18
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:38版主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:41版主
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日 5:58Hi Naomi, i try it.. the SQL script still had Error. please advise.
-
2012年7月4日 6:08Show your recent query and error
Many Thanks & Best Regards, Hua Min
-
2012年7月4日 6:13
;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:18
Where is the error?
Many Thanks & Best Regards, Hua Min
-
2012年7月4日 6:22
;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:28Since 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日 13:19版主
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月5日 1:53版主
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日 3:30
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) -
2012年7月5日 3:38
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
- 已编辑 HuaMin ChenMicrosoft Community Contributor 2012年7月5日 3:50
-
2012年7月5日 4:12版主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日 6:30Hi Hua Min, the result still the same is NULL
-
2012年7月5日 6:39
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 ?
- 已编辑 Caulson 2012年7月5日 6:41
-
2012年7月5日 7:09I can't see the relevant INV_ID in the 2nd table above.
Many Thanks & Best Regards, Hua Min
-
2012年7月5日 7:14
-
2012年7月5日 7:21
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:26Hi, yes, i try it, but the result is NULL. i want show all the result. thank you
-
2012年7月5日 8:09
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:12Also 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日 13:32版主
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月6日 0:22
HI Naomi, As below is the output image.
-
2012年7月6日 0:37版主
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日 5:40
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
-
2012年7月6日 5:42Hi, HUa MIn, i get the result output.. thank for help.
-
2012年7月6日 5:43
Hi, HUa MIn, i get the result output.. thank for help.
Please close thisMany Thanks & Best Regards, Hua Min
-
2012年7月6日 5:46
Mean? How to close ?
-
2012年7月6日 5:48Mark any helpful/answered replies.
Many Thanks & Best Regards, Hua Min
-
2012年7月6日 12:58版主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
.png)
.png){kind=spacer}
