March 3, 2016 at 4:43 pm
Hi,
At present am showing quarters from 2015-Q1 to Q4 , it should be like that, because report must show data for last 4 quarters only.
Whenever i got data for "2016-Q1" then automatically report should change and display data for "2016-Q1" - "2015-Q4" - "2015-Q3" - "2015-Q2", it should be based on current quarter. same for upcoming quarters also.
Is it possible??
again this is for reporting purpose only... there is 4 sets of query and using UNION
and also in the logic its hard coded as:
'2015 01' AS Qtr from Table WHERE (YYYYMM = 201503)
UNION
'2015 02' AS Qtr from Table WHERE (YYYYMM = 201506)
UNION
'2015 03' AS Qtr from Table WHERE (YYYYMM = 201509)
UNION
'2015 04' AS Qtr from Table WHERE (YYYYMM = 201512).
please let me know if anyone need more details.
March 3, 2016 at 5:06 pm
do it in your WHERE clause.
WHERE MyTable.MyDate>DATEADD(yyyy,-1,GETDATE())
March 3, 2016 at 6:15 pm
mandymvn (3/3/2016)
Hi,At present am showing quarters from 2015-Q1 to Q4 , it should be like that, because report must show data for last 4 quarters only.
Whenever i got data for "2016-Q1" then automatically report should change and display data for "2016-Q1" - "2015-Q4" - "2015-Q3" - "2015-Q2", it should be based on current quarter. same for upcoming quarters also.
Is it possible??
again this is for reporting purpose only... there is 4 sets of query and using UNION
and also in the logic its hard coded as:
'2015 01' AS Qtr from Table WHERE (YYYYMM = 201503)
UNION
'2015 02' AS Qtr from Table WHERE (YYYYMM = 201506)
UNION
'2015 03' AS Qtr from Table WHERE (YYYYMM = 201509)
UNION
'2015 04' AS Qtr from Table WHERE (YYYYMM = 201512).
please let me know if anyone need more details.
Just making double sure... do you want the report headings to auto-magically change based on today's date (whatever it is)?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2016 at 6:42 pm
Hi
pietlinden
could you please elaborate a bit, I am not getting 🙁
March 3, 2016 at 6:46 pm
Hi Jeff,
"do you want the report headings to auto-magically change based on today's date (whatever it is)?"
is there a way to do it ? I am not sure If possible yeah pls give me some suggestions.
Thanks
March 4, 2016 at 12:08 am
mandymvn (3/3/2016)
Hi Jeff,"do you want the report headings to auto-magically change based on today's date (whatever it is)?"
is there a way to do it ? I am not sure If possible yeah pls give me some suggestions.
Thanks
Heh... of course there's a way to do it. 🙂
First, we need some test data so that you can see that we're not bogging things down too by getting clever. This will create a million row test table in about 4 seconds (on my laptop, anyway). The details as to what the content and limits on each column are in the comments in the code. This is my de facto standard test table.
-- DROP TABLE dbo.JBMTest
;
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2010 and <01/01/2020 non-unique date/times
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2010','2020')+CAST('2010' AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Add the temporal Clustered Index for performance purposes.
CREATE CLUSTERED INDEX IXC_JBMTest
ON dbo.JBMTest (SomeDateTime)
;
--===== Show the first 100 rows to see what the data looks like.
SELECT TOP 100 * FROM dbo.JBMTest
;
We could do an Indexed View but those have too many limitations so far as I'm concerned. So, here's a regular view that creates a pretty nifty vertically oriented report that automatically adjusts the reported quarters based on whatever the current date is. Again, the details are in the comments and there are some usage examples in the header of the code. There are also some references (the 2nd & 3rd "Ref:) that you should read to understand the GROUP BY WITH CUBE and the GROUPING() function.
--DROP VIEW dbo.PreviousQuarters
;
GO
CREATE VIEW dbo.PreviousQuarters AS
/**************************************************************************************************
Purpose:
Aggregate the last 3 quarters and the current quarter through all of today and produce a
"vertical" report.
It has all sorts of different sub-totals, looks kind of pretty, is nasty fast, and you never
have to change a date because it figures out the quartes auto-magically.
You can also do neat things like selecting just detail rows or just subtotal rows, etc.
Examples (not all inclusive and sorts not guaranteed without an ORDER BY):
--===== Produce the entire report
SELECT *
FROM dbo.PreviousQuarters
ORDER BY SortOrder
;
--===== Produce a report just for where SomeLetters 2 = 'JM' or 'QZ'
-- and include a "Percent of Total" for each row where the "Total"
-- is the total of SomeMoney between the two. Since subtotals are
-- included in all that, we have to divide the total by 2.
-- A "Percent of Grand Total" for all data in the quarters is included, as well.
SELECT *
,PercentOfTotal = SomeMoney/((SUM(SomeMoney) OVER ())/2) * 100
,PercentofGrandTotal = SomeMoney*100.0/(SELECT SUM(SomeMoney) FROM dbo.PreviousQuarters)
FROM dbo.PreviousQuarters
WHERE SomeLetters2 IN ('JM','QZ')
ORDER BY SortOrder
;
--===== Produce a report just for quarter subtotals and the grand total
SELECT *
FROM dbo.PreviousQuarters
WHERE RowDescription IN ('SubTotal Quarter','Grand Total')
ORDER BY SortOrder
;
--===== Produce a report just for SomeLetters2 subtotals and the grand total
SELECT *
FROM dbo.PreviousQuarters
WHERE RowDescription IN ('SubTotal SomeLetters2','Grand Total')
ORDER BY SortOrder
;
Revision History:
Rev 00 - 04 Mar 2016 - Jeff Moden
- Initial creation and unit test
- Ref: http://www.sqlservercentral.com/Forums/Topic1766578-3412-1.aspx#bm1766600
- https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms178544(v=sql.105).aspx
**************************************************************************************************/
WITH cteQuarter AS
(
SELECT Qtr = DATENAME(yy,SomeDateTime)+' Q'+DATENAME(qq,SomeDateTime)
,SomeLetters2
,SomeMoney
FROM dbo.JBMTest
WHERE SomeDateTime >= DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0) --Start of 3 quarters ago
AND SomeDateTime < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0) --Through all of today
)
SELECT SortOrder =
ROW_NUMBER() OVER (ORDER BY GROUPING(SomeLetters2), SomeLetters2
,GROUPING(Qtr), Qtr)
,RowDescription =
CASE
WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 0 THEN 'Detail'
WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 1 THEN 'SubTotal SomeLetters2'
WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 0 THEN 'SubTotal Quarter'
WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 1 THEN 'Grand Total'
ELSE 'ERROR'
END
,Qtr =
CASE
WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 0 THEN Qtr
WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 1 THEN ''
WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 0 THEN Qtr
WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 1 THEN ''
ELSE 'ERROR'
END
,SomeLetters2 =
CASE
WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 0 THEN SomeLetters2
WHEN GROUPING(SomeLetters2) = 0 AND GROUPING(Qtr) = 1 THEN SomeLetters2
WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 0 THEN ''
WHEN GROUPING(SomeLetters2) = 1 AND GROUPING(Qtr) = 1 THEN ''
ELSE 'ERROR'
END
,SomeMoney = SUM(SomeMoney)
FROM cteQuarter
GROUP BY SomeLetters2, Qtr WITH CUBE
;
If no one steps up, I'll show you how to do a dynamic CROSSTAB to make a "horizontal" report like most spreadsheet users would expect. Just not tonight. I've got to get some sleep.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2016 at 11:52 am
Hi Jeff,
used this in WHERE clause as:
where Qtr >=DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0) AND Qtr < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)
i tried to convert but still getting the error
and getting conversion error :Conversion failed when converting date and/or time from character string.
i have a 'Qtr is a Varchar(10)' in a table
any help ??
March 4, 2016 at 12:49 pm
DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0)
... returns a DATE value.
'2015 01' is a STRING value, not a date. You would have to turn that into a date... maybe split off the year, and add 0 - 3 quarters to it.
March 4, 2016 at 1:20 pm
mandymvn (3/4/2016)
Hi Jeff,used this in WHERE clause as:
where Qtr >=DATEADD(qq,DATEDIFF(qq,0,GETDATE())-3,0) AND Qtr < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)
i tried to convert but still getting the error
and getting conversion error :Conversion failed when converting date and/or time from character string.
i have a 'Qtr is a Varchar(10)' in a table
any help ??
Do you also have some sort of DATETIME column?
Also, please see the article at the first link under "Helpful Links" in my signature line below before you post again. It'll help save a lot on this type of confusion in the future and you'll have a pat answer much more quickly. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply