Link to home
Start Free TrialLog in
Avatar of mgordon-spi
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
Avatar of plummet
plummet
Flag of United Kingdom of Great Britain and Northern Ireland image

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
ASKER CERTIFIED SOLUTION
Avatar of Rajeev
Rajeev
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mgordon-spi
mgordon-spi

ASKER

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