Solved

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

Posted on 2014-02-15
6
206 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

810 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