Author |
Topic |
amanalyn
Starting Member
9 Posts |
Posted - 2012-12-03 : 13:34:04
|
I am trying to find a way to add the value (if less then 200) to the next record/row of data in a query. I and getting the value using count. The reason for this is we have late starts on Sunday's so it throws the numbers off when the percentage per week is done by day.My query so far for week to date is this:WITH stat_tab AS(SELECT DISTINCT CONVERT(VarChar(20), vwPouringUnion.Date, 101) AS Date, COUNT(vwPouringUnion.PT) AS Data, COUNT(DISTINCT vwPouringUnion.HN) AS HeatsFROM vwPouringUnionWHERE (vwPouringUnion.PT BETWEEN CONVERT(VarChar(20), DATEADD(hh, - 2, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(hh, - 22, GETDATE())), 0)), 101) AND CONVERT(VarChar(20), DATEADD(d,-1, GETDATE()) + '10:00:00 PM', 101))GROUP BY CONVERT(VarChar(20), vwPouringUnion.Date, 101)HAVING (COUNT(vwPouringUnion.PT) > 200)),mean_tab AS(SELECT AVG(Data) AS [Average] FROM stat_tab),dev_tab AS(SELECT STDEV(Data) AS [StDev] FROM stat_tab)SELECT st.Date,st.Data,mt.[Average],mt.[Average] + (2*dt.[StDev]) AS UWL2,mt.[Average] + (3*dt.[StDev]) AS UCL,mt.[Average] - (2*dt.[StDev]) AS LWL2,mt.[Average] - (3*dt.[StDev]) AS LCLFROM stat_tab stCROSS JOIN mean_tab mtCROSS JOIN dev_tab dtORDER BY Date |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-03 : 15:20:16
|
Sample data and expected output? |
|
|
amanalyn
Starting Member
9 Posts |
Posted - 2012-12-04 : 09:57:39
|
Sample Data now;Date Data11/18/2012 511/19/2012 104911/20/2012 101311/21/2012 78411/26/2012 109911/27/2012 115111/28/2012 120011/29/2012 104611/30/2012 108312/2/2012 1512/3/2012 1034Expected output would be;Date Data11/19/2012 105411/20/2012 101311/21/2012 78411/26/2012 109911/27/2012 115111/28/2012 120011/29/2012 104611/30/2012 108312/3/2012 1049 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-04 : 10:17:06
|
This seems a bit awkwardDECLARE @Table TABLE (Col1 DATE, Col2 int)INSERT INTO @TableVALUES('11/18/2012', 5 ),('11/19/2012', 1049),('11/20/2012', 1013),('11/21/2012', 784),('11/26/2012', 1099),('11/27/2012', 1151),('11/28/2012', 1200),('11/29/2012', 1046),('11/30/2012', 1083),('12/2/2012', 15),('12/3/2012', 1034)SELECT t.Col1, CASE WHEN t.Col2 > 200 THEN t3.Col2 ELSE t.Col2 + t3.Col2 ENDFROM @table tCROSS APPLY (select top 1 t2.Col2 from @table t2 where t.Col1 < t2.Col1 order by t.col2 asc ) t3 WHERE CASE WHEN t.Col2 > 200 THEN t3.Col2 ELSE t.Col2 + t3.Col2 END >200ORDER BY Col1 asc JimEveryday I learn something that somebody else already knew |
|
|
amanalyn
Starting Member
9 Posts |
Posted - 2012-12-04 : 11:03:25
|
quote: Originally posted by jimf This seems a bit awkwardDECLARE @Table TABLE (Col1 DATE, Col2 int)INSERT INTO @TableVALUES('11/18/2012', 5 ),('11/19/2012', 1049),('11/20/2012', 1013),('11/21/2012', 784),('11/26/2012', 1099),('11/27/2012', 1151),('11/28/2012', 1200),('11/29/2012', 1046),('11/30/2012', 1083),('12/2/2012', 15),('12/3/2012', 1034)SELECT t.Col1, CASE WHEN t.Col2 > 200 THEN t3.Col2 ELSE t.Col2 + t3.Col2 ENDFROM @table tCROSS APPLY (select top 1 t2.Col2 from @table t2 where t.Col1 < t2.Col1 order by t.col2 asc ) t3 WHERE CASE WHEN t.Col2 > 200 THEN t3.Col2 ELSE t.Col2 + t3.Col2 END >200ORDER BY Col1 asc JimEveryday I learn something that somebody else already knew
I am sorry but I am not able to follow what you mean with the t, t2, t3 part of that code, I am fairly new to SQL and SSRS still. I am getting my number by COUNT(vwPouringUnion.PT), and the date is vwPouringUnion.Date. Maybe I forgot to mention that I am writing this in a SSRS query not a view in SQL? |
|
|
|
|
|