SQL experts oka sari randi.. need help

[Doravaru]

I need to retrieve observed and expected ratio values of some patients. 

i have a table with FY and Qtr.. (andulo 2 columns unnay FY   column and Qtr column)

report create chestunna (using ssrs)... 

it has 2 parameters 

FY   Qtr

if user passes the parameters as  2017  and 3 then the report / result set should show the previous 4 qrtrs information 

result should be 

 fy        qtr      obsrv    expeted    overall

2016    3          ...         ...                     ...

2016   4           ...       ...                     ...

2017   1         ...           ..                  ...

2017    2         ...         ...              ... 

sql query ela rayali.. new to sql server .. help plz...

[Doravaru]

@BillMarkSteve 

@vivek1234

@jbourne

@IloveIndia 

@LuvIndia

@loveindia

@k2s

@RamGopalVarma

@GunturMirchiBajji

@kiran karthik

@Naaperushiva

@papacha

@Popkatapetapotapulti

@tables 

@tesla

[Doravaru]

@Tesla

@Quickgun_murugan

[Quickgun_murugan]

-- To get the Fiscal start Date
create FUNCTION GetQuarterStartDate 
(
@YearandQuarter varchar(10)
)
RETURNS Date
AS
Begin 
Declare @DateFiscal Date
Select @YearandQuarter =
Case substring(@YearandQuarter,7,7)
when 1 then 
substring(@YearandQuarter,1,4) + '-01-01'
when 2 then
substring(@YearandQuarter,1,4) + '-04-01'
when 3 then
substring(@YearandQuarter,1,4) + '-07-01'
when 4 then
substring(@YearandQuarter,1,4) + '-10-01'
end
select @DateFiscal =DATEADD(qq,DATEDIFF(qq,0, @YearandQuarter) ,0)
set @DateFiscal = Dateadd(mm, -3,@DateFiscal)
return @Datefiscal
End

-- To get the Fiscal End Date
create FUNCTION GetQuarterEndDate 
(
@YearandQuarter varchar(10)
)
RETURNS Date
AS
Begin 
Declare @Datefiscal Date
Select @YearandQuarter =
Case substring(@YearandQuarter,7,7)
when 1 then 
substring(@YearandQuarter,1,4) + '-01-01'
when 2 then
substring(@YearandQuarter,1,4) + '-04-01'
when 3 then
substring(@YearandQuarter,1,4) + '-07-01'
when 4 then
substring(@YearandQuarter,1,4) + '-10-01'
end
select @DateFiscal = DATEADD(qq,DATEDIFF(qq,-1,@YearandQuarter),-1)
set @DateFiscal = Dateadd(mm, -3,@DateFiscal)
return @Datefiscal
End

[Quickgun_murugan]

Just now, Quickgun_murugan said:

-- To get the Fiscal start Date
create FUNCTION GetQuarterStartDate 
(
@YearandQuarter varchar(10)
)
RETURNS Date
AS
Begin 
Declare @DateFiscal Date
Select @YearandQuarter =
Case substring(@YearandQuarter,7,7)
when 1 then 
substring(@YearandQuarter,1,4) + '-01-01'
when 2 then
substring(@YearandQuarter,1,4) + '-04-01'
when 3 then
substring(@YearandQuarter,1,4) + '-07-01'
when 4 then
substring(@YearandQuarter,1,4) + '-10-01'
end
select @DateFiscal =DATEADD(qq,DATEDIFF(qq,0, @YearandQuarter) ,0)
set @DateFiscal = Dateadd(mm, -3,@DateFiscal)
return @Datefiscal
End

-- To get the Fiscal End Date
create FUNCTION GetQuarterEndDate 
(
@YearandQuarter varchar(10)
)
RETURNS Date
AS
Begin 
Declare @Datefiscal Date
Select @YearandQuarter =
Case substring(@YearandQuarter,7,7)
when 1 then 
substring(@YearandQuarter,1,4) + '-01-01'
when 2 then
substring(@YearandQuarter,1,4) + '-04-01'
when 3 then
substring(@YearandQuarter,1,4) + '-07-01'
when 4 then
substring(@YearandQuarter,1,4) + '-10-01'
end
select @DateFiscal = DATEADD(qq,DATEDIFF(qq,-1,@YearandQuarter),-1)
set @DateFiscal = Dateadd(mm, -3,@DateFiscal)
return @Datefiscal
End

source: sqlcentral

[4Vikram]

3 minutes ago, Quickgun_murugan said:

source: sqlcentral

vuncle ne number @Kontekurradu ki post karo naa

[Quickgun_murugan]

@Kontekurradu

[Doravaru]

37 minutes ago, Doravaru said:

I need to retrieve observed and expected ratio values of some patients. 

i have a table with FY and Qtr.. (andulo 2 columns unnay FY   column and Qtr column)

report create chestunna (using ssrs)... 

it has 2 parameters 

FY   Qtr

if user passes the parameters as  2017  and 3 then the report / result set should show the previous 4 qrtrs information 

result should be 

 fy        qtr      obsrv    expeted    overall

