Dear Tem,
my sample query is below----
i would like the sum of ([ADT]+[CHD])in the seprate column and sum of [INF] in other column
you will find the above column in the last section of the query i m using pivot
sample o/p of the query isin the bottom
Declare @FL_DATE_FROM INT,
@FL_DATE_TO INT,
@MYDATE DATETIME
SELECT @MYDATE = GETDATE()
SELECT @FL_DATE_FROM = CONVERT(VARCHAR(10),DATEADD(MONTH,DATEDIFF(MONTH,0,@MYDATE)-1,0),112)----PREVIOUS MONTH'S FIRST DAY
SELECT @FL_DATE_TO= CONVERT(VARCHAR(10),DATEADD(DAY,-(DAY(@MYDATE)),@MYDATE),112)--PREVIOUS MONTH'S LAST DAY
SELECT * FROM (SELECT FL_DATE=CONVERT(VARCHAR,CONVERT(DATETIME,STR(t1.FL_DATE)),105),[FLT NO]=t1.FL_NO,Frm=t1.FL_FROM,[To]=t1.FL_TO,
--Flown=(case when FL_DTTI_GATE_CLOSED is null then 'no' else 'YES' end),
DC_BP_ID,PAX_TYPE_CODE FROM T_DC_BOARDING_PASS t1
join AIRS_U_TJ.DBO.T_FL_FLIGHT_availability t2 (nolock) on t1.FL_DATE=t2.fl_date and
t1.FL_NO=t2.fl_no and t1.FL_FROM=t2.FL_FROM and t1.FL_to=t2.FL_to
WHERE t1.FL_DATE BETWEEN @FL_DATE_FROM AND @FL_DATE_TO
and ISNULL(bp_cancelled,0)=0
and fl_Status_id<>3
) AS A
PIVOT (COUNT (DC_BP_ID) FOR PAX_TYPE_CODE IN ([ADT],[CHD],[INF]))AS SEG_COUNT
ORDER BY FL_DATE
sample o/p--
| FL_DATE | FLT NO | Frm | To | ADT | CHD | INF |
|---|---|---|---|---|---|---|
| 01-11-2018 | 2T101 | HYD | TIR | 60 | 4 | 3 |
| 01-11-2018 | 2T102 | TIR | HYD | 52 | 4 | 1 |
| 01-11-2018 | 2T107 | HYD | TIR | 64 | 3 | 1 |
| 01-11-2018 | 2T108 | TIR | HYD | 44 | 1 | 2 |
| 01-11-2018 | 2T121 | HYD | GOI | 51 | 0 | 0 |
| 01-11-2018 | 2T122 | GOI | HYD | 63 | 2 | 1 |
| 01-11-2018 | 2T131 | HYD | VGA | 56 | 2 | 0 |
please help
