SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

manasa601
Starting Member

1 Post

Posted - 2010-05-21 : 12:44:40

I am trying to write a sql query that will give me the usage details between a start date and end date

Here is the query

DECLARE @T1 TABLE (T1Months Datetime,NewUsers int)

INSERT INTO @T1 (T1Months,NewUsers)

SELECT
DateAdd(Month, DateDiff(Month, 0, RegisteredOn), 0) as Months,
COUNT(users.Id) AS NewUsers
FROM [TCV_11_PROD].[dbo].[Users] WHERE RegisteredOn is not Null and RegisteredOn > '2008-06-01 12:00 AM' and RegisteredOn < '2010-05-31 12:00 AM'
GROUP BY DateAdd(Month, DateDiff(Month, 0, RegisteredOn), 0)

DECLARE @T2 TABLE( Months Datetime,Newusers int,TotalUsers int)

INSERT INTO @T2 ( Months, Newusers, TotalUsers)
Select T1Months, NewUsers ,(Select sum(NewUsers) from @T1
where T1Months <= T.T1Months) As TotalUsers
from @T1 T
order by T1months desc

DECLARE @T3 TABLE (T3Months Datetime,Newcerts int)

INSERT INTO @T3 (T3Months,Newcerts)

SELECT DateAdd(Month, DateDiff(Month, 0,CertificationDate), 0) AS Months,
COUNT(*) AS NewCerts
FROM [TCV_11_PROD].[dbo].[Projects] INNER JOIN [TCV_11_PROD].[dbo].[Users] ON [TCV_11_PROD].[dbo].Projects.BuilderID = [TCV_11_PROD].[dbo].Users.ID WHERE RegisteredOn is not null And CertificationDate is not Null and CertificationDate > '2008-06-01 12:00 AM' and CertificationDate < '2010-05-31 12:00 AM'
GROUP BY DateAdd(Month, DateDiff(Month, 0, CertificationDate), 0)
ORDER BY DateAdd(Month, DateDiff(Month, 0, CertificationDate), 0) DESC

DECLARE @T4 TABLE( Months Datetime, Newcerts int,Totalcerts int)

INSERT INTO @T4 ( Months, Newcerts, TotalCerts)
Select T3Months, Newcerts ,(Select sum(Newcerts) from @T3
where T3Months <= T.T3Months) As Totalcerts
from @T3 T
order by T3months desc

Select T4.Months,TotalUsers, TotalCerts
from @T4 T4 inner join @T2 T2 on T4.Months=T2.Months
order by T4.months desc

as we want to see user details even if it is "0" (for the graph)

my boss wanted me to work as shown below

--Find the Range of Days Available
--* Need to convert this to the start of the month
declare @maxmonth datetime
declare @minmonth datetime
set @maxmonth = (Select MAX(CreatedOn) From Projects)
print @maxmonth
set @minmonth = (Select Min(CreatedOn) From Projects)
print @minmonth

/* Populate a Month List
Using the previously found dates create a list of only
selectable months by filtering based on what you find for
maxmonth and minmonth */

/* Join the month list with any metric queries so that
empty months still show up in your result sets */

/* 2008 R2 join the metric sets together so you get
County, Monthly_Users,Total_users,Montly_Projects,Total_Projects,Montly_Certs,Total_Certs
and City, Monthly_Users,Total_users,Montly_Projects,Total_Projects,Montly_Certs,Total_Certs */

/* 2008 R2 - if you join all the metric sets together, you can parameterize a subselect
as Select ? and use a custom table to list available metrics.
You can then select individual metrics and show them by themselves
(this will be key in doing this in GIS/mapping as the GIS object only
wants 2 columns - county & value) */

/* 2008 R2 - In reporting services, you can take the top two queries that populate
a month list and use them all by themselve to populate a range
that you can use as a paramter ... This will let you create a report
tool that you can use to select any individual month .. this will also
help in GIS/mapping as you can drilldown to a map that shows only the
current month and pairs with the previous parameter you can build
a report that you can select a metric and select a month and visually
compare the metrics*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources