?
Solved

SQL - Running Value

Posted on 2014-11-08
7
Medium Priority
?
124 Views
Last Modified: 2014-11-09
I created a sql query to calculate a running value to calculate shortage.  However, I can't seem to get it to work correctly.

This is what I'm expecting.  The RunningValue column is what I'm after.
Expected Result
However, I'm getting the following instead with my current SQL Query.
 Current Output
Here's my sql script.  Maybe I'm not doing something right.
SELECT	J.START_DATE, J.JOB_ID, J.ITEM_ID, J.REQ_QTY, J.ON_HAND,

		(
		SELECT	MAX(J2.ON_HAND) - SUM(J2.REQ_QTY)
		FROM	JOB_ORDER J2
		WHERE	J2.ITEM_ID = J.ITEM_ID
				AND J2.START_DATE <= J.START_DATE
				AND J2.JOB_ID <= J.JOB_ID
		)
		
FROM	JOB_ORDER J
WHERE	J.ITEM_ID = 'GDM001'
		AND J.START_DATE BETWEEN '2014-11-7' AND '2014-11-14'

Open in new window

0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40430739
I have some observations, but not an outright solution.

Using Max() probably does not do what you want. That's because the quantity on hand (QOH) will fluctuate as orders are processed.

On a related note, the source data looks odd. It would be odd for orders to be unprocessed for a week, and for the QOH to remain the same for an entire week.

Also, your data is not sorted by the Job ID. That will make the Running Values misleading, even when they are being evaluated correctly.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40430774
|   START_DATE | JOB_ID | ITEM_ID | REQ_QTY | ON_HAND | PREV_QTY | COLUMN_6 |
|--------------|--------|---------|---------|---------|----------|----------|
| Nov, 07 2014 |  42006 |  GDM001 |       4 |      15 |        4 |       11 |
| Nov, 11 2014 |  42448 |  GDM001 |  13.375 |      15 |   17.375 |   -2.375 |
| Nov, 13 2014 |  42350 |  GDM001 |       4 |      15 |   29.375 |  -14.375 |
| Nov, 13 2014 |  42007 |  GDM001 |       4 |      15 |   29.375 |  -14.375 |
| Nov, 13 2014 |  42424 |  GDM001 |       4 |      15 |   29.375 |  -14.375 |
| Nov, 14 2014 |  42656 |  GDM001 |     1.5 |      15 |   30.875 |  -15.875 |

Open in new window

Produced by:
SELECT
      J.START_DATE
    , J.JOB_ID
    , J.ITEM_ID
    , J.REQ_QTY
    , J.ON_HAND
    , CA.PREV_QTY
    , J.ON_HAND - CA.PREV_QTY
FROM JOB_ORDER J
      CROSS APPLY (
            SELECT
                  SUM(J2.REQ_QTY)
            FROM JOB_ORDER J2
            WHERE J2.ITEM_ID = J.ITEM_ID
            AND J2.START_DATE <= J.START_DATE
      ) CA (PREV_QTY)
WHERE J.ITEM_ID = 'GDM001'
AND ( J.START_DATE >= '20141107' AND J.START_DATE < '20141115' )
;

Open in new window

Please provide sample data with questions of this nature. While that image of the data may be helpful in describing the problem it isn't of so much use in solving the problem. e.g.
CREATE TABLE JOB_ORDER 
	([Start_Date] datetime, [Job_ID] int, [Item_ID] varchar(6), [Req_Qty] decimal(12,3), [On_Hand] decimal(12,3))
;
	
INSERT INTO JOB_ORDER
	([Start_Date], [Job_ID], [Item_ID], [Req_Qty], [On_Hand])
VALUES
	('2014-11-07 00:00:00', 42006, 'GDM001', 4, 15),
	('2014-11-11 00:00:00', 42448, 'GDM001', 13.375, 15),
	('2014-11-13 00:00:00', 42350, 'GDM001', 4, 15),
	('2014-11-13 00:00:00', 42007, 'GDM001', 4, 15),
	('2014-11-13 00:00:00', 42424, 'GDM001', 4, 15),
	('2014-11-14 00:00:00', 42656, 'GDM001', 1.5, 15)
;

Open in new window

And with that data it is possible to test a proposed solution.

see it working at: http://sqlfiddle.com/#!3/f77db5/1

Please note I have altered the method used for the date range filter.
See: "Beware of Between"

and the safest date literal in SQL Server is YYYYMMDD
0
 

Author Comment

by:holemania
ID: 40430796
Thanks PortletPaul.

Anyway to get it so that the running value as followed:

|   START_DATE | JOB_ID | ITEM_ID | REQ_QTY | ON_HAND | PREV_QTY | COLUMN_6 |
|--------------|--------|---------|---------|---------|----------|----------|
| Nov, 07 2014 |  42006 |  GDM001 |       4 |      15 |        4 |       11 |
| Nov, 11 2014 |  42448 |  GDM001 |  13.375 |      15 |   17.375 |   -2.375 |
| Nov, 13 2014 |  42350 |  GDM001 |       4 |      15 |   29.375 |  -6.375 |
| Nov, 13 2014 |  42007 |  GDM001 |       4 |      15 |   29.375 |  -10.375 |
| Nov, 13 2014 |  42424 |  GDM001 |       4 |      15 |   29.375 |  -14.375 |
| Nov, 14 2014 |  42656 |  GDM001 |     1.5 |      15 |   30.875 |  -15.875 |

