July 14, 2012 at 1:08 pm
I have a SQL 2008 table that keeps a record of bkup attempts -types of bkup (FULL, LOG,etc.), date of bkup attempt, and return code from the bkup attempt (lots of values but for me I want 0/GOOD, <>0/FAILURE).
I'm looking to aggregate/group the data by month, count of good, count of bad, sum of good + bad for each month (across a provided date range) with a monthly subtotal & a grand total for the entire date range.
In addition, rather than the "NULL" description that Rollup/Cube provides in subtotal categories, I'd prefer a label of my own such as 'Grand Total' or 'All Bkup Types' (picky, ain't I?).
Hints anyone?
MON | TYPE | # of good by type | # of bad by type | sum good+bad by type
--------------------------------------------------------------------------------------------------------------------
JAN | FULL | JAN # good FULL bkups | JAN # bad FULL bkups | #Jan good+bad FULL bkup (C+D this row)
JAN | LOG | JAN # good LOG bkups | JAN # bad LOG bkups | #Jan good+bad LOG bkups (C+D this row)
...
...
JAN |'ALL TYPES' | FEB #good bkups all types | FEB # bad bkups all types | sum # FEB bkups all types
FEB | FULL | FEB # good FULL bkups | FEB # bad FULL bkups | #FEB good+bad FULL bkup (C+D this row)
FEB | LOG | FEB # good LOG bkups | FEB # bad LOG bkups | #FEB good+bad LOG bkups (C+D this row)
...
...
'ALL' |'ALL TYPES' |# good bkups all months |# bad bkups all months | # all bkups all months
July 16, 2012 at 8:26 am
So it has been two days since you posted and not a single person has responded. That is because you didn't even really provide a question. Step back and read your post and ask yourself if you think you could answer the question based on what you posted. We can't see over your shoulders and have no knowledge of your project. There is absolutely no way anybody can help unless you provide some details. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 16, 2012 at 8:32 am
Well, Sean had a good point... but I will try to give you a "hint" on what I would do. Write a stored proc that takes a date range as parameters and it returns raw data. I would then create a report in SSRS that uses the returned data and formats it in the presentation layer to give the groupings and sub-totals you are looking for.
Jared
CE - Microsoft
July 16, 2012 at 10:35 am
Sean - great point, just reread my post & you're right, insufficient detail provided to adequately explain my situation/question.
SQLKnowItAll - thanks for the SSRS suggestion but I intend this question to be a T-SQL exercise , mostly for my own benefit.
Thanks for the help & pointing out the forum etiquette. Let me get this rephrased & I'll try again.
July 16, 2012 at 2:13 pm
Something like below should do it:
SELECT
CASE WHEN backup_month IS NULL THEN 'ALL'
ELSE LEFT(DATENAME(MONTH, backup_month), 3) END AS MON,
CASE WHEN backup_type IS NULL THEN 'ALL TYPES'
ELSE backup_type END AS TYPE,
#_Good,
#_Bad,
#_Total
FROM (
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, backup_date), 0) AS backup_month,
backup_type,
SUM(CASE WHEN return_code = 0 THEN 1 ELSE 0 END) AS #_Good,
SUM(CASE WHEN return_code = 0 THEN 0 ELSE 1 END) AS #_Bad,
SUM(1) AS #_Total
FROM dbo.tablename
GROUP BY
ROLLUP(
DATEADD(MONTH, DATEDIFF(MONTH, 0, backup_date), 0),
backup_type
)
) AS derived
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply