अलग-अलग तिथियों को महीनों में कैसे समूहित करें - एसक्यूएल, एसक्यूएल-सर्वर, वाक्यविन्यास-त्रुटि

मैं महीने तक इस डेटा के परिणामों को सारांशित करने की कोशिश कर रहा हूं ... व्यक्तिगत तारीख नहीं। ऐसा मैं किस प्रकार करूं?

SELECT DISTINCT
CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)) AS Appended_SignInDate,
GO.GeographicLocationDescription,
COUNT(DISTINCT acd.EmployeeID) as Total_FTL

FROM
dbACD.Detail.vwRockwellEventAgentPerformance AS ACD

LEFT OUTER JOIN
dbEmployee.Summary.vwEmployeeHistory AS EH
ON acd.employeeID = eh.EmployeeID
AND ACD.SignOutDateTime Between eh.StartDate and eh.EndDate

LEFT OUTER JOIN
dbEmployee.Config.vwGeographicLocation AS GO
ON EH.GeographicLocationID = GO.GeographicLocationID


WHERE
ACD.SignOutDateTime BETWEEN "2012-06-01" AND "2013-03-31"
--  AND ACD.SignoutReasonCode ="4"
AND GO.GeographicLocationDescription is not null

GROUP BY

CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)),
GO.GeographicLocationDescription


ORDER BY
CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)),
GO.GeographicLocationDescription

उत्तर:

उत्तर № 1 के लिए 1

DatePart () फ़ंक्शन को इसके लिए काम करना चाहिए।

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

SELECT DISTINCT
DATEPART(month, CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101))) AS Appended_SignInDate
, GO.GeographicLocationDescription, COUNT(DISTINCT acd.EmployeeID) as Total_FTL
FROM dbACD.Detail.vwRockwellEventAgentPerformance AS ACD
LEFT OUTER JOIN dbEmployee.Summary.vwEmployeeHistory AS EH
ON acd.employeeID = eh.EmployeeID
AND ACD.SignOutDateTime Between eh.StartDate and eh.EndDate
LEFT OUTER JOIN dbEmployee.Config.vwGeographicLocation AS GO
ON EH.GeographicLocationID = GO.GeographicLocationID
WHERE ACD.SignOutDateTime BETWEEN "2012-06-01" AND "2013-03-31"
-- AND ACD.SignoutReasonCode ="4" AND GO.GeographicLocationDescription is not null
GROUP BY
DATEPART(month, CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)))
, GO.GeographicLocationDescription

ORDER BY
CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)), GO.GeographicLocationDescription

संबंधित सवाल
सबसे लोकप्रिय