February 28, 2014 at 12:08 pm
I have four queries that i need returned onto a single row. I have tried to come up with an inline view on my own but keep running into errors or very long execution times. I need the four values returned onto a single row to produce an inventory turns report. The formula for inventory returns is Total number of units received/ending balance*12.
This is sort of what i want the result to be....
YearMonthShippedReceivedStock Adjustment Ending BalanceTurn Factor
2014January44260506720 86604 7.83
Queries used.
Declare @startdate datetime = cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)as Date)
Declare @enddate datetime = cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)as Date)
Select sum(act_quantity) as QtyReceived, podet.wh_num
FROM [irms_2_5_2].[dbo].[podet]
WHERE delivery between @startdate and @enddate
and wh_num = 'HM10'
Group by podet.wh_num
Select sum(qty) as QtyShipped, pick.wh_num
FROM pick
WHERE convert(datetime, left(date_time,8),102) between @startdate and @enddate
and wh_num = 'HM10'
Group by pick.wh_num
Select sum(item_qty-sugg_qty) as Adjustments, auditlog.wh_num
FROM auditlog
WHERE wh_num = 'hm10'
and trans_type = 'as'
and convert(datetime, left(date_time,8),102) between @startdate and @enddate
Group by auditlog.wh_num
Select sum(total_qty)as endBalance, inventory.wh_num
FROM inventory
WHERE wh_num = 'HM10'
Group by inventory.wh_num
February 28, 2014 at 12:22 pm
Here is quick and dirty:
Declare @startdate datetime = cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)as Date)
Declare @enddate datetime = cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)as Date)
SELECT wh_num, QtyReceived, QtyShipped, Adjustments, endBalance
FROM (
Select sum(act_quantity) as QtyReceived, podet.wh_num
FROM [irms_2_5_2].[dbo].[podet]
WHERE delivery between @startdate and @enddate
and wh_num = 'HM10'
Group by podet.wh_num
) AS A
INNER JOIN (
Select sum(qty) as QtyShipped, pick.wh_num
FROM pick
WHERE convert(datetime, left(date_time,8),102) between @startdate and @enddate
and wh_num = 'HM10'
Group by pick.wh_num
) AS B ON A.wh_Num = B.wh_Num
INNER JOIN (
Select sum(item_qty-sugg_qty) as Adjustments, auditlog.wh_num
FROM auditlog
WHERE wh_num = 'hm10'
and trans_type = 'as'
and convert(datetime, left(date_time,8),102) between @startdate and @enddate
Group by auditlog.wh_num
) AS C ON A.wh_Num = C.wh_Num
INNER JOIN (
Select sum(total_qty) as endBalance, inventory.wh_num
FROM inventory
WHERE wh_num = 'HM10'
Group by inventory.wh_num
) AS D ON A.wh_Num = C.wh_Num
February 28, 2014 at 12:28 pm
Thanks! That worked.
February 28, 2014 at 12:29 pm
Glad I could help.
Do you understand what I did?
February 28, 2014 at 1:15 pm
sort of...still breaking it down... I was on that track originally with the following query, but it would run forever so I always cancelled it.
Declare @startdate datetime = cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)as Date)
Declare @enddate datetime = cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)as Date)
Declare @wh_num nvarchar(8) = 'HM10'
SELECT SUM(podet.act_quantity) AS QtyReceived,
(SELECT SUM(qty) AS Expr1
FROM pick
WHERE (CONVERT(datetime, LEFT(date_time, 8), 102) BETWEEN @startdate AND @enddate)
AND (wh_num = @wh_num)) AS QtyShipped,
(SELECT SUM(item_qty - sugg_qty) AS Expr1
FROM auditlog
WHERE (wh_num = @wh_num) AND (trans_type = 'as') AND (CONVERT(datetime, LEFT(date_time, 8), 102) BETWEEN @startdate AND @enddate))
AS Adjustments,
(SELECT SUM(total_qty) AS Expr1
FROM inventory
WHERE (wh_num = @wh_num) AND (date_time = @enddate)) AS endBalance
FROM auditlog AS auditlog_1 INNER JOIN
pick AS pick ON auditlog_1.co_num = pick.co_num AND auditlog_1.wh_num = pick.wh_num AND auditlog_1.abs_num = pick.abs_num INNER JOIN
podet ON auditlog_1.co_num = podet.co_num AND auditlog_1.wh_num = podet.wh_num AND pick.abs_num = podet.abs_num AND
pick.co_num = podet.co_num AND pick.wh_num = podet.wh_num AND auditlog_1.abs_num = podet.abs_num INNER JOIN
inventory AS inventory_1 ON auditlog_1.co_num = inventory_1.co_num AND auditlog_1.wh_num = inventory_1.wh_num AND
podet.abs_num = inventory_1.abs_num AND pick.abs_num = inventory_1.abs_num AND podet.co_num = inventory_1.co_num AND
podet.wh_num = inventory_1.wh_num AND auditlog_1.abs_num = inventory_1.abs_num
Where podet.wh_num = @wh_num
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply