Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Quert regarding getting the results based on months. Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 10:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:13 AM
Points: 202, Visits: 1,164

use adventureworks
go
select
YEAR(OrderDate) as orderyear,
SUM(case MONTH(orderdate) when 1 then TotalDue end) as January,
SUM(case MONTH(orderdate) when 2 then TotalDue end) as February,
SUM(case MONTH(orderdate) when 3 then TotalDue end) as March,
SUM(case MONTH(orderdate) when 4 then TotalDue end) as April,
SUM(case MONTH(orderdate) when 5 then TotalDue end) as May,

SUM(case MONTH(orderdate) when 6 then TotalDue end) as June,
SUM(case MONTH(orderdate) when 7 then TotalDue end) as July,
SUM(case MONTH(orderdate) when 8 then TotalDue end) as August,
SUM(case MONTH(orderdate) when 9 then TotalDue end) as September,
SUM(case MONTH(orderdate) when 10 then TotalDue end) as October,
SUM(case MONTH(orderdate) when 11 then TotalDue end) as November,
SUM(case MONTH(orderdate) when 12 then TotalDue end) as December
from Sales.SalesOrderHeader
group by YEAR(OrderDate)
order by orderyear


Below query gives the total due based on the year and month wise

i want to write a query which will return results according to below

1. if a select month as February the results should only be up to January
and if i select march the query should give results up to February
and end column should give the total
how to come up with the logic
Ex

Order Year January Total
Post #1401977
Posted Wednesday, January 2, 2013 12:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
Smash125, this is untested as I don't have AdventureWorks, but I think you will be able to gleen something from it. Also, you did not mention anything about year, but I suspect that will be an issue at some point. @year and @month will be parameters supplied by you.

SELECT
SUM(TotalDue) AS Total
FROM
(
SELECT
YEAR(orderdate) AS YR,
MONTH(orderdate) AS MO,
TotalDue
FROM Sales.SalesOrderHeader
) t1
WHERE t1.yr = @year
AND t1.mo < @month
GROUP BY t1.yr, t1.mo



Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1402040
Posted Wednesday, January 2, 2013 3:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 1,945, Visits: 3,068
Report Period Table [standard idio]

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Something_Report_Periods
(something_report_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (something_report_name LIKE <pattern>),
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (something_report_start_date <= something_report_end_date),
etc);

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

Weeks within Year:

I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.

You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'

There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1402108
Posted Wednesday, January 2, 2013 7:11 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
This is not the most elegant or optimized approach but this will get you the result set that you are looking for...

--Parameter
DECLARE @topMonth int=3;

--if a valid month is not selected, return all months
IF @topMonth<=0 OR @topMonth>12 SET @topMonth=12

DECLARE @x varchar(2000), @x2 varchar(1000), @i int=1,
@p1 varchar(40)='SUM(case MONTH(orderdate) when ',
@p2 varchar(50)='',
@months varchar(300)='(';

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL
DROP TABLE ##tmp;

SET @x='SELECT YEAR(OrderDate) as orderyear, '+CHAR(13);

WHILE @i<=@topMonth
BEGIN
SET @x=@x+@p1+CAST(@i AS varchar(2))+' then TotalDue end) as '
+CAST(DATENAME(MONTH,(CAST(@i AS varchar(2))+'/1/2000')) AS varchar(20))
+CASE WHEN @i<>@topMonth THEN ','+CHAR(13) ELSE CHAR(13) END

SET @months=@months+CAST(DATENAME(MONTH,(CAST(@i AS varchar(2))+'/1/2000')) AS varchar(20))+
CASE WHEN @i<>@topMonth THEN '+' ELSE ')' END

SET @i=@i+1
END;

SELECT @x=@x+'INTO ##tmp FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate) ORDER BY orderyear',
@x2 = 'SELECT *, '+@months+' AS GrandTotal FROM ##tmp ORDER BY orderyear'

EXEC(@x);
EXEC(@x2);

DROP TABLE ##tmp;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1402148
Posted Thursday, January 3, 2013 10:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 2,201, Visits: 3,313
CELKO (1/2/2013)

Since SQL is a database language, we prefer to do look ups and not calculations.



That "we" is undefined and thus meaningless. But people who have to write and run applications in the real world know that the I/O from any lookup is orders of magnitude more overhead than all but the most extreme calculations.

Apparently CELKO doesn't understand that computers perform billions of mathematical calculations per second.

Where possible, use calculations when appropriate rather than wasting extremely expensive I/O to do things that can be done with a nanosecond or microsecond calculation.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1402477
Posted Thursday, January 3, 2013 11:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
ScottPletcher (1/3/2013)
CELKO (1/2/2013)

Since SQL is a database language, we prefer to do look ups and not calculations.



That "we" is undefined and thus meaningless. But people who have to write and run applications in the real world know that the I/O from any lookup is orders of magnitude more overhead than all but the most extreme calculations.

Apparently CELKO doesn't understand that computers perform billions of mathematical calculations per second.

Where possible, use calculations when appropriate rather than wasting extremely expensive I/O to do things that can be done with a nanosecond or microsecond calculation.


I believe that was intended to be the "royal" we.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1402701
Posted Friday, January 4, 2013 3:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
dwain.c (1/3/2013) I believe that was intended to be the "royal" we.


Oh my goodness, Dwain. We laughed hard when we read that one.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1402773
Posted Friday, January 4, 2013 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 2,201, Visits: 3,313
dwain.c (1/3/2013)
ScottPletcher (1/3/2013)
CELKO (1/2/2013)

Since SQL is a database language, we prefer to do look ups and not calculations.



That "we" is undefined and thus meaningless. But people who have to write and run applications in the real world know that the I/O from any lookup is orders of magnitude more overhead than all but the most extreme calculations.

Apparently CELKO doesn't understand that computers perform billions of mathematical calculations per second.

Where possible, use calculations when appropriate rather than wasting extremely expensive I/O to do things that can be done with a nanosecond or microsecond calculation.


I believe that was intended to be the "royal" we.



But as CELKO is not royalty, that doesn't apply .

For example, plenty of people believe they are Napolean, but of course it doesn't actually make them Napolean!


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1402919
Posted Friday, January 4, 2013 10:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 1,945, Visits: 3,068
Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Something_Report_Periods
(something_report_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (something_report_name LIKE <pattern>),
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (something_report_start_date <= something_report_end_date),
etc);

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1402999
Posted Friday, January 4, 2013 10:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 2,201, Visits: 3,313
CELKO (1/4/2013)
Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Something_Report_Periods
(something_report_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (something_report_name LIKE <pattern>),
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (something_report_start_date <= something_report_end_date),
etc);

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'



Starting to look like "bot" posts -- the same ridiculous nonsense repeated ad infinitum.

NOTHING in that post is really true.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1403005
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse