calculating count with different dates
-
I'm trying to fetch count for two different criteria in one query i.e, need count based on open and closed dates by month.Can someone help in grouping this by respective dates and find out count by monthly?
Assigned count needs to be based on DateCreated and Closed count on Closed date
1. SELECT I.Status, COUNT(*) as CNT,DATENAME(month,Datecreated) as Month
FROM IM_V_Defects I
WHERE I.ProjectName = 'Corporate' and I.Status = 'Assigned' and I.DateCreated >='01/01/2017'
GROUP BY I.Status,DATENAME(month,Datecreated)
union
SELECT I.Status, COUNT(*) as CNT,DATENAME(month,Dateclosed) as Month
FROM IM_V_Defects I
WHERE I.ProjectName = 'Corporate' and I.Status = 'Closed' and I.DateClosed >= '01/01/2017'
GROUP BY I.Status,DATENAME(month,Dateclosed)
union
SELECT 'OutStanding', COUNT(*) as CNT,DATENAME(month,Datecreated) as Month
FROM IM_V_Defects I
WHERE I.ProjectName = 'Corporate' I.Status <> 'Closed' and I.DateCreated>='01/01/2017'
GROUP BY DATENAME(month,Datecreated)2 .
SELECT
DATENAME(month,I.DateCreated) as Month,
AssignedCNT = SUM (CASE WHEN I.Status = 'Assigned' THEN 1 ELSE 0 END),
ClosedCNT = SUM(CASE WHEN I.Status = 'Closed' THEN 1 ELSE 0 END),
FROM IM_V_Defects I
WHERE I.ProjectName = 'Corporate' and I.DateLastMod >='01/01/2017' and I.DateCreated >='01/01/2017'
GROUP BY DATEPART(month,I.DateCreated)
ORDER BY DATEPART(month,I.DateCreated) -
So, why exactly do you need help? Are you getting incorrect results? an error? We don't have any sample data for you, so we've got almost nothing to go on other than you asking for help. One thing that is possible with your UNION query is that because of UNION being there instead of UNION ALL, any duplication of status and count and you end up losing a record. That could be fixed by using UNION ALL instead of UNION.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Yes, actually the count which i'm getting is incorrect.i'm using below query, this gives assigned count properly since i'm grouping bases on created date but i want to group for both closed and created date in order to get assigned and closed count which i'm unable to.I'm looking someone to help to get counts based on the dates for each.
SELECT
DATENAME(month,I.DateCreated) as Month,
AssignedCNT = SUM (CASE WHEN I.Status = 'Assigned' THEN 1 ELSE 0 END),
ClosedCNT = SUM(CASE WHEN I.Status = 'Closed' THEN 1 ELSE 0 END),
FROM IM_V_Defects I
WHERE I.ProjectName = 'Corporate' I.DateLastMod >='01/01/2017' and I.DateCreated >='01/01/2017'
GROUP BY DATEPART(month,I.DateCreated),DATEName(month,I.DateCreated)
ORDER BY DATEPART(month,I.DateCreated) -
We'll need more than that information to have any idea why the counts are off. We need to know what a single record in the IM_V_Defects table represents, plus the knowledge on how you know the counts are off. It's entirely possible that what you query vs. what you can see in some application may be operating on different criteria, so it's essential that you know that the query criteria and the application criteria are 100% identical. Ideally, provide some sample data that demonstrates the problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Hello. Below is the resultset of the query.Hope someone can help in getting proper counts.I'm trying to get the monthly counts based on dates for the year 2016 and 2017 i.e,
1. AssignedCNT based on Date Created and Status is Assigned
2. CNT based on Closed Date and Status is Closed
3. Outstanding CNT based on status not equal to Closed
-
Unfortunately, just having the results of the query doesn't really tell us where the problem is. Your query looks only at data for 2017 based on your WHERE clause, but your last post says 2016 and 2017. If you're going to mix years, then your GROUP BY has to include the year as well as the month. Also, you still haven't specified exactly what values you should be seeing. Finally, and possibly most importantly, you're looking at counts based on disparate meanings. A "CLOSED" defect is defined as being included in the month it was closed, but an "ASSIGNED" defect in the month that it was created, and you didn't define "OUTSTANDING" as to what date to use. However, your query uses DateCreated exclusively. In order to get the query to match your written definition, you're going to need to use a CASE statement in your GROUP BY that addresses when a given defect is in a given group.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply