Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waterduck
Aged Yak Warrior
982 Posts |
 Posted - 2009-08-07 : 02:36:10
|
[code]SELECT t.company, t.do_date, t.trx_type, CASE WHEN (CASE WHEN h.in_no IS NULL then t.trnx_ref ELSE h.in_no END) IS NOT NULL then (CASE WHEN h.in_no IS NULL then t.trnx_ref ELSE h.in_no END) else (CASE WHEN h.do_no IS NULL then t.trnx_ref ELSE h.do_no END) END reference, t.st_code, m.st_desc, CASE WHEN t.trx_type = 'CN' then t.quantity ELSE 0-t.qtt_out END quantity, h.forex_code, t.unit_price, CASE WHEN t.trx_type = 'CN' then 0-t.total_price ELSE t.total_price END subtotalFROM st_trx t join customer c on t.company=c.cm_cust_no join (SELECT st_code, st_desc FROM st_mast WHERE (@ds = 'a' or @ds = 'i') and (st_code >= @startcode and st_code <= @endcode) UNION SELECT st_code, st_desc FROM st_msuspend WHERE (@ds = 'o' or @ds = 'i') and (st_code >= @startcode and st_code <= @endcode))m on t.st_code=m.st_code left join st_head h on h.trx_type=t.trx_type and t.trnx_ref=(CASE WHEN t.trx_type IN ('DO','SO','SOX')then h.do_no else h.in_no END) WHERE (t.do_date >= @startdate and t.do_date <= @enddate) and (t.trx_type NOT IN ('PDO','GRO','ADJ')) and (cm_cust_no >= 'A001' and cm_cust_no <= 'AAA')[/code]How do i make a running total...Below is what i tried and it did not running the total accordingly..[code]WITH CTEAS( SELECT row_number() over (order by t.company, t.do_date, t.st_code)sqno, t.company, t.do_date, t.trx_type, CASE WHEN (CASE WHEN h.in_no IS NULL then t.trnx_ref ELSE h.in_no END) IS NOT NULL then (CASE WHEN h.in_no IS NULL then t.trnx_ref ELSE h.in_no END) else (CASE WHEN h.do_no IS NULL then t.trnx_ref ELSE h.do_no END) END reference, t.st_code, m.st_desc, CASE WHEN t.trx_type = 'CN' then t.quantity ELSE 0-t.qtt_out END quantity, h.forex_code, t.unit_price, CASE WHEN t.trx_type = 'CN' then 0-t.total_price ELSE t.total_price END subtotal FROM st_trx t join customer c on t.company=c.cm_cust_no join (SELECT st_code, st_desc FROM st_mast WHERE (@ds = 'a' or @ds = 'i') and (st_code >= @startcode and st_code <= @endcode) UNION SELECT st_code, st_desc FROM st_msuspend WHERE (@ds = 'o' or @ds = 'i') and (st_code >= @startcode and st_code <= @endcode))m on t.st_code=m.st_code left join st_head h on h.trx_type=t.trx_type and t.trnx_ref=(CASE WHEN t.trx_type IN ('DO','SO','SOX')then h.do_no else h.in_no END) WHERE (t.do_date >= @startdate and t.do_date <= @enddate) and (t.trx_type NOT IN ('PDO','GRO','ADJ')) and (cm_cust_no >= @startcust and cm_cust_no <= @endcust))SELECT company, do_date, trx_type, reference, st_code, st_desc, quantity, forex_code, unit_price, subtotal, (SELECT SUM(subtotal) FROM CTE two WHERE two.sqno<=one.sqno and two.company=one.company)runningtotalFROM CTE one[/code]
Hope can help...but advise to wait pros with confirmation... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-07 : 02:55:27
|
you want the running total by company or continuous for all result ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 02:57:03
|
woops, i wanna run by company...but i use CTE it become very slow...plus i have no idea what happen to my cte...doesn't sure why it didn't run accordingly with subtotal...
Hope can help...but advise to wait pros with confirmation... |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 03:01:15
|
some record by my running total querysqno company do_date trx_type reference st_code st_desc quantity forex_code unit_price subtotal runningtotal-------------------- -------------------- ----------------------- -------- -------------------- ------------------------- --------------------------------------------- --------------------------------------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------1 A001 2009-01-21 00:00:00.000 DO BQ-5150C BQ-5150CFDFDFZD -20.0000 MAL 25.5000 510.0000 3060.00002 A001 2009-01-21 00:00:00.000 INV LCF-00453 BQ-5150C BQ-5150CFDFDFZD -40.0000 NULL 25.5000 1020.0000 3570.00003 A001 2009-01-21 00:00:00.000 INV LCF-00454 BQ-5150C BQ-5150CFDFDFZD -5.0000 NULL 25.5000 127.5000 8670.00004 A001 2009-01-21 00:00:00.000 INV LCF-00455 BQ-5150C BQ-5150CFDFDFZD -1.0000 NULL 25.5000 25.5000 13770.00005 A001 2009-01-21 00:00:00.000 INV LCF-00456 BQ-5150C BQ-5150CFDFDFZD -4.0000 NULL 25.5000 102.0000 14790.00006 A001 2009-01-21 00:00:00.000 INV LCF-00457 BQ-5150C BQ-5150CFDFDFZD -2.0000 NULL 25.5000 51.0000 14917.50007 A001 2009-01-21 00:00:00.000 DO INV01068 BQ-5150C BQ-5150CFDFDFZD -120.0000 MAL 25.5000 3060.0000 14943.00008 A001 2009-01-21 00:00:00.000 DOL DOL-090121 BQ-5150C BQ-5150CFDFDFZD -8.0000 NULL 25.5000 5100.0000 15045.00009 A001 2009-01-21 00:00:00.000 DOL DOL-090121 BQ-5150C BQ-5150CFDFDFZD 0.0000 NULL 25.5000 5100.0000 15096.000010 A001 2009-01-21 00:00:00.000 DOL DOL-090121 K-CDRW482448 PRODUCT1497 -8.0000 NULL 146.0000 14600.0000 20936.000011 A001 2009-01-21 00:00:00.000 DOL DOL-090121 K-CDRW482448 PRODUCT1497 0.0000 NULL 146.0000 14600.0000 21228.000012 A001 2009-01-21 00:00:00.000 INV LCF-00453 K-CDRW482448 PRODUCT1497 -30.0000 NULL 146.0000 4380.0000 21812.000013 A001 2009-01-21 00:00:00.000 INV LCF-00454 K-CDRW482448 PRODUCT1497 -5.0000 NULL 146.0000 730.0000 21958.000014 A001 2009-01-21 00:00:00.000 INV LCF-00455 K-CDRW482448 PRODUCT1497 -1.0000 NULL 146.0000 146.0000 22688.000015 A001 2009-01-21 00:00:00.000 DO INV01068 K-CDRW482448 PRODUCT1497 -40.0000 MAL 146.0000 5840.0000 27068.000016 A001 2009-01-21 00:00:00.000 INV LCF-00456 K-CDRW482448 PRODUCT1497 -4.0000 NULL 146.0000 584.0000 41668.000017 A001 2009-01-21 00:00:00.000 INV LCF-00457 K-CDRW482448 PRODUCT1497 -2.0000 NULL 146.0000 292.0000 56268.000018 A001 2009-01-21 00:00:00.000 DO K-CDRW482448 PRODUCT1497 -10.0000 MAL 146.0000 1460.0000 57728.0000
Hope can help...but advise to wait pros with confirmation... |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 04:11:49
|
sifu....peso....madhi....webby... where u guys go...T.T
Hope can help...but advise to wait pros with confirmation... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-07 : 04:16:41
|
the result you posted does not make sense. Your query should not generate such resultline 1, sqno = 1, the running total is 3060, seems like it subtotal for sqno 7. Can you re-run your CTE query and update both your query and the result generated here ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 04:41:12
|
i also notice that...i run few time and it post totally same result...so i really cant use this query, however i test with sample data, there is no problem with the cte format...btw the query used 53 second to compile!!!!
Hope can help...but advise to wait pros with confirmation... |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 04:47:33
|
[code]DECLARE @startcode VARCHAR SET @startcode = ''DECLARE @endcode VARCHAR SET @endcode = 'zzzzzzzzzzz'DECLARE @ds CHAR(1) SET @ds = 'a'DECLARE @startdate DATETIME SET @startdate = '20090101'DECLARE @enddate DATETIME SET @enddate = '20091231';WITH CTEAS( SELECT row_number() over (order by t.company, t.do_date, t.st_code)sqno, t.company, t.do_date, t.trx_type, CASE WHEN (CASE WHEN h.in_no IS NULL then t.trnx_ref ELSE h.in_no END) IS NOT NULL then (CASE WHEN h.in_no IS NULL then t.trnx_ref ELSE h.in_no END) else (CASE WHEN h.do_no IS NULL then t.trnx_ref ELSE h.do_no END) END reference, t.st_code, m.st_desc, CASE WHEN t.trx_type = 'CN' then t.quantity ELSE 0-t.qtt_out END quantity, h.forex_code, t.unit_price, CASE WHEN t.trx_type = 'CN' then 0-t.total_price ELSE t.total_price END subtotal FROM st_trx t join customer c on t.company=c.cm_cust_no join (SELECT st_code, st_desc FROM st_mast WHERE (@ds = 'a' or @ds = 'i') and (st_code >= @startcode and st_code <= @endcode) UNION SELECT st_code, st_desc FROM st_msuspend WHERE (@ds = 'o' or @ds = 'i') and (st_code >= @startcode and st_code <= @endcode))m on t.st_code=m.st_code left join st_head h on h.trx_type=t.trx_type and t.trnx_ref=(CASE WHEN t.trx_type IN ('DO','SO','SOX')then h.do_no else h.in_no END) WHERE (t.do_date >= @startdate and t.do_date <= @enddate) and (t.trx_type NOT IN ('PDO','GRO','ADJ')) and (cm_cust_no >= 'A001' and cm_cust_no <= 'AAA'))SELECT sqno, company, do_date, trx_type, reference, st_code, st_desc, quantity, forex_code, unit_price, subtotal, (SELECT SUM(subtotal) FROM CTE two WHERE two.sqno<=one.sqno and two.company=one.company)runningtotalFROM CTE one[/code]TEST RUN 1[code]sqno company do_date trx_type reference st_code st_desc quantity forex_code unit_price subtotal runningtotal-------------------- -------------------- ----------------------- -------- -------------------- ------------------------- --------------------------------------------- --------------------------------------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------1 A001 2009-01-21 00:00:00.000 DO BQ-5150C BQ-5150CFDFDFZD -20.0000 MAL 25.5000 510.0000 3060.00002 A001 2009-01-21 00:00:00.000 INV LCF-00453 BQ-5150C BQ-5150CFDFDFZD -40.0000 NULL 25.5000 1020.0000 3570.00003 A001 2009-01-21 00:00:00.000 INV LCF-00454 BQ-5150C BQ-5150CFDFDFZD -5.0000 NULL 25.5000 127.5000 8670.00004 A001 2009-01-21 00:00:00.000 INV LCF-00455 BQ-5150C BQ-5150CFDFDFZD -1.0000 NULL 25.5000 25.5000 13770.00005 A001 2009-01-21 00:00:00.000 INV LCF-00456 BQ-5150C BQ-5150CFDFDFZD -4.0000 NULL 25.5000 102.0000 14790.00006 A001 2009-01-21 00:00:00.000 INV LCF-00457 BQ-5150C BQ-5150CFDFDFZD -2.0000 NULL 25.5000 51.0000 14917.50007 A001 2009-01-21 00:00:00.000 DO INV01068 BQ-5150C BQ-5150CFDFDFZD -120.0000 MAL 25.5000 3060.0000 14943.00008 A001 2009-01-21 00:00:00.000 DOL DOL-090121 BQ-5150C BQ-5150CFDFDFZD -8.0000 NULL 25.5000 5100.0000 15045.00009 A001 2009-01-21 00:00:00.000 DOL DOL-090121 BQ-5150C BQ-5150CFDFDFZD 0.0000 NULL 25.5000 5100.0000 15096.000010 A001 2009-01-21 00:00:00.000 DOL DOL-090121 K-CDRW482448 PRODUCT1497 -8.0000 NULL 146.0000 14600.0000 20936.000011 A001 2009-01-21 00:00:00.000 DOL DOL-090121 K-CDRW482448 PRODUCT1497 0.0000 NULL 146.0000 14600.0000 21228.000012 A001 2009-01-21 00:00:00.000 INV LCF-00453 K-CDRW482448 PRODUCT1497 -30.0000 NULL 146.0000 4380.0000 21812.000013 A001 2009-01-21 00:00:00.000 INV LCF-00454 K-CDRW482448 PRODUCT1497 -5.0000 NULL 146.0000 730.0000 21958.000014 A001 2009-01-21 00:00:00.000 INV LCF-00455 K-CDRW482448 PRODUCT1497 -1.0000 NULL 146.0000 146.0000 22688.000015 A001 2009-01-21 00:00:00.000 DO INV01068 K-CDRW482448 PRODUCT1497 -40.0000 MAL 146.0000 5840.0000 27068.000016 A001 2009-01-21 00:00:00.000 INV LCF-00456 K-CDRW482448 PRODUCT1497 -4.0000 NULL 146.0000 584.0000 41668.000017 A001 2009-01-21 00:00:00.000 INV LCF-00457 K-CDRW482448 PRODUCT1497 -2.0000 NULL 146.0000 292.0000 56268.000018 A001 2009-01-21 00:00:00.000 DO K-CDRW482448 PRODUCT1497 -10.0000 MAL 146.0000 1460.0000 57728.000019 A002 2009-01-20 00:00:00.000 DO INV01063 TEST_DOL -55.0000 RM 102.0000 5610.0000 5610.000020 A002 2009-01-20 00:00:00.000 DO INV01064 TEST_DOL -45.0000 RM 102.0000 4590.0000 10200.000021 AAA 2009-01-19 00:00:00.000 DOL DOL-00430 TRD-SPORTY -1.0000 NULL 1119.0000 1007.1000 1007.100022 AAA 2009-01-19 00:00:00.000 DOL DOL-00430 TRD-SPORTY 0.0000 NULL 1119.0000 1007.1000 2014.200023 AAA 2009-01-20 00:00:00.000 DO INV01065 BQ-5150C BQ-5150CFDFDFZD -15.0000 RM 55.2500 828.7500 2842.950024 AAA 2009-01-20 00:00:00.000 DO INV01066 BQ-5150C BQ-5150CFDFDFZD -20.0000 RM 55.2500 1105.0000 3947.950025 AAA 2009-01-20 00:00:00.000 DO INV01067 BQ-5150C BQ-5150CFDFDFZD -15.0000 RM 55.2500 828.7500 4776.700026 AAA 2009-01-20 00:00:00.000 DO INV01062 TRD-SPORTY -3.0000 RM 1222.0000 3666.0000 8442.700027 AAA 2009-01-21 00:00:00.000 INV LCF-00460 CUTTER YELLOW -1.0000 NULL 11.2000 11.2000 8465.100028 AAA 2009-01-21 00:00:00.000 INV LCF-00461 CUTTER YELLOW -4.0000 NULL 11.2000 44.8000 8498.700029 AAA 2009-01-21 00:00:00.000 DO CUTTER YELLOW -3.0000 RM 11.2000 33.6000 8509.900030 AAA 2009-01-21 00:00:00.000 DO INV01074 CUTTER YELLOW -2.0000 RM 11.2000 22.4000 8554.700031 AAA 2009-01-21 00:00:00.000 DO INV01074 K-CDRW482448 PRODUCT1497 -5.0000 RM 146.0000 730.0000 10598.700032 AAA 2009-01-21 00:00:00.000 INV LCF-00460 K-CDRW482448 PRODUCT1497 -11.0000 NULL 146.0000 1606.0000 12204.700033 AAA 2009-01-21 00:00:00.000 DO K-CDRW482448 PRODUCT1497 -10.0000 RM 146.0000 1460.0000 13664.700034 AAA 2009-01-21 00:00:00.000 INV LCF-00461 K-CDRW482448 PRODUCT1497 -14.0000 NULL 146.0000 2044.0000 14394.700035 AAA 2009-01-22 00:00:00.000 DOL DOL-00434 THS-PENZOIL -50.0000 NULL 111.0000 4995.0000 19389.700036 AAA 2009-01-22 00:00:00.000 DOL DOL-00434 THS-PENZOIL 0.0000 NULL 111.0000 4995.0000 24384.700037 AAA 2009-03-11 00:00:00.000 INV INV01083 123 -1.0000 RM 0.0000 0.0000 24384.700038 AAA 2009-11-05 00:00:00.000 DO kim-06242 111 PRODUCT20 -500.0000 NULL 26.0000 130.0000 24514.7000(38 row(s) affected)[/code]
Hope can help...but advise to wait pros with confirmation... |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 04:49:27
|
TEST RUN 2sqno company do_date trx_type reference st_code st_desc quantity forex_code unit_price subtotal runningtotal-------------------- -------------------- ----------------------- -------- -------------------- ------------------------- --------------------------------------------- --------------------------------------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------1 A001 2009-01-21 00:00:00.000 DO BQ-5150C BQ-5150CFDFDFZD -20.0000 MAL 25.5000 510.0000 3060.00002 A001 2009-01-21 00:00:00.000 INV LCF-00453 BQ-5150C BQ-5150CFDFDFZD -40.0000 NULL 25.5000 1020.0000 3570.00003 A001 2009-01-21 00:00:00.000 INV LCF-00454 BQ-5150C BQ-5150CFDFDFZD -5.0000 NULL 25.5000 127.5000 8670.00004 A001 2009-01-21 00:00:00.000 INV LCF-00455 BQ-5150C BQ-5150CFDFDFZD -1.0000 NULL 25.5000 25.5000 13770.00005 A001 2009-01-21 00:00:00.000 INV LCF-00456 BQ-5150C BQ-5150CFDFDFZD -4.0000 NULL 25.5000 102.0000 14790.00006 A001 2009-01-21 00:00:00.000 INV LCF-00457 BQ-5150C BQ-5150CFDFDFZD -2.0000 NULL 25.5000 51.0000 14917.50007 A001 2009-01-21 00:00:00.000 DO INV01068 BQ-5150C BQ-5150CFDFDFZD -120.0000 MAL 25.5000 3060.0000 14943.00008 A001 2009-01-21 00:00:00.000 DOL DOL-090121 BQ-5150C BQ-5150CFDFDFZD -8.0000 NULL 25.5000 5100.0000 15045.00009 A001 2009-01-21 00:00:00.000 DOL DOL-090121 BQ-5150C BQ-5150CFDFDFZD 0.0000 NULL 25.5000 5100.0000 15096.000010 A001 2009-01-21 00:00:00.000 DOL DOL-090121 K-CDRW482448 PRODUCT1497 -8.0000 NULL 146.0000 14600.0000 20936.000011 A001 2009-01-21 00:00:00.000 DOL DOL-090121 K-CDRW482448 PRODUCT1497 0.0000 NULL 146.0000 14600.0000 21228.000012 A001 2009-01-21 00:00:00.000 INV LCF-00453 K-CDRW482448 PRODUCT1497 -30.0000 NULL 146.0000 4380.0000 21812.000013 A001 2009-01-21 00:00:00.000 INV LCF-00454 K-CDRW482448 PRODUCT1497 -5.0000 NULL 146.0000 730.0000 21958.000014 A001 2009-01-21 00:00:00.000 INV LCF-00455 K-CDRW482448 PRODUCT1497 -1.0000 NULL 146.0000 146.0000 22688.000015 A001 2009-01-21 00:00:00.000 DO INV01068 K-CDRW482448 PRODUCT1497 -40.0000 MAL 146.0000 5840.0000 27068.000016 A001 2009-01-21 00:00:00.000 INV LCF-00456 K-CDRW482448 PRODUCT1497 -4.0000 NULL 146.0000 584.0000 41668.000017 A001 2009-01-21 00:00:00.000 INV LCF-00457 K-CDRW482448 PRODUCT1497 -2.0000 NULL 146.0000 292.0000 56268.000018 A001 2009-01-21 00:00:00.000 DO K-CDRW482448 PRODUCT1497 -10.0000 MAL 146.0000 1460.0000 57728.000019 A002 2009-01-20 00:00:00.000 DO INV01063 TEST_DOL -55.0000 RM 102.0000 5610.0000 5610.000020 A002 2009-01-20 00:00:00.000 DO INV01064 TEST_DOL -45.0000 RM 102.0000 4590.0000 10200.000021 AAA 2009-01-19 00:00:00.000 DOL DOL-00430 TRD-SPORTY -1.0000 NULL 1119.0000 1007.1000 1007.100022 AAA 2009-01-19 00:00:00.000 DOL DOL-00430 TRD-SPORTY 0.0000 NULL 1119.0000 1007.1000 2014.200023 AAA 2009-01-20 00:00:00.000 DO INV01065 BQ-5150C BQ-5150CFDFDFZD -15.0000 RM 55.2500 828.7500 2842.950024 AAA 2009-01-20 00:00:00.000 DO INV01066 BQ-5150C BQ-5150CFDFDFZD -20.0000 RM 55.2500 1105.0000 3947.950025 AAA 2009-01-20 00:00:00.000 DO INV01067 BQ-5150C BQ-5150CFDFDFZD -15.0000 RM 55.2500 828.7500 4776.700026 AAA 2009-01-20 00:00:00.000 DO INV01062 TRD-SPORTY -3.0000 RM 1222.0000 3666.0000 8442.700027 AAA 2009-01-21 00:00:00.000 INV LCF-00460 CUTTER YELLOW -1.0000 NULL 11.2000 11.2000 8465.100028 AAA 2009-01-21 00:00:00.000 INV LCF-00461 CUTTER YELLOW -4.0000 NULL 11.2000 44.8000 8498.700029 AAA 2009-01-21 00:00:00.000 DO CUTTER YELLOW -3.0000 RM 11.2000 33.6000 8509.900030 AAA 2009-01-21 00:00:00.000 DO INV01074 CUTTER YELLOW -2.0000 RM 11.2000 22.4000 8554.700031 AAA 2009-01-21 00:00:00.000 DO INV01074 K-CDRW482448 PRODUCT1497 -5.0000 RM 146.0000 730.0000 10598.700032 AAA 2009-01-21 00:00:00.000 INV LCF-00460 K-CDRW482448 PRODUCT1497 -11.0000 NULL 146.0000 1606.0000 12204.700033 AAA 2009-01-21 00:00:00.000 DO K-CDRW482448 PRODUCT1497 -10.0000 RM 146.0000 1460.0000 13664.700034 AAA 2009-01-21 00:00:00.000 INV LCF-00461 K-CDRW482448 PRODUCT1497 -14.0000 NULL 146.0000 2044.0000 14394.700035 AAA 2009-01-22 00:00:00.000 DOL DOL-00434 THS-PENZOIL -50.0000 NULL 111.0000 4995.0000 19389.700036 AAA 2009-01-22 00:00:00.000 DOL DOL-00434 THS-PENZOIL 0.0000 NULL 111.0000 4995.0000 24384.700037 AAA 2009-03-11 00:00:00.000 INV INV01083 123 -1.0000 RM 0.0000 0.0000 24384.700038 AAA 2009-11-05 00:00:00.000 DO kim-06242 111 PRODUCT20 -500.0000 NULL 26.0000 130.0000 24514.7000(38 row(s) affected) I HAVE TOTALLY NO IDEA WHY IT WILL BECOME LIKE THAT...IT MADE ME CRAZY!!!
Hope can help...but advise to wait pros with confirmation... |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 05:39:08
|
 i still cant find where is the problem...
Hope can help...but advise to wait pros with confirmation... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-07 : 07:23:42
|
try go via temp table
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 09:16:59
|
hmmm my syntax for running total correct?? or should i use cross apply for faster compilation time??<--- super weak in cross apply...
Hope can help...but advise to wait pros with confirmation... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-07 : 09:47:20
|
yes. The Subquery is correct. Cross Apply versionSELECT sqno, company, do_date, trx_type, reference, st_code, st_desc, quantity, forex_code, unit_price, subtotal, (SELECT SUM(subtotal) FROM CTE two WHERE two.sqno<=one.sqno and two.company=one.company) runningtotalFROM CTE one CROSS APPLY ( SELECT SUM(subtotal) as runningtotal FROM CTE two WHERE two.sqno <= one.sqno and two.company = one.company ) two
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-07 : 09:49:18
|
or maybe if you can provide the script for your tables and data in "create table #temp" or "declare @temp" etc that we can run in our environment to test out your query . .
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-08-07 : 12:59:23
|
too bad sifu...i have no idea how to provide sample data...you see the way i join my table....T.T
Hope can help...but advise to wait pros with confirmation... |
|
|
|
|
|
|
|
|
Running total again..