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
voucher txn_currency gross_amt prcnt_wthhld co_dscto CO_DSCTO_DETRAC CO_DSCTO_OTROS CO_A_PAGAR
SCE04753 PEN 3570 6 134,01 0 0 1791,96
total 3570
co_dscto 134,01
CO_DSCTO_DETRAC 0
CO_DSCTO_OTROS 0
CO_A_PAGAR 1791,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