Solved

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

Posted on 2014-02-15
6
205 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Make all values in arrayist to titlecase+vb.net 23 27
Memory Usage 2 49
DataGridView Events ? 3 37
Angular JS Route 3 44
IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

948 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

19 Experts available now in Live!

Get 1:1 Help Now