Open in new window


Seems to repeat -14.375 since the date is the same.  However, I also need it to sum correctly for each line.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:holemania
ID: 40430842
Looks like the following works, but only with SQL 2012.  Unfortunately, that's only my test environment and all my production SSRS reports as well as databases are on SQL 2005 and 2008.

DECLARE @SPOUTPUT TABLE(
		START_DATE DATE,
		JOB_ID VARCHAR(30),
		ITEM_ID VARCHAR(30),
		REQ_QTY DECIMAL(15, 4),
		ON_HAND DECIMAL(15, 4)
		)
		
INSERT INTO @SPOUTPUT
VALUES('2014-11-7', '42006', 'GDM001', '4', '15')
INSERT INTO @SPOUTPUT
VALUES('2014-11-11', '42448', 'GDM001', '13.375', '15')
INSERT INTO @SPOUTPUT
VALUES('2014-11-13', '41320', 'GDM001', '4', '15')
INSERT INTO @SPOUTPUT
VALUES('2014-11-13', '42007', 'GDM001', '4', '15')
INSERT INTO @SPOUTPUT
VALUES('2014-11-13', '42425', 'GDM001', '4', '15')
INSERT INTO @SPOUTPUT
VALUES('2014-11-14', '42656', 'GDM001', '1.5', '15')		
		
SELECT	S.START_DATE, S.JOB_ID, S.ITEM_ID, S.REQ_QTY, S.ON_HAND,
		S.ON_HAND - SUM(S.REQ_QTY) OVER (PARTITION BY S.ITEM_ID ORDER BY S.START_DATE, S.JOB_ID)
FROM	@SPOUTPUT S
ORDER BY S.START_DATE, S.JOB_ID

Open in new window


The above produce the result I need.
START_DATE JOB_ID                         ITEM_ID                        REQ_QTY                                 ON_HAND                                 RUNNING_VALUE
---------- ------------------------------ ------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------
2014-11-07 42006                          GDM001                         4.0000                                  15.0000                                 11.0000
2014-11-11 42448                          GDM001                         13.3750                                 15.0000                                 -2.3750
2014-11-13 41320                          GDM001                         4.0000                                  15.0000                                 -6.3750
2014-11-13 42007                          GDM001                         4.0000                                  15.0000                                 -10.3750
2014-11-13 42425                          GDM001                         4.0000                                  15.0000                                 -14.3750
2014-11-14 42656                          GDM001                         1.5000                                  15.0000                                 -15.8750

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40430870
"Sum correctly for each line"

Exactly how do you decide what is correct? That isn't stated.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40430878
Assuming JOB_ID is the tie breaker within a day:
| START_DATE | JOB_ID | ITEM_ID | REQ_QTY | ON_HAND | PREV_QTY | RUNNING_QTY |
|------------|--------|---------|---------|---------|----------|-------------|
| 2014-11-07 |  42006 |  GDM001 |       4 |      15 |        4 |          11 |
| 2014-11-11 |  42448 |  GDM001 |  13.375 |      15 |   17.375 |      -2.375 |
| 2014-11-13 |  42007 |  GDM001 |       4 |      15 |   21.375 |      -6.375 |
| 2014-11-13 |  42350 |  GDM001 |       4 |      15 |   25.375 |     -10.375 |
| 2014-11-13 |  42424 |  GDM001 |       4 |      15 |   29.375 |     -14.375 |
| 2014-11-14 |  42656 |  GDM001 |     1.5 |      15 |   30.875 |     -15.875 |

Open in new window


Produced by (and should run on SQL 2005):
WITH CTE
AS (
    SELECT
          START_DATE
        , JOB_ID
        , ITEM_ID
        , REQ_QTY
        , ON_HAND
        , ROW_NUMBER() OVER(PARTITION BY ITEM_ID
                            ORDER BY START_DATE, JOB_ID) AS RN
    FROM JOB_ORDER
   )
SELECT
      CONVERT(VARCHAR(10),J.START_DATE,121) START_DATE
    , J.JOB_ID
    , J.ITEM_ID
    , J.REQ_QTY
    , J.ON_HAND
    , ca.prev_qty
    , J.ON_HAND - ca.prev_qty AS RUNNING_QTY
FROM CTE J
CROSS APPLY (
            SELECT
                  ISNULL(SUM(J2.REQ_QTY),0)
            FROM CTE J2
            WHERE J2.ITEM_ID = J.ITEM_ID
            AND J2.START_DATE <= J.START_DATE
            AND J2.RN <= J.RN
             ) ca (prev_qty)
WHERE J.ITEM_ID = 'GDM001'
AND J.START_DATE >= '20141107' AND J.START_DATE < '20141115'
;

Open in new window

0
 

Author Closing Comment

by:holemania
ID: 40431257
Awesome.  Thank you.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question