mgordon-spi
asked on
SQL Query Date Range
Below I have Multiple Queries to return counts per group under different where clauses.
Where dtCallDate between @datecol-30 and @datecol needs to search for records between 30 to 59 days old. But if I add -59 to the second property then the entire Statement returns 0 rows
Also if any of the SELECTS return nothing then they all will return 0
Declare @WeekNum int = 15
DECLARE @datecol datetime = GETDATE();
DECLARE @YearNum char(4);
SELECT @YearNum = CAST(DATEPART(YY, @datecol) AS CHAR(4));
SET NOCOUNT ON;
-- Insert statements for procedure here
With Qry1 as (
SELECT tGroups.sGroupName, count(*) row_cnt
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between dbo.GetWeekFirst(@WeekNum - 4, @YearNum) AND dbo.GetWeekLast(@WeekNum, @YearNum)
Group by tGroups.sGroupName),
Qry2 as (
SELECT tGroups.sGroupName, count(*) row_cnt2
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where iStatus in (0, 1)
Group by tGroups.sGroupName),
Qry3 as (
SELECT tGroups.sGroupName, count(*) row_cnt3
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between @datecol-30 and @datecol
Group by tGroups.sGroupName),
Qry4 as (
SELECT tGroups.sGroupName, count(*) row_cnt4
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between @datecol-60 and @datecol
Group by tGroups.sGroupName),
Qry5 as (
SELECT tGroups.sGroupName, count(*) row_cnt5
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between @datecol-90 and @datecol
Group by tGroups.sGroupName),
Qry6 as (
SELECT tGroups.sGroupName, count(*) row_cnt6
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where iStatus in (2) AND dtEnquiryCompleted between dbo.GetWeekFirst(@WeekNum - 4, @YearNum) AND dbo.GetWeekLast(@WeekNum, @YearNum)
Group by tGroups.sGroupName)
Select
Qry1.sGroupName , Qry1.Row_Cnt as 'NewJobs', row_cnt2 as 'TotalOutstanding', row_cnt3 as '30Days', row_cnt4 as '60Days', row_cnt5 as '90Days',
row_cnt6 as 'JobsCompleted'
From Qry1
Join Qry2 on Qry1.sGroupName = Qry2.sGroupName
Join Qry3 on Qry1.sGroupName = Qry3.sGroupName
Join Qry4 on Qry1.sGroupName = Qry4.sGroupName
Join Qry5 on Qry1.sGroupName = Qry5.sGroupName
Join Qry6 on Qry1.sGroupName = qry6.sGroupName
Where dtCallDate between @datecol-30 and @datecol needs to search for records between 30 to 59 days old. But if I add -59 to the second property then the entire Statement returns 0 rows
Also if any of the SELECTS return nothing then they all will return 0
Declare @WeekNum int = 15
DECLARE @datecol datetime = GETDATE();
DECLARE @YearNum char(4);
SELECT @YearNum = CAST(DATEPART(YY, @datecol) AS CHAR(4));
SET NOCOUNT ON;
-- Insert statements for procedure here
With Qry1 as (
SELECT tGroups.sGroupName, count(*) row_cnt
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between dbo.GetWeekFirst(@WeekNum - 4, @YearNum) AND dbo.GetWeekLast(@WeekNum, @YearNum)
Group by tGroups.sGroupName),
Qry2 as (
SELECT tGroups.sGroupName, count(*) row_cnt2
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where iStatus in (0, 1)
Group by tGroups.sGroupName),
Qry3 as (
SELECT tGroups.sGroupName, count(*) row_cnt3
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between @datecol-30 and @datecol
Group by tGroups.sGroupName),
Qry4 as (
SELECT tGroups.sGroupName, count(*) row_cnt4
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between @datecol-60 and @datecol
Group by tGroups.sGroupName),
Qry5 as (
SELECT tGroups.sGroupName, count(*) row_cnt5
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where dtCallDate between @datecol-90 and @datecol
Group by tGroups.sGroupName),
Qry6 as (
SELECT tGroups.sGroupName, count(*) row_cnt6
FROM tEnquiry INNER JOIN
tCustomers ON tEnquiry.iCustomerID = tCustomers.ID INNER JOIN
tGroups ON tCustomers.iGroupID = tGroups.ID
Where iStatus in (2) AND dtEnquiryCompleted between dbo.GetWeekFirst(@WeekNum - 4, @YearNum) AND dbo.GetWeekLast(@WeekNum, @YearNum)
Group by tGroups.sGroupName)
Select
Qry1.sGroupName , Qry1.Row_Cnt as 'NewJobs', row_cnt2 as 'TotalOutstanding', row_cnt3 as '30Days', row_cnt4 as '60Days', row_cnt5 as '90Days',
row_cnt6 as 'JobsCompleted'
From Qry1
Join Qry2 on Qry1.sGroupName = Qry2.sGroupName
Join Qry3 on Qry1.sGroupName = Qry3.sGroupName
Join Qry4 on Qry1.sGroupName = Qry4.sGroupName
Join Qry5 on Qry1.sGroupName = Qry5.sGroupName
Join Qry6 on Qry1.sGroupName = qry6.sGroupName
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much rajeevnandanmishra, that now works exactly how I need it to, I really appreciate it.
http://www.w3schools.com/sql/func_dateadd.asp
Hope that helps