January 10, 2012 at 2:39 pm
Hi All, i have this data:
declare @vartable TABLE(
PAY_CYCLE varchar(50),
PAY_CYCLE_SEQ_NUM INT,
VOUCHER_ID varchar(50),
VENDOR_ID FLOAT,
NAME1 varchar(150),
TXN_CURRENCY_CD VARCHAR(50),
GROSS_AMT FLOAT,
PYMNT_GROSS_AMT FLOAT,
PRCNT_WTHHLD FLOAT,
CO_DSCTO_RETEN FLOAT,
CO_DSCTO_DETRAC FLOAT,
CO_DSCTO_OTROS FLOAT,
CO_A_PAGAR FLOAT,
PYMNT_RATE_MULT varchar(50),
PYMNT_RATE_DIV varchar(50),
EXPR varchar(50),
VOUCHER_ID2 varchar(50),
PYMNT_SEPARATE varchar(50),
DOC_TYPE varchar(50)
)
INSERT INTO @tabla
SELECT 'PTSOL',38,'SCE04753',0000000015,'CATERPILLAR LEASING CHILE SA','PEN',3570.000,1791.960,6.00,134.010,0.000,0.000,1791.960,1.00000000,1.00000000,'0000000015','SCE04753','N','01' UNION ALL
SELECT 'PTSOL',38,'SCE04900',0000008798,'VCI CONSTRUYE S.A.C.','PEN',1180.000,1038.000,12.00,0.000,142.000,0.000,1038.000,1.00000000,1.00000000,'0000008798','SCE04900','N','01' UNION ALL
SELECT 'PTSOL',38,'SCE04901',0000008798,'VCI CONSTRUYE S.A.C.','PEN',1180.000,988.000,12.00,0.000,142.000,50.000,988.000,1.00000000,1.00000000,'0000008798','SCE04901','N','01' UNION ALL
SELECT 'PTSOL',38,'SCE03132',0000010035,'S.A DE COMERCIO Y SERV. DE INGENIERIA','PEN',214.200,214.200,0.00,0.000,0.000,0.000,214.200,1.00000000,1.00000000,'0000010035SCE03132','SCE03132','Y','01' UNION ALL
SELECT 'PTSOL',38,'SCE04751',0000010035,'S.A DE COMERCIO Y SERV. DE INGENIERIA','PEN',11800.000,5059.930,6.00,322.970,0.000,0.000,5059.930,1.00000000,1.00000000,'0000010035','SCE04751','N','01' UNION ALL
SELECT 'PTSOL',38,'SCE04899',0000010035,'S.A DE COMERCIO Y SERV. DE INGENIERIA','PEN',1000.000,780.750,6.00,49.830,0.000,0.000,780.750,1.00000000,1.00000000,'0000010035SCE04899','SCE04899','Y','AP_PAGOS'
select * from @vartable
vendor_id <voucher_value>caterpillar leasing chile
vouchertxn_currencygross_amtprcnt_wthhldco_dsctoCO_DSCTO_DETRACCO_DSCTO_OTROSCO_A_PAGAR
SCE04753PEN35706134,01001791,96
total3570
co_dscto134,01
CO_DSCTO_DETRAC0
CO_DSCTO_OTROS0
CO_A_PAGAR1791,96
In the report data is grouped by vendor_id and then the voucher_id
conditions exist, if the voucher value is 'N' go together on the same sheet and the total sum, and when they are separated with Y .. (not in the same block)
Any recommendations PLEASE!
like that: http://postimage.org/image/41h6h6k1j/
____________________________________________________________________________
Rafo*
January 10, 2012 at 2:59 pm
Very nice sample data but the expected result is anything but clear.
Can you post in a different way what you're looking for? (maybe even attaching a jpeg)
And if the expected result is not obvious, please explain any special rules that may apply.
January 10, 2012 at 3:12 pm
Hi, thanks
the rules are, just when the voucher_id='N' (i mean pymnt_separate column) are going toghether and the co_a_pagar joins in the totals final of the group by.
if the voucher_id='Y' (pymnt_separate) are separate
____________________________________________________________________________
Rafo*
January 10, 2012 at 3:22 pm
still not clear. Please provide the expected output in a readable format (not mangled by the code mystiprettifier)
January 10, 2012 at 3:33 pm
January 10, 2012 at 3:47 pm
Why does the result set only show data for vendor_id = 15?
It looks like the second result set is either a repetition of previous columns or an aggregation. In either way this should be done at the presentation layer based on the data returned for the first result set. Otherwise you'd have to query the data twice.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply