SQL Query  - Change column field based on data in another field

Posted on 2014-02-15
Last Modified: 2014-02-16
I have a table as shown below

Machine  CountCum
A                    1
B                   12
C                    46I want to make a query which outputs
D                    51

Machine  CountCum  Count
A                    1                 1
B                   12               11
C                    46              34
D                    51              5

All help very welcome
Question by:SweetingA
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
  • 2
LVL 11

Expert Comment

by:Gregory Miller
ID: 39861954
I do not think you have provided enough info to even get started on this. Can you elaborate some please...

Author Comment

ID: 39861983
I am not sure how much more i can say....

I am using SQL 2008 express

I have a table called machine data in which there are two columns

Column 1 = Machine
Coulmn 2 = The result of a cululative product counter

I want to make a view (query) which adds a 2rd column, which is the actual product count per row

Column 1 = Machine
Column 2 = Cumulative product count
Column 3 = Actual product count (cum count of current row - cum count of previous row)

But i have no idea in sql server how to do it......


Machine  CountCum
A                    1
B                   12
C                    46
D                    51

Query Result......

Machine  CountCum  Count
A                    1                 1
B                   12               11
C                    46              34
D                    51              5

Thanks for the asistance
LVL 41

Expert Comment

ID: 39862124
try this query.
    FROM Test T1)
SELECT T1.Machine,T1.CountCum,
       T1.CountCum-ISNULL(T2.CountCum,0) [Count] 
  LEFT JOIN CTE T2 ON T1.rn = T2.rn + 1

Open in new window!3/cb851/7
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?


Author Comment

ID: 39862410
I can not run the above script, i get all sorts of errors

the query can not represented graphically in the diagram and criteria pane....

I changed it a little t match the actual names...see belwo

WITH CTE AS (SELECT *,ROW_NUMBER() OVER (ORDER BY Machine) rn FROM Test cbo.tbl_MachineData)
SELECT cbo.tbl_MachineData.Machine, cbo.tbl_MachineData.ProductCount, cbo.tbl_MachineData.ProductCount-ISNULL(T2.ProductCount,0) [Count]
LEFT JOIN CTE T2 ON cbo.tbl_MachineData.rn = T2.rn + 1

I am a novice i'm afraid so cany hints you can provide will be most welcome

Author Comment

ID: 39862589
This works....but its 1 line out of sync (see code below)

Cycle count is the cumulative value
Cycle step should be the diffence between the rows values

Current Result

Machine  CountCum  Count
A                    1                 11
B                   12               34
C                    46              5
D                    51              -51

Need Result

Machine  CountCum  Count
A                    1                 1
B                   12               11
C                    46              34
D                    51              5

Very close but still need some assistance please


SELECT     TOP (100) PERCENT ID, Year, Month, Week, Day, Hour, Time, Date, ShiftType, ShiftClassification, ShiftID, Machine, ProductCode, Batch, CavitationStd, CavitationAct,
                      CycleTimeStd, CycleTimeAct, CycleCount, ScrapCount, StopCount, RunStatus, Uptime, Downtime, COALESCE
                          ((SELECT     TOP (1) PERCENT CycleCount
                              FROM         dbo.qry_MachineData_All AS mi
                              WHERE     (ID > m.ID)
                              ORDER BY ID), 0) - CycleCount AS CycleStep
FROM         dbo.qry_MachineData_All AS m

LVL 11

Accepted Solution

Gregory Miller earned 500 total points
ID: 39862732
You need to drop the count column from your SQL query. You are not going to be able to make that work without a huge amount of complexity, if at all. The biggest killer is that you are sorting your output, which happen last, after all the joins and querys are finished, so something is always going to be out of place, unless you get lucky and the data just happens to be in the proper order before the sort.

Drop the count column from the query and perform the calculations to find the difference in a separate line of script. The count for the first row is always equal to the CountCum value which is simple enough, then each value under that can be handled by a simple formula.

Dim cell As Range
For Each cell In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    If cell.Row > 1 Then
        cell.Offset(0, 1).Value = cell.Value - cell.Offset(-1, 0)
        cell.Offset(0, 1).Value = cell.Value
    End If

I have put your cells in Columns A-C for my example. You may need to adjust the script to match where your columns reside. I am also assuming that the Count column can take the entire column top to bottom.

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 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