2016    3          ...         ...                     ...

2016   4           ...       ...                     ...

2017   1         ...           ..                  ...

2017    2         ...         ...              ... 

sql query ela rayali.. new to sql server .. help plz...

ID    fyRpt    quarterRpt
1    2009    3
2    2009    4
3    2010    1
4    2010    2
5    2010    3
6    2010    4
7    2011    1
8    2011    2
9    2011    3
10    2011    4
11    2012    1
12    2012    2
13    2012    3
14    2012    4
15    2013    1
16    2013    2
17    2013    3
18    2013    4
19    2014    1
20    2014    2
21    2014    3
22    2014    4
23    2015    1
24    2015    2
25    2015    3
26    2015    4
27    2016    1
28    2016    2
29    2016    3
30    2016    4
31    2017    1
32    2017    2
33    2017    3

[BostonBullodu]

40 minutes ago, Doravaru said:

I need to retrieve observed and expected ratio values of some patients. 

i have a table with FY and Qtr.. (andulo 2 columns unnay FY   column and Qtr column)

report create chestunna (using ssrs)... 

it has 2 parameters 

FY   Qtr

if user passes the parameters as  2017  and 3 then the report / result set should show the previous 4 qrtrs information 

result should be 

 fy        qtr      obsrv    expeted    overall

2016    3          ...         ...                     ...

2016   4           ...       ...                     ...

2017   1         ...           ..                  ...

2017    2         ...         ...              ... 

sql query ela rayali.. new to sql server .. help plz...

cascading parameters use avuthada eh case loo 

expression rayi bro using your data set 

avasaram ayithe create separate data sets for each parameter... 

[Doravaru]

7 minutes ago, BostonBullodu said:

cascading parameters use avuthada eh case loo 

expression rayi bro using your data set 

avasaram ayithe create separate data sets for each parameter... 

konchem detail ga eyyu bro... 

just user passes FY and Qtr

I need to show the previous 4 quarters along with FY in the report..

simple undi requirement but logic telvatledu..

table lo data ilaa undi...

ID    fyRpt    quarterRpt
1    2009    3
2    2009    4
3    2010    1
4    2010    2
5    2010    3
6    2010    4
7    2011    1
8    2011    2
9    2011    3
10    2011    4
11    2012    1
12    2012    2
13    2012    3
14    2012    4
15    2013    1
16    2013    2
17    2013    3
18    2013    4
19    2014    1
20    2014    2
21    2014    3
22    2014    4
23    2015    1
24    2015    2
25    2015    3
26    2015    4
27    2016    1
28    2016    2
29    2016    3
30    2016    4
31    2017    1
32    2017    2
33    2017    3

[Quickgun_murugan]

17 minutes ago, Doravaru said:

ID    fyRpt    quarterRpt
1    2009    3
2    2009    4
3    2010    1
4    2010    2
5    2010    3
6    2010    4
7    2011    1
8    2011    2
9    2011    3
10    2011    4
11    2012    1
12    2012    2
13    2012    3
14    2012    4
15    2013    1
16    2013    2
17    2013    3
18    2013    4
19    2014    1
20    2014    2
21    2014    3
22    2014    4
23    2015    1
24    2015    2
25    2015    3
26    2015    4
27    2016    1
28    2016    2
29    2016    3
30    2016    4
31    2017    1
32    2017    2
33    2017    3

data undi kada inkendi simple... use subquery n Unionall

GO
DECLARE @fy INT = '2017',
@qtr INT = '3'
Select fy, quarter 
from tab as A
Where A.ID = (SELECT ID FROM tab as B
WHERE fy = @fy AND quarter = @qtr)-1
UNION ALL
Select fy, quarter 
from tab as A
Where A.ID = (SELECT ID FROM tab as B
WHERE fy = @fy AND quarter = @qtr)-2
UNION ALL
 -3

UNION ALL

-4
ORDER by 1,2

 

@Doravaru
 

[Doravaru]

Just now, Quickgun_murugan said:

data undi kada inkendi simple... use subquery n Unionall

GO
DECLARE @fy INT = '2017',
@qtr INT = '3'
Select fy, quarter 
from tab as A
Where A.ID = (SELECT ID FROM tab as B
WHERE fy = @fy AND quarter = @qtr)-1
UNION ALL
Select fy, quarter 
from tab as A
Where A.ID = (SELECT ID FROM tab as B
WHERE fy = @fy AND quarter = @qtr)-2
UNION ALL
 -3

UNION ALL

-4
ORDER by 1,2

 

@Doravaru
 

Thank you Sir.. it works

[4Vikram]

3 hours ago, Quickgun_murugan said:

@Kontekurradu

pampinava ledu aa number?

[Quickgun_murugan]

6 minutes ago, 4Vikram said:

pampinava ledu aa number?

Deniki bro?

[Srimantudu]

8 minutes ago, 4Vikram said:

pampinava ledu aa number?

 

2 minutes ago, Quickgun_murugan said:

Deniki bro?

froxy ki vadukuntam