What sql server version are you using? Is the list of chargeType+RptCode fixed (4 items as in your example) or dynamic?


>Hope someone can help me modify my code with this query. Currently the query returns mutliple rows for each meetingnumber, I want to summarize the data so that I have 1 row for each meetingnumber. Currently the query returns the following:
>
>

>meetingnumber Qty_Sent_To_Charge Charge_Date                                            chargeType  RptCode
>------------- ------------------ ------------------------------------------------------ ----------- ----------
>180512        1                  2005-11-01 17:18:00                                    1           PACU
>180512        35                 2005-11-01 17:19:00                                    2           PACU
>180512        1                  2005-11-01 17:18:00                                    1           OR
>180512        219                2005-11-01 17:18:00                                    2           OR
>186153        1                  2005-11-01 17:01:00                                    1           PACU
>186153        1                  2005-11-01 17:01:00                                    1           OR
>186153        185                2005-11-01 17:01:00                                    2           OR
>
>

>
>My desired result would be
>
>

>meetingnumber PACU_ChargeType1 PACU_ChargeType2    OR_ChargeType1  OR_ChargeType2
>------------- ---------------- ------------------  --------------  --------------
>180512          1                  35                 1               219
>186153          1                   0                 1               185
>

>
>My current query is below:
>
>

>select m.meetingnumber,
>cd.ChgQtySent as Qty_Sent_To_Charge,
>cd.ChargeDate as Charge_Date,
>dc.chargeType,
>dc.RptCode
>
>from meetings m inner join
>dbo.coCaseData cd on m.meetingnumber = cd.meetingnumber inner join
>dbo.coinv i on cd.LinkedID=i.invID inner join
>dbo.coDeptChg dc on i.invID=dc.InvID
>where iscompleted=1
>and isblock=0
>and m.deptID=@tiDeptID
>and cd.ChargeDate between @ltQueryFrom and @ltQueryTo
>and m.iscompleted=1
>and i.timechargeItem=1
>order by 1
>

>
>Thanks for any help.
>
>Kirk