CODEWITHSUNDEEP
sqlsql-server
user2147357
user2147357

Reputation: 127

sql query for monthly data

I have written this query that gives day wise data of all 365 days of all 12 months when i select a year from drop down list.

SELECT
      YEAR(Date) AS [Year]
    , MONTH(Date) AS [Month]
    , COUNT(*) AS [Total]
FROM EnquiryMaster
WHERE EnquiryOwner = '" + enqowner + "'
     AND DATEPART(yyyy, Date) = '" + year + "'
     AND orgid = '" + orgid + "'
GROUP BY
      YEAR(Date)
    , MONTH(Date)

UNION ALL

SELECT
      YEAR(Date) AS [Year]
    , NULL
    , COUNT(*)
FROM EnquiryMaster
WHERE EnquiryOwner = '" + enqowner + "'
     AND DATEPART(yyyy, Date) = '" + year + "'
     AND orgid = '" + orgid + "'
GROUP BY YEAR(Date)

I want to write a query that will select month also along with year and display data only for that month

Upvotes: 3

Views: 13455

Answers (2)

chetan
chetan

Reputation: 2886

if you want result for only one month then put it in where clause (1 for jan, 2 for feb)

SELECT
      YEAR(Date) AS [Year]
    , MONTH(Date) AS [Month]
    , COUNT(*) AS [Total]
FROM EnquiryMaster
WHERE EnquiryOwner = '" + enqowner + "'
     AND DATEPART(yyyy, Date) = '" + year + "'
     AND orgid = '" + orgid + "'
     AND MONTH(Date) = '" + month + "'
GROUP BY
      YEAR(Date)
    , MONTH(Date)

UNION ALL

SELECT
      YEAR(Date) AS [Year]
    , NULL
    , COUNT(*)
FROM EnquiryMaster
WHERE EnquiryOwner = '" + enqowner + "'
     AND DATEPART(yyyy, Date) = '" + year + "'
     AND orgid = '" + orgid + "'
      AND MONTH(Date) = '" + month + "'
GROUP BY YEAR(Date)

Upvotes: 0

asafrob
asafrob

Reputation: 1858

I think this is what you want, assuming I understood the question

SELECT
    DATEPART(yyyy, Date), 
    DATEPART(mm, Date),
    COUNT(*) AS [Total]
FROM EnquiryMaster
GROUP BY
    DATEPART(yyyy, Date), 
    DATEPART(mm, Date)
ORDER BY
    DATEPART(yyyy, Date), DATEPART(mm, Date)

Upvotes: 1

Related Questions