SQL Query Date Range

mgordon-spi
mgordon-spi used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You should be using DATEADD to subtract days from a date, there's some help on that here:
http://www.w3schools.com/sql/func_dateadd.asp

Hope that helps
The between clause is expecting the lower value to come first and then the higher valuer.
So, if you say
where dtCallDate between @datecol-30 and @datecol -59

Open in new window

It will create the first value bigger than the second value and it will not work. You need to reverse it.
Also, the JOIN you have used is called equi-join. That means only the matching rows should be returned. That is the reason if any query doesn't return the data then the whole query will not return anything.
I have changed your code on the basis of above two aspects. Try running it:
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 -59 and  @datecol-30 
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
left outer Join Qry2 on Qry1.sGroupName = Qry2.sGroupName
left outer Join Qry3 on Qry1.sGroupName = Qry3.sGroupName
left outer Join Qry4 on Qry1.sGroupName = Qry4.sGroupName
left outer Join Qry5 on Qry1.sGroupName = Qry5.sGroupName
left outer Join Qry6 on Qry1.sGroupName = qry6.sGroupName

Open in new window

Author

Commented:
Thank you very much rajeevnandanmishra, that now works exactly how I need it to, I really appreciate it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial