I need to sort data by date to produce a graph 1

[bloko]

Hi,

Got given a problem to look at and im a bit stuck. need to get a running total from the last 12 months to show progression.

Below is the code which was already in place to show data per month and the code in the *** is what I have tried to do. I have tried lots of different combinations and the best i got was showing was the running totals for each year.

SELECT TOP (100) Decision, MONTH(Date) AS month, YEAR(Date) AS Year, CASE WHEN len(rtrim(CAST(Year(Date) AS char)) + rtrim(CAST(MONTH(Date)
AS char))) = 5 THEN rtrim(CAST(Year(Date) AS char)) + '0' + rtrim(CAST(MONTH(Date) AS char)) ELSE rtrim(CAST(Year(Date) AS char))
+ rtrim(CAST(MONTH(Date) AS char)) END AS Date,
(SELECT COUNT(Completed) AS Expr1
FROM dbo.SRW_Incident_View
WHERE (Decision = a.Decision) AND (Completed = 0) AND (MONTH(Date) = MONTH(a.Date)) AND (YEAR(Date) = YEAR(a.Date))) AS [Open],
(SELECT COUNT(Completed) AS Expr1
FROM dbo.SRW_Incident_View AS SRW_Incident_1
WHERE (Decision = a.Decision) AND (Completed = 1) AND (MONTH(Date) = MONTH(a.Date)) AND (YEAR(Date) = YEAR(a.Date))) AS Complete,

************************************************
(SELECT COUNT(Completed) AS Expr1
FROM dbo.SRW_Incident_View AS SRW_Incident_1
WHERE (Decision = a.Decision) AND (MONTH(a.Date) <= MONTH(Date)) AND (Date > DATEADD(m, - 12, YEAR(a.Date)))) AS Totalc
FROM dbo.SRW_Incident_View AS a
WHERE (Date >= YEAR(DATEADD(m, - 12, YEAR(Date))))
GROUP BY Decision, Value_Stream, MONTH(Date), YEAR(Date)
************************************************
HAVING (Value_Stream = 'Aftermarket') AND (Decision = 'Conc/Salvage') OR
(Value_Stream = 'Defence') AND (Decision = 'Conc/Salvage') OR
(Value_Stream = 'Industrial') AND (Decision = 'Conc/Salvage')

I did manage to get a running total when looking at just the running total but when i try to do it to also get the date values it doesnt work.

SELECT TOP (100) PERCENT Date,
(SELECT SUM([Open] + Complete) AS Expr1
FROM dbo.SRW_Stats_Tot_Conc
WHERE (LEN(Date) > 5) AND (Date <= O.Date)) AS Running_Total
FROM dbo.SRW_Stats_Tot_Conc AS O
WHERE (Date > 2010)
GROUP BY Date

Any help would be appreciated, and saying it cant be done would win me an argument.

 

[bloko]

I kind of solved the problem by using a new view and using

SELECT TOP (100) PERCENT Date, [Open], Complete, month,
(SELECT SUM([Open] + Complete) AS Expr1
FROM dbo.SRW_Stats_Tot_Conc
WHERE (LEN(Date) > 5) AND (Date <= O.Date) AND (Date >= DATEADD(mm, - 9, GETDATE()))) AS Running_Total
FROM dbo.SRW_Stats_Tot_Conc AS O
GROUP BY Date, [Open], Complete, month

One problem I am having is that I only need to look at the past 12 month of data and the above code doesnt seem to do this. Any ideas?

 

[djj55]

For a running 12 months why not add something like:

WHERE [DATE] BETWEEN Dateadd(mm, -12, getdate()) and getdate()

or some variation of criteria.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[bloko]

Hi djj55,

Thanks for your reply, I didnt think of using a date range, but seems a good idea, my only issue is that my column "Date" is the year and month added together eg 201002, so when I use your code it isnt bringing back any info. Any ideas of a way round this?

 

[djj55]

Depending on the requirements try using the first or fifteenth of the month by creating your date to compare.

CAST([Date] + '01' AS DateTime)

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!