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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.


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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Groupbox Control ? 2 27
PowerShell: ForEach ($y in $x.licenses | where {....} 3 55
TFS 2015 Access denied. 1 30
Get sourcecode path 14 47
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

749 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