Solved

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

Posted on 2014-02-15
6
203 Views
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
0
Comment
Question by:SweetingA
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

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

Author Comment

by:SweetingA
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......

Table......

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
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39862124
try this query.
;WITH CTE AS (
  SELECT *,ROW_NUMBER() OVER (ORDER BY Machine) rn
    FROM Test T1)
SELECT T1.Machine,T1.CountCum,
       T1.CountCum-ISNULL(T2.CountCum,0) [Count] 
  FROM CTE T1
  LEFT JOIN CTE T2 ON T1.rn = T2.rn + 1

Open in new window

http://sqlfiddle.com/#!3/cb851/7
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:SweetingA
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]
FROM CTE T1
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
0
 

Author Comment

by:SweetingA
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
ORDER BY ID

------------------------
0
 
LVL 11

Accepted Solution

by:
Technodweeb 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)
    Else
        cell.Offset(0, 1).Value = cell.Value
    End If
Next

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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now