Data between 2 dates (different years)
-
Hello Everyone,
I have a procedure which gives a result set of months with some figures. The procedure takes 2 parameters @emp_id and @year.
DECLARE @emp_id INT= 22;
DECLARE @year VARCHAR= '2020';
WITH TBL1
AS (SELECT CAST('2020-01-01' AS DATETIME) AS MONTH_NAME
UNION ALL
SELECT DATEADD(MONTH, 1, MONTH_NAME)
FROM TBL1
WHERE DATEPART(MONTH, MONTH_NAME) < 12),
imprestdata
AS (SELECT imp_id,
(DATENAME(MONTH, DATEADD(MONTH, DATEPART(MONTH, imp_fin_appr_date), -1))) AS MONTH_NAME,
DATEPART(MONTH, imp_fin_appr_date) AS Mon,
ISNULL(SUM(imp_amount_approved_by_finance), 0) AS imprest
FROM tbl_emp_imprest
WHERE imp_fin_appr_date BETWEEN '' + @year + '-01-01 00:00:00.000' AND '' + @year + '-12-31 23:59:59.000'
AND imp_id = @emp_id
GROUP BY imp_id,
DATEPART(MONTH, imp_fin_appr_date)),
expendituredata
AS (SELECT emp_id,
(DATENAME(MONTH, DATEADD(MONTH, DATEPART(MONTH, audit_date), -1))) AS MONTH_NAME,
DATEPART(MONTH, audit_date) AS Mon,
ISNULL(SUM(audit_amount), 0) AS expenditure
FROM tblExpenditure
WHERE audit_appr = 1
AND emp_id = @emp_id
AND audit_date BETWEEN '' + @year + '-01-01 00:00:00.000' AND '' + @year + '-12-31 23:59:59.000'
GROUP BY emp_id,
DATEPART(MONTH, audit_date))
SELECT DATENAME(MONTH, TBL1.MONTH_NAME) AS monthname,
ISNULL(imprestdata.imprest, 0) AS imprest,
ISNULL(expendituredata.expenditure, 0) AS expenditure
FROM TBL1
LEFT JOIN imprestdata ON imprestdata.MONTH_NAME = DATENAME(MONTH, TBL1.MONTH_NAME)
LEFT JOIN expendituredata ON expendituredata.emp_id = imprestdata.imp_id
AND expendituredata.Mon = imprestdata.Mon;And the output and the result set is as below.
Now I need to give here 5 parameters @emp_id , @star_year, @start_month, @end_year and @end_month.
So that I can get data between tow dates i.e 2020(@star_year) - February(@start_month) to 2021(@end_year) - January(@end_month)
Could you please suggest the solution?
Thanks
-
Is it me or is all you have to do is change the following:
BETWEEN '' + @year + '-01-01 00:00:00.000' AND '' + @year + '-12-31 23:59:59.000'
to --> replacing the static months in string and both years
BETWEEN '' + @start_year +'-'+ @start_month+ '-01 00:00:00.000' AND '' + @end_year +'-'+ @end_month+ '-31 23:59:59.000'
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
ktflash, afaik it's almost a correct "quick fix". TBL1 is a recursive CTE which generates a sequence of months (1 to 12). Presumably this date range is to be adjusted when start month and end month are added to the inputs.
While a "quick fixes" make total sense in an operational role, here on the intertube we're supposed to be proposing the better ways. Shoe-horning '23:59:59' into BETWEEN to make datetime comparisons work (although very, very tempting) is not really ideal. If best practices don't get put forward right now then not good practices get carried forward. Or that's what I've been told 🙂
If it were me doing this I would split the dates into year and month columns because this type of query is 99% destined for a pivottable in a spreadsheet. So rather than get hit with a follow up request 10 minutes later for a "yearly rollup" I would split it so they could do it themselves.
The rCTE could be replaced by a tally function. Something like this
declare @emp_id int= 22;
declare @start_yr int= 2020;
declare @start_mo int= 3;
declare @end_yr int= 2020;
declare @end_mo int= 9;
with
dt_cte(dt) as (
select dateadd(month, fn.n, dfp.start_dt)
from (values (datefromparts(@start_yr, @start_mo, 1),
datefromparts(@end_yr, @end_mo, 1))) dfp(start_dt, end_dt)
cross apply dbo.fnTally(0, datediff(month, dfp.start_dt, dfp.end_dt)) fn),
imp_cte(imp_id, dt, imprest) as (
select imp_id, dfp.dt, sum(imp_amount_approved_by_finance)
from tbl_emp_imprest
cross apply (values (datefromparts(year(imp_fin_appr_date),
month(imp_fin_appr_date), 1)) dfp(start_dt)
where imp_id = @emp_id
and imp_fin_appr_date>=cast(datefromparts(@start_yr, @start_mo, 1) as datetime)
and imp_fin_appr_date<cast(dateadd(day, 1, eomonth(datefromparts(@end_yr, @end_mo, 1)) as datetime)
group by imp_id, dfp.dt),
exp_cte(emp_id, dt, expenditure) as (
select emp_id, dfp.dt, sum(audit_amount)
from tblexpenditure
cross apply (values (datefromparts(year(imp_fin_appr_date),
month(imp_fin_appr_date), 1)) dfp(start_dt)
where emp_id = @emp_id
and audit_appr = 1
and audit_date>=cast(datefromparts(@start_yr, @start_mo, 1) as datetime)
and audit_date<cast(dateadd(day, 1, eomonth(datefromparts(@end_yr, @end_mo, 1)) as datetime)
group by emp_id, dfp.dt)
select d.dt, datename(month, d.dt) as month_name,
isnull(i.imprest, 0) as imprest,
isnull(e.expenditure, 0) as expenditure
from dt_cte d
left join imp_cte i on d.dt=i.dt
left join exp_cte e on d.dt=e.dt;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
shows error
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'dfp'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'dfp'.
what is dfp(start_dt) ?
-
dfp(start_dt) is (or was intended to be) the alias (with column label) for the object defined by the VALUES table value constructor. It appears there some were missing parentheses in a few places. Here's an update. Obviously, debugging is greatly facilitated by sample data. Without any it's assumed you're looking for a "best guess" attempt.
with
dt_cte(dt) as (
select dateadd(month, fn.n, dfp.start_dt)
from (values (datefromparts(@start_yr, @start_mo, 1),
datefromparts(@end_yr, @end_mo, 1))) dfp(start_dt, end_dt)
cross apply dbo.fnTally(0, datediff(month, dfp.start_dt, dfp.end_dt)) fn),
imp_cte(imp_id, dt, imprest) as (
select imp_id, dfp.dt, sum(imp_amount_approved_by_finance)
from tbl_emp_imprest
cross apply (values (datefromparts(year(imp_fin_appr_date),
month(imp_fin_appr_date), 1))) dfp(start_dt)
where imp_id = @emp_id
and imp_fin_appr_date>=cast(datefromparts(@start_yr, @start_mo, 1) as datetime)
and imp_fin_appr_date<cast(dateadd(day, 1, eomonth(datefromparts(@end_yr, @end_mo, 1))) as datetime)
group by imp_id, dfp.dt),
exp_cte(emp_kd, dt, expenditure) as (
select emp_id, dfp.dt, sum(audit_amount)
from tblexpenditure
cross apply (values (datefromparts(year(imp_fin_appr_date),
month(imp_fin_appr_date), 1))) dfp(start_dt)
where emp_id = @emp_id
and audit_appr = 1
and audit_date>=cast(datefromparts(@start_yr, @start_mo, 1) as datetime)
and audit_date<cast(dateadd(day, 1, eomonth(datefromparts(@end_yr, @end_mo, 1))) as datetime)
group by emp_id, dfp.dt)
select d.dt, datename(month, d.dt) as month_name,
isnull(i.imprest, 0) as imprest,
isnull(e.expenditure, 0) as expenditure
from dt_cte d
left join imp_cte i on d.dt=i.dt
left join exp_cte e on d.dt=e.dt;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thank you Steve Collins for replying
it now shows error
Msg 207, Level 16, State 1, Line 21
Invalid column name 'dt'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'dt'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'dt'.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'dt'.
Please suggest.
-
It seems dfp(start_dt) should've been dfp(dt) in two places. This is a really slow way to debug 🙂
with
dt_cte(dt) as (
select dateadd(month, fn.n, dfp.start_dt)
from (values (datefromparts(@start_yr, @start_mo, 1),
datefromparts(@end_yr, @end_mo, 1))) dfp(start_dt, end_dt)
cross apply dbo.fnTally(0, datediff(month, dfp.start_dt, dfp.end_dt)) fn),
imp_cte(imp_id, dt, imprest) as (
select imp_id, dfp.dt, sum(imp_amount_approved_by_finance)
from tbl_emp_imprest
cross apply (values (datefromparts(year(imp_fin_appr_date),
month(imp_fin_appr_date), 1))) dfp(dt)
where imp_id = @emp_id
and imp_fin_appr_date>=cast(datefromparts(@start_yr, @start_mo, 1) as datetime)
and imp_fin_appr_date<cast(dateadd(day, 1, eomonth(datefromparts(@end_yr, @end_mo, 1))) as datetime)
group by imp_id, dfp.dt),
exp_cte(emp_id, dt, expenditure) as (
select emp_id, dfp.dt, sum(audit_amount)
from tblexpenditure
cross apply (values (datefromparts(year(imp_fin_appr_date),
month(imp_fin_appr_date), 1))) dfp(dt)
where emp_id = @emp_id
and audit_appr = 1
and audit_date>=cast(datefromparts(@start_yr, @start_mo, 1) as datetime)
and audit_date<cast(dateadd(day, 1, eomonth(datefromparts(@end_yr, @end_mo, 1))) as datetime)
group by emp_id, dfp.dt)
select d.dt, datename(month, d.dt) as month_name,
isnull(i.imprest, 0) as imprest,
isnull(e.expenditure, 0) as expenditure
from dt_cte d
left join imp_cte i on d.dt=i.dt
left join exp_cte e on d.dt=e.dt;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thank you @steve-2 Collins
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply