Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

COUNT the number of enquiries for the past 6 MONTHS

I want to return a table (to use as a dataview) which has the name of the past 6 months (each month with the count of no. of inquiries) from today's date and a count on the no. of inquiries in that period

so at the moment I have a table which has a date columns and an inquiry column
0
manivineet
Asked:
manivineet
  • 6
  • 6
  • 2
1 Solution
 
chapmandewCommented:
post your table structure.
0
 
Kevin CrossChief Technology OfficerCommented:
Something like this should work:
SELECT Month(datecol) AS InquiryMonth
, Count(inquiry) AS InquiryCount
FROM inquiries
WHERE datecol >= DateAdd(mm, -6, DateAdd(dd, -day(getdate())+1, DateDiff(dd, 0, getdate())))
GROUP BY Month(datecol)
ORDER BY 1

Open in new window

0
 
manivineetAuthor Commented:
here is my table structure
clip-image002.jpg
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
manivineetAuthor Commented:
for mwvisa1:

the current solution looks fine, but it returns data for past three months and not 6 months, how can we modify it?

pardon my ignorance, for i'am a complete beginner
0
 
Kevin CrossChief Technology OfficerCommented:
Or in the honor of chapmandew, you can use a common table expression like this:
;WITH inquiryCTE AS (
    SELECT DateDiff(mm, datecol, getdate()) AS MonthsInThePast
    , Month(datecol) AS InquiryMonth
    , Count(inquiry) AS InquiryCount
    FROM inquiries
    GROUP BY DateDiff(mm, datecol, getdate()), Month(datecol)
)
SELECT *
FROM inquiryCTE
WHERE MonthsInThePast <= 6

Open in new window

0
 
manivineetAuthor Commented:
actually ignore that please... for my database doesnt have entry before july (7)
0
 
Kevin CrossChief Technology OfficerCommented:
>>the current solution looks fine, but it returns data for past three months and not 6 months, how can we modify it?

Do you have data in the inquiry table from 6 months ago?

This query will only show month if it has a count.  If it doesn't it will not show up.  For that, you will have to join to the report through UNION or something to that effect a query with all months.
0
 
manivineetAuthor Commented:
can we also return the YEAR as another column as well like

year month inquires
2008   9      243
0
 
Kevin CrossChief Technology OfficerCommented:
In either query you just add this to the select and group by clauses Year(datecol) where datecol is the name of your actual date column.
0
 
Kevin CrossChief Technology OfficerCommented:
If I am reading the image correctly, this should be your query:
SELECT Year(dttSubmitted) AS EnquiryYear
, Month(dttSubmitted) AS EnquiryMonth
, Count(*) AS EnquiryCount
FROM tblEnquiry
WHERE dttSubmitted >= DateAdd(mm, -6, DateAdd(dd, -day(getdate())+1, DateDiff(dd, 0, getdate())))
GROUP BY Year(dttSubmitted), Month(dttSubmitted)
ORDER BY 1 DESC, 2 DESC

Open in new window

0
 
manivineetAuthor Commented:
thank you so much mwvisa1
0
 
manivineetAuthor Commented:
just one more addition to it... even if the ENQUIRY COUNT is 0 , still the table should show year and month
0
 
chapmandewCommented:
Do I have you hooked on CTEs yet?  ;)
0
 
Kevin CrossChief Technology OfficerCommented:
I think so!

@manivineet, as suggested earlier, you will need to right join to a table holding all days OR months OR use a UNION to all days OR months.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now