Enter Month and year option

Hi everyone me again, I have an MDB that I need to Change into an ADP, SQL server is giving me fits on this and I need to make it so that when the users open this report it gives them the option to enter in the month and year in this format "January 2007" and then get the results on the report. The person who created the MDB gave that option with the code below how do I interpret that from Jet SQL to SQL Server?

Code Snippet

SELECT DISTINCTROW Inspectors.[Last Name], Inspectors.[First Name], Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], Count(*) AS [Count Of Main Table]
FROM Inspectors INNER JOIN [Main Table] ON Inspectors.ID = [Main Table].Inspector
GROUP BY Inspectors.[Last Name], Inspectors.[First Name], Format$([Main Table].Date,'mmmm yyyy'), Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and Year]));

This is what I got so far but its giving me fits

Code Snippet

SELECT Inspector, 'Date: Year([Main Table]).Date,mm yyyy)' AS [Date by Month], COUNT('Count of [Main Table]:Count(*)') AS [Count], YEAR(Date)
* 12 + DATEPART('m', Date) - 1 AS Year, 'Date:([Main Table].[Date],mmmm yyyy)' AS [Enter Month and Year]
FROM dbo.[Main Table]
GROUP BY Inspector, 'Date: Year([Main Table]).Date,mm yyyy)', YEAR(Date) * 12 + DATEPART('m', Date) - 1
HAVING ('Date:([Main Table].[Date],mmmm yyyy)' = @.Enter_Month AND 'Date:([Main Table].[Date],mmmm yyyy)' = @.Enter_Year)

You need to use the DATEPART function to get different pieces from your dates.

This documentation on MSDN should get you through it.

http://msdn2.microsoft.com/en-us/library/ms174420.aspx

The way you are calling your functions is syntactically incorrect. Simply use the function name and a column alias if neccessary.

Code Snippet

SELECT DATEPART(yyyy, t.date) AS Year

,COUNT(*) AS Count

FROM table t

GROUP BY t.Date

|||

Is this what you want the output to look like:

Inspector Date by Month Year Enter Month and Year
-- - -- --
1 3 2007 3 2007

or maybe something like:

declare @.enter_year integer set @.enter_year = 2007
declare @.enter_month integer set @.enter_month = 3

insert into dbo.[main table]
select 1, '3/15/7'

SELECT Inspector,
convert(varchar(2), datepart(mm, date)) as [Date by Month],
YEAR(Date) AS Year,
count(*) as [count],
convert(varchar(2), datepart(mm, date)) + ' '
+ convert (varchar(4), year(date)) as [Enter Month and Year]
FROM dbo.[Main Table]
where year(date) = @.enter_year
and month(date) = @.enter_month
group by inspector,
convert(varchar(2), datepart(mm, date)),
YEAR(Date),
convert(varchar(2), datepart(mm, date)) + ' '
+ convert (varchar(4), year(date))

-- Inspector Date by Month Year count Enter Month and Year
-- -- - -- -- --
-- 1 3 2007 1 3 2007

|||Well not exaclty see when they execute the query it asks them to enter in a date [Month and Year] then you get the results. It basically tells the users how many reports each inspector did for that month and year and they it gives the over all total|||

I think that this is basically the query you want.

Code Snippet

SELECT I.[Last Name], I.[First Name],
[Date by Month] = DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]),
[Count Of Main Table] = count(*)
FROM Inspectors I
INNER JOIN [Main Table] MT
ON (I.ID = MT.Inspector)
GROUP BY I.[Last Name], I.[First Name],
DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE])
WHERE (MT.[Date] >= CONVERT(datetime, @.EnterMonthYear)) AND
(MT.[Date] < DATEADD(month, 1, CONVERT(datetime, @.EnterMonthYear)))

@.EnterMonthYear is a varchar variable containing the input date in the form 'mmmm yyyy'. This SQL will error if the text in the variable cannot be converted to a date.

I removed the "DATEPART(year, MT.[Date]) * 12 + DATEPART(month, MT.[Date]) - 1" expression as it simply seemed to have a 1:1 correspondance with the Date by Month expression (so not changing the records generated by the GROUP BY) and was not used anywhere else. It seems to be generating some kind of serial month number.

I simplified the selection condition to work before the grouping and use the datetime fields directly. The one effect of this is that if they input a date with a day number on it (5 January 2007 for instance) they will get the data from 5 January 2007 to 4 February 2007 and will get up to 2 rows per inspector labelled January 2007 and February 2007 grouping this data appropriately.

Rather than using this SQL directly you might like to consider putting it in a stored procedure and calling that if possible. This will allow handling of the bad date problems (and the forcing of the input date to first of the month etc.). Also it will allow any later maintenance to be performed in the datebase rather than application (as long as the stored procedure calling convention/arguments do not change).