Share via

best way to query

PV 21 Reputation points
2020-08-20T01:57:31.053+00:00

Hi,
I have the below query.I am passing the param to SP and @month is of yyyy-MM format.Basically whenever the @month is any month other than quarter end month,i calculate value from previous quarter end else i calculate the value for quarter end month.Example:
for @month=2020-07,calculate value from case_month=2020-06
for @month=2020-08,calculate value from case_month=2020-06
for @month=2020-09,calculate value from case_month=2020-09

select 'Failure Rate' as KPIname,case when @month in
(format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @Case _date) + 1, 0)) ,'yyyy-MM')) then
(select round(cast(OnTime as float)/cast(Ontime+NotOnTime+PastDue as float)*100,2)
from Cust_vw a where a.case_month= (format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @Case _date) + 1, 0)) ,'yyyy-MM')))
else (select round(cast(OnTime as float)/cast(Ontime+NotOnTime+PastDue as float)*100,2)
from Cust_vw a where a.case_month= (format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @ADMIN _metric_date) , 0)) ,'yyyy-MM')) )
end as 'value'

I want to use the same case conditions to return the products based on the @month values.Requirement:

  • case when @month in
    (format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @Case _date) + 1, 0)) ,'yyyy-MM')) then select all products for last month of previous quarter
    Example : 2020-07 as @month,choose all products for case_month=2020-06
  • case when @month not in
    (format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @Case _date) + 1, 0)) ,'yyyy-MM')) then select all products for @month
    Example : 2020-09 as @month,choose all products for case_month=2020-09

Sample data of Cust_vw:
18964-image.png
18972-image.png

Expected sample with 2 columns namely KPIName as 'Failure Rate' and productmodel per row.
18955-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,581 Reputation points
    2020-08-20T07:24:36.567+00:00

    Hi PV-3831,

    According to your description, I wrote the following code,please refer to : 

    select  'Failure Rate' as KPIname,
case when @month in
(format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @case_date) + 1, 0)) ,'yyyy-MM')) then
(select productmodel
from Cust_vw a where a.case_month= (format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @case_date) + 1, 0)) ,'yyyy-MM')))
else (select productmodel
from Cust_vw a where a.case_month= (format(dateadd(d, -1, DATEADD(q, DATEDIFF(q, 0, @csi_metric_date) , 0)) ,'yyyy-MM')) )
end as 'productmodel'

    Best Regards
    Echo

  2. Guoxiong 8,201 Reputation points
    2020-08-20T18:11:33.2+00:00

    I think you can build both report start date and end date based on the value of the parameter @month: 

    DECLARE @month varchar(10);
DECLARE @reportStartDate date;
DECLARE @reportEndDate date;
SET @month = '2020-07';
--SET @month = '2020-09';
SET @reportStartDate = CAST (
 CASE 
 WHEN RIGHT(@month, 2) IN ('01', '02') THEN CAST(LEFT(@month, 4) - 1 AS char(4)) + '-12-01' 
 WHEN RIGHT(@month, 2) IN ('03', '04', '05') THEN CAST(LEFT(@month, 4) AS char(4)) + '-03-01'
 WHEN RIGHT(@month, 2) IN ('06', '07', '08') THEN CAST(LEFT(@month, 4) AS char(4)) + '-06-01'
 WHEN RIGHT(@month, 2) IN ('09', '10', '11') THEN CAST(LEFT(@month, 4) AS char(4)) + '-09-01'
 WHEN RIGHT(@month, 2) IN ('12') THEN @month + '-01'
 END
 AS date
)
SET @reportEndDate = DATEADD(MONTH, 1, @reportStartDate);
SELECT @reportStartDate, @reportEndDate;

    In the WHERE clause, you can use the date column >= @reportStartDate AND < @reportEndDate.

    0 comments