August 3, 2010 at 7:24 pm
Hello,
I have a query that needs some monthly date functionality added to it.
I think this can be achieved through GETDATE and Subqueries. Whenever the query is ran, it
will evaluate the GETDATE and return the results for that Entire month. So for example
if the query is ran on 8/16/10 it will evaluate GETDATE and return everything in August
(8/1/10-8/31/10) for @MONTH1 as 'August'
Then based off that, I would like @MONTH2, @MONTH3, @MONTH4, @MONTH5, @MONTH6 as
the next subquent months after month 1 each as their own subqueries.
This is a rough draft of what I think so far. Let me know if Im heading in the right direction.
---unknown logic...
, SUM(CASE WHEN ((A.EXPECTMOVEINDATE>= CONVERT(VARCHAR(10),DATEADD(MM,1,@DATEVARIABLE),101)
AND A.EXPECTMOVEINDATE<=@DATEVARIABLE1
AND A.MOVEINDATE IS NULL)
OR (A.MOVEINDATE>=CONVERT(VARCHAR(10),DATEADD(DD,1,@DATEVARIABLE),101)
AND A.MOVEINDATE<=@DATEVARIABLE1)
OR (L.EXPECTXFERDATE>=CONVERT(VARCHAR(10),DATEADD(MM,1,@DATEVARIABLE),101)
AND L.EXPECTXFERDATE<=@DATEVARIABLE1
AND L.XFERDATE IS NULL
AND L.RESIXFERSTATUS='1'))
AND A.CANCELDATE IS NULL
AND A.PROPERTYID = @SERENA
THEN 1 ELSE 0 END) AS MONTH1
, SUM(CASE WHEN ((A.EXPECTMOVEINDATE>= CONVERT(VARCHAR(10),DATEADD(MM,1,@DATEVARIABLE1),101)
AND A.EXPECTMOVEINDATE<=@DATEVARIABLE2
AND A.MOVEINDATE IS NULL)
OR (A.MOVEINDATE>=CONVERT(VARCHAR(10),DATEADD(DD,1,@DATEVARIABLE1),101)
AND A.MOVEINDATE<=@DATEVARIABLE2)
OR (L.EXPECTXFERDATE>=CONVERT(VARCHAR(10),DATEADD(MM,1,@DATEVARIABLE1),101)
AND L.EXPECTXFERDATE<=@DATEVARIABLE2
AND L.XFERDATE IS NULL
AND L.RESIXFERSTATUS='1'))
AND A.CANCELDATE IS NULL
AND A.PROPERTYID = @SERENA
THEN 1 ELSE 0 END) AS MONTH2
August 3, 2010 at 8:19 pm
I think you're close. You might want to consider this...
Create a small table of the month # and starting/ending dates:
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;
CREATE TABLE #TestTable (MonthNbr tinyint, StartDate datetime, EndDate datetime, PRIMARY KEY CLUSTERED (StartDate, EndDate));
WITH CTE (N) AS
(
SELECT TOP (6) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.objects
)
INSERT INTO #TestTable
SELECT N, DateAdd(month, DateDiff(month, 0, GetDate())-1+N, 0), dateadd(month, DateDiff(month, 0, GetDate())+N, 0)
FROM CTE;
SELECT *
FROM #TestTable;
Then join on this table to get sums by the month, and it looks like all you would have left to do is to pivot the data:
WITH CTE AS
(-- sum the data by month
SELECT t1.MonthNbr, Total = sum()...
FROM ...
JOIN #TestTable t1
ON L.EXPECTXFERDATE >= t1.StartDate
AND L.EXPECTXFERDATE < t1.EndDate
GROUP BY t1.MonthNbr
) -- pivot the data by month
SELECT Month1 = SUM(CASE WHEN MonthNbr = 1 THEN Total ELSE 0 END),
Month2 = SUM(CASE WHEN MonthNbr = 2 THEN Total ELSE 0 END),
Month3 = SUM(CASE WHEN MonthNbr = 3 THEN Total ELSE 0 END),
Month4 = SUM(CASE WHEN MonthNbr = 4 THEN Total ELSE 0 END),
Month5 = SUM(CASE WHEN MonthNbr = 5 THEN Total ELSE 0 END),
Month6 = SUM(CASE WHEN MonthNbr = 6 THEN Total ELSE 0 END)
FROM CTE;
So, how does this look for you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 4, 2010 at 12:33 pm
Wow, that looks awesome.
What does it do? lol
Honestly you blasted way past me with your SQL knowledge. You went to the moon and
im still stuck in Rhode Island.
August 5, 2010 at 11:58 am
If you will have to do this sort of thing more than once, I highly recommend setting up a monthly calendar table to make coding much easier and increase performance.
Here is a link to an article I wrote on the subject published last month:
http://www.sqlservercentral.com/articles/T-SQL/70482/
Todd Fifield
August 5, 2010 at 12:30 pm
Ricardumus (8/4/2010)
Wow, that looks awesome.What does it do? lol
Honestly you blasted way past me with your SQL knowledge. You went to the moon and
im still stuck in Rhode Island.
Hey, I didn't see this response. Do you need an explanation of this code?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 12:33 pm
I just didn't understand the CTE (N). Not sure what that is. 🙂
August 5, 2010 at 12:49 pm
Ricardumus (8/5/2010)
I just didn't understand the CTE (N). Not sure what that is. 🙂
Here is a link to a pretty good primer article about CTEs and what they can be used for.
August 5, 2010 at 1:47 pm
Ricardumus (8/5/2010)
I just didn't understand the CTE (N). Not sure what that is. 🙂
A "CTE" (Common-Table-Expression) is essentially a pre-defined sub-query. It starts with the keyword "WITH", followed by the table alias (in my case, CTE). Optionally, it can have a comma-delimited list of fields being output, encased in parenthesis. It is followed by the keyword AS, an open-parenthesis, the sub-query, a closing parenthesis, optional additional CTEs, and finally the SQL statement (select, insert, update, delete) that you are performeing. You can "chain" multiple CTEs together; to continue put a comma after that closing parenthesis, and then proceed from the next alias.
So, "WITH CTE(N)" means:
The following query will go by the alias "CTE", and will have one column with the name "N".
The query:
SELECT t1.MonthNbr, Total = sum()...
FROM ...
JOIN #TestTable t1
ON L.EXPECTXFERDATE >= t1.StartDate
AND L.EXPECTXFERDATE < t1.EndDate
GROUP BY t1.MonthNbr
is the pre-defined subquery.
From this point, take the results of the CTE, and use that to get the final results. In this case, we're pivoting by the month. For a more in-depth explanation of cross-tab/pivots, see the two links in my signature (Parts 1 and 2) on this subject.
Obviously, the ... is left for you to define based on your system (columns to sum, tables to get data out of).
So, any further questions? Does this explain it all to you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 2:37 pm
Yes thanks so much Wayne! I appreciate all the help you have given me!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply