Sachin 80451 (12/6/2012)
Unfortunately i dont have the faintest idea.
Is there any way you can break it down for me?
Okay let me thave a go.
Cadavre introduces the concept of a tally table. In his solution he creates the table on the fly which is great but as this numbers table is so useful I highly recommend you create this table permanently in a utility database perhaps?
Here is one way of creating the Tally table:
If exists (select 1 from information_schema.tables where table_name = 'Tally')
drop table dbo.[Tally]
CREATE TABLE dbo.Tally (N INT,CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <= 10000
INSERT INTO dbo.Tally (N)
SET @Counter = @Counter + 1
Now that we have our numbers we can get the first day of each month as follows:
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) as N
The above bit of code looks complicated but I will try and explain. Our goal is to get one record per month as per yor requirement. To do this we start with:
SELECT DATEDIFF(month, 0, GETDATE())+N
This gives us the number of months since 01/01/1900 this is the date you get when you cast the value 0 as a date. So the above query will run somethign like this.
1) Number of months since 01/01/1900 to this day = 1355 + N (tally table starts at 0) so = 1355
2) Number of months since 01/01/1900 to this day = 1355 + N (next value of N is 1 ) so = 1356
and so on.
If we then add the number of months back to 01/01/1900 we will get back 1 record per month
And to get the name we can make the above a derived table or CTE as follows:
select DATENAME(month,N), YEAR(N)
FROM (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) as N
FROM TALLY) as a
Hope this helps.
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn