InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 01:29:31 GMT20http://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspx[quote][b]ScottPletcher (1/4/2013)[/b][hr][quote][b]CELKO (1/4/2013)[/b][hr]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'[/quote]Starting to look like "bot" posts -- the same ridiculous nonsense repeated ad infinitum.NOTHING in that post is really true.[/quote]!!!!ROTFLMAO!!!! :hehe::-P:hehe::-P:hehe::-PFri, 04 Jan 2013 17:50:53 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspx[quote][b]CELKO (1/4/2013)[/b][hr]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'[/quote]Starting to look like "bot" posts -- the same ridiculous nonsense repeated ad infinitum.NOTHING in that post is really true.Fri, 04 Jan 2013 10:36:20 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspxSince 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'Fri, 04 Jan 2013 10:24:54 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspx[quote][b]dwain.c (1/3/2013)[/b][hr][quote][b]ScottPletcher (1/3/2013)[/b][hr][quote][b]CELKO (1/2/2013)[/b]Since SQL is a database language, we prefer to do look ups and not calculations. [/quote]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 [i][b]billions[/b] of mathematical calculations [b]per second[/b][/i]. 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.[/quote]I believe that was intended to be the "royal" we.[/quote]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!Fri, 04 Jan 2013 08:43:10 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspx[quote][b]dwain.c (1/3/2013)[/b] I believe that was intended to be the "royal" we.[/quote]Oh my goodness, Dwain. We laughed hard when we read that one.Fri, 04 Jan 2013 03:50:58 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspx[quote][b]ScottPletcher (1/3/2013)[/b][hr][quote][b]CELKO (1/2/2013)[/b]Since SQL is a database language, we prefer to do look ups and not calculations. [/quote]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 [i][b]billions[/b] of mathematical calculations [b]per second[/b][/i]. 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.[/quote]I believe that was intended to be the "royal" we.Thu, 03 Jan 2013 23:38:30 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspx[quote][b]CELKO (1/2/2013)[/b]Since SQL is a database language, we prefer to do look ups and not calculations. [/quote]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 [i][b]billions[/b] of mathematical calculations [b]per second[/b][/i]. 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.Thu, 03 Jan 2013 10:45:39 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspxThis is not the most elegant or optimized approach but this will get you the result set that you are looking for... [code="sql"]--ParameterDECLARE @topMonth int=3;--if a valid month is not selected, return all months IF @topMonth<=0 OR @topMonth>12 SET @topMonth=12DECLARE @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<=@topMonthBEGIN 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+1END;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;[/code]Wed, 02 Jan 2013 19:11:32 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspxReport 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 Wed, 02 Jan 2013 15:48:37 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspxSmash125, 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.[code]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[/code]Wed, 02 Jan 2013 12:32:53 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1401977-392-1.aspxuse adventureworksgoselect 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 Decemberfrom Sales.SalesOrderHeadergroup by YEAR(OrderDate)order by orderyearBelow query gives the total due based on the year and month wisei 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 Januaryand if i select march the query should give results up to Februaryand end column should give the totalhow to come up with the logicExOrder Year January TotalWed, 02 Jan 2013 10:57:26 GMTSmash125