Cześć wam,
potrzebuję pomocy z napisaniem kwerendy, która zliczałaby dla każdego miesiąca wartość pozostałą do spłaty.
W szczegółach pisząc mam tabelę z kolumnami 201804, 201805, 201806...201812, ....., 203212 i chciałbym dla każdego rekordu policzyć sumę z tych kolumn w ten sposób, żeby dla 201804 była to suma od 201805 do 203212, dla 201805 od 201806 do 203212 i tak dalej.
Czy byłby ktoś w stanie podrzucić pomysł jak napisać taką petlę, która obsługiwałaby tak liczoną sumę?
Pozdrawiam
Żadne pętla. trzeba to zrobić (i można) SQL-em. Ale jak nie podasz nazwy bazy, to ci nikt nie pomoże...
Jeśli dobrze rozumiem, to masz tabelę ze 177 kolumnami o nazwach od [201804] do [203212], np:
SELECT * FROM Amounts
----------------------------------------------------------------- -----------
| 201804 | 201805 | 201806 | 201807 | 201808 | 201809 | 201810 | | 203212 |
----------------------------------------------------------------- -----------
| 10 | 22 | 12 | 1 | 32 | 23 | 11 | ... | 73 |
----------------------------------------------------------------- -----------
| 31 | 4 | 72 | 6 | 12 | 87 | 44 | | 3 |
----------------------------------------------------------------- -----------
Dla uproszczenia nadpisuję wartości w oryginalnej tabeli. Jeśli można to zrobić bez pętli, to chciałbym poznać sposób.
DECLARE
@currentYear INT = 2018,
@currentMonth INT = 4,
@year INT = 2032,
@month INT = 12,
@column NVARCHAR(6),
@nextColumn NVARCHAR(6)
WHILE (@month <> @currentMonth OR @year <> @currentYear)
BEGIN
SET @column = STR(@year, 4) + REPLACE(STR(@month, 2), SPACE(1), '0')
SET @month = @month - 1
IF (@month = 0)
BEGIN
SET @month = 12
SET @year = @year - 1
END
SET @nextColumn = STR(@year, 4) + REPLACE(STR(@month, 2), SPACE(1), '0')
EXECUTE('UPDATE Amounts SET [' + @nextColumn + '] = [' + @column + '] + [' + @nextColumn + ']')
END
@Burmistrz: prosisz, masz ;)
declare @query as nvarchar(max)
SELECT distinct
convert(int,convert(varchar(4),year(dateadd(month,number,'20180301')))+right('0'+convert(varchar(2),month(dateadd(month,number,'20180301'))),2)) as c
into #tmp
FROM master.dbo.spt_values
where number between 1 and 177
set @query = 'Update
table
set
'
select
@query = @query + STRING_AGG(cast(c+'='+v as nvarchar(max)),',')
from (select
QUOTENAME(tmp.c) c
, isnull(STRING_AGG(QUOTENAME(ce.c),'+'),'0') v
from
#tmp tmp
left join #tmp ce on tmp.c < ce.c
group by
tmp.c) dt
sp_executesql @query
Tu fiddle, bez tabeli tymczasowej bo to nie działa na tej platformie: http://sqlfiddle.com/#!18/c4136/13
Burmistrz napisał(a):
Jeśli dobrze rozumiem, to masz tabelę ze 177 kolumnami o nazwach od
[201804]do[203212], np:SELECT * FROM Amounts ----------------------------------------------------------------- ----------- | 201804 | 201805 | 201806 | 201807 | 201808 | 201809 | 201810 | | 203212 | ----------------------------------------------------------------- ----------- | 10 | 22 | 12 | 1 | 32 | 23 | 11 | ... | 73 | ----------------------------------------------------------------- ----------- | 31 | 4 | 72 | 6 | 12 | 87 | 44 | | 3 | ----------------------------------------------------------------- -----------Dla uproszczenia nadpisuję wartości w oryginalnej tabeli. Jeśli można to zrobić bez pętli, to chciałbym poznać sposób.
DECLARE @currentYear INT = 2018, @currentMonth INT = 4, @year INT = 2032, @month INT = 12, @column NVARCHAR(6), @nextColumn NVARCHAR(6) WHILE (@month <> @currentMonth OR @year <> @currentYear) BEGIN SET @column = STR(@year, 4) + REPLACE(STR(@month, 2), SPACE(1), '0') SET @month = @month - 1 IF (@month = 0) BEGIN SET @month = 12 SET @year = @year - 1 END SET @nextColumn = STR(@year, 4) + REPLACE(STR(@month, 2), SPACE(1), '0') EXECUTE('UPDATE Amounts SET [' + @nextColumn + '] = [' + @column + '] + [' + @nextColumn + ']') END
Jak ubrać to w kwerendę, która zgrupuje to teraz po kolumnie 'numerumowy', a nazwa tabeli w której siedzą te dane to 'harmonogramy'? Czy ta procedura zamieni dane w tabeli źródłowej (nadpisze je?)
Pozdrawiam
Panczo napisał(a):
@Burmistrz: prosisz, masz ;)
declare @query as nvarchar(max) SELECT distinct convert(int,convert(varchar(4),year(dateadd(month,number,'20180301')))+right('0'+convert(varchar(2),month(dateadd(month,number,'20180301'))),2)) as c into #tmp FROM master.dbo.spt_values where number between 1 and 177 set @query = 'Update table set ' select @query = @query + STRING_AGG(cast(c+'='+v as nvarchar(max)),',') from (select QUOTENAME(tmp.c) c , isnull(STRING_AGG(QUOTENAME(ce.c),'+'),'0') v from #tmp tmp left join #tmp ce on tmp.c < ce.c group by tmp.c) dt sp_executesql @queryTu fiddle, bez tabeli tymczasowej bo to nie działa na tej platformie: http://sqlfiddle.com/#!18/c4136/13
master.dbo.spt_values to będzie moja tabela źródłowa?
jak to zgrupować po polu 'numerumowy'?
master.dbo.spt_values to będzie moja tabela źródłowa?
Nie, to tabela na podstawie której buduje zapytanie
jak to zgrupować po polu 'numerumowy'?
Napisz może co chcesz uzyskać, bo ja wygenerowałem update, a co ty chcesz to nie wiem. Napisz proszę skąd taka struktura tabeli, bo to jest rzeźba w g... I ciągle nie napisałeś jakiej bazy używasz co nie pozwala na dobrą pomoc
Używam bazy MS SQL, tabela którą chce przerobić ma kolumny 'numerumowy' i kolumny z wartością kapitału do spłaty na każdy miesiąc - 201804, 201805, ..., 201812, .... 203212. Moim celem jest uzyskanie dla każdego rekordu 'numerumowy' sumy kapitałów pozostających do spłaty na każdy miesiąc, czyli stworzenie kolumn np 201804 która będzie sumą wartości kolumn dla danego rekordu od 201805 do 203212 i tak dalej dla każdego miesiąca. Nazwa tabeli to 'harmonogramy'
A nie lepiej (łatwiej) było by przerobić tę tabelą na "ludzką" postać?
Jak się ma taką strukturę to się rzeźbi, czyli najpierw musisz doprowadzić do obrócenia tabeli, aby miała postać: numerumowy, miesiąc, wartość to pozwoli na policzenie rat pozostających do końca i później obrócenie wyniku w drugą stronę, aby dostać oczekiwany wynik:
with nh as (SELECT numerumowy, convert(int,miesiac) miesiac, wartosc
FROM
(SELECT numerumowy, [201804],[201805],[201806],[201807],[201808],[201809],[201810],[201811],[201812],[201901],[201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909],[201910],[201911],[201912],[202001],[202002],[202003],[202004],[202005],[202006],[202007],[202008],[202009],[202010],[202011],[202012],[202101],[202102],[202103],[202104],[202105],[202106],[202107],[202108],[202109],[202110],[202111],[202112],[202201],[202202],[202203],[202204],[202205],[202206],[202207],[202208],[202209],[202210],[202211],[202212],[202301],[202302],[202303],[202304],[202305],[202306],[202307],[202308],[202309],[202310],[202311],[202312],[202401],[202402],[202403],[202404],[202405],[202406],[202407],[202408],[202409],[202410],[202411],[202412],[202501],[202502],[202503],[202504],[202505],[202506],[202507],[202508],[202509],[202510],[202511],[202512],[202601],[202602],[202603],[202604],[202605],[202606],[202607],[202608],[202609],[202610],[202611],[202612],[202701],[202702],[202703],[202704],[202705],[202706],[202707],[202708],[202709],[202710],[202711],[202712],[202801],[202802],[202803],[202804],[202805],[202806],[202807],[202808],[202809],[202810],[202811],[202812],[202901],[202902],[202903],[202904],[202905],[202906],[202907],[202908],[202909],[202910],[202911],[202912],[203001],[203002],[203003],[203004],[203005],[203006],[203007],[203008],[203009],[203010],[203011],[203012],[203101],[203102],[203103],[203104],[203105],[203106],[203107],[203108],[203109],[203110],[203111],[203112],[203201],[203202],[203203],[203204],[203205],[203206],[203207],[203208],[203209],[203210],[203211],[203212]
FROM harmonogramy) p
UNPIVOT
(wartosc FOR miesiac IN
([201804],[201805],[201806],[201807],[201808],[201809],[201810],[201811],[201812],[201901],[201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909],[201910],[201911],[201912],[202001],[202002],[202003],[202004],[202005],[202006],[202007],[202008],[202009],[202010],[202011],[202012],[202101],[202102],[202103],[202104],[202105],[202106],[202107],[202108],[202109],[202110],[202111],[202112],[202201],[202202],[202203],[202204],[202205],[202206],[202207],[202208],[202209],[202210],[202211],[202212],[202301],[202302],[202303],[202304],[202305],[202306],[202307],[202308],[202309],[202310],[202311],[202312],[202401],[202402],[202403],[202404],[202405],[202406],[202407],[202408],[202409],[202410],[202411],[202412],[202501],[202502],[202503],[202504],[202505],[202506],[202507],[202508],[202509],[202510],[202511],[202512],[202601],[202602],[202603],[202604],[202605],[202606],[202607],[202608],[202609],[202610],[202611],[202612],[202701],[202702],[202703],[202704],[202705],[202706],[202707],[202708],[202709],[202710],[202711],[202712],[202801],[202802],[202803],[202804],[202805],[202806],[202807],[202808],[202809],[202810],[202811],[202812],[202901],[202902],[202903],[202904],[202905],[202906],[202907],[202908],[202909],[202910],[202911],[202912],[203001],[203002],[203003],[203004],[203005],[203006],[203007],[203008],[203009],[203010],[203011],[203012],[203101],[203102],[203103],[203104],[203105],[203106],[203107],[203108],[203109],[203110],[203111],[203112],[203201],[203202],[203203],[203204],[203205],[203206],[203207],[203208],[203209],[203210],[203211],[203212])
) AS unpvt)
, hs as (
select
nh.numerumowy
,nh.miesiac
,isnull(k.wartosc,0) w
from
nh
left join nh k on nh.numerumowy=k.numerumowy and nh.miesiac<k.miesiac
)
select
numerumowy, [201804],[201805],[201806],[201807],[201808],[201809],[201810],[201811],[201812],[201901],[201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909],[201910],[201911],[201912],[202001],[202002],[202003],[202004],[202005],[202006],[202007],[202008],[202009],[202010],[202011],[202012],[202101],[202102],[202103],[202104],[202105],[202106],[202107],[202108],[202109],[202110],[202111],[202112],[202201],[202202],[202203],[202204],[202205],[202206],[202207],[202208],[202209],[202210],[202211],[202212],[202301],[202302],[202303],[202304],[202305],[202306],[202307],[202308],[202309],[202310],[202311],[202312],[202401],[202402],[202403],[202404],[202405],[202406],[202407],[202408],[202409],[202410],[202411],[202412],[202501],[202502],[202503],[202504],[202505],[202506],[202507],[202508],[202509],[202510],[202511],[202512],[202601],[202602],[202603],[202604],[202605],[202606],[202607],[202608],[202609],[202610],[202611],[202612],[202701],[202702],[202703],[202704],[202705],[202706],[202707],[202708],[202709],[202710],[202711],[202712],[202801],[202802],[202803],[202804],[202805],[202806],[202807],[202808],[202809],[202810],[202811],[202812],[202901],[202902],[202903],[202904],[202905],[202906],[202907],[202908],[202909],[202910],[202911],[202912],[203001],[203002],[203003],[203004],[203005],[203006],[203007],[203008],[203009],[203010],[203011],[203012],[203101],[203102],[203103],[203104],[203105],[203106],[203107],[203108],[203109],[203110],[203111],[203112],[203201],[203202],[203203],[203204],[203205],[203206],[203207],[203208],[203209],[203210],[203211],[203212]
from
(select numerumowy,miesiac, w from hs) as sc
PIVOT
(
SUM(w) for miesiac in ([201804],[201805],[201806],[201807],[201808],[201809],[201810],[201811],[201812],[201901],[201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909],[201910],[201911],[201912],[202001],[202002],[202003],[202004],[202005],[202006],[202007],[202008],[202009],[202010],[202011],[202012],[202101],[202102],[202103],[202104],[202105],[202106],[202107],[202108],[202109],[202110],[202111],[202112],[202201],[202202],[202203],[202204],[202205],[202206],[202207],[202208],[202209],[202210],[202211],[202212],[202301],[202302],[202303],[202304],[202305],[202306],[202307],[202308],[202309],[202310],[202311],[202312],[202401],[202402],[202403],[202404],[202405],[202406],[202407],[202408],[202409],[202410],[202411],[202412],[202501],[202502],[202503],[202504],[202505],[202506],[202507],[202508],[202509],[202510],[202511],[202512],[202601],[202602],[202603],[202604],[202605],[202606],[202607],[202608],[202609],[202610],[202611],[202612],[202701],[202702],[202703],[202704],[202705],[202706],[202707],[202708],[202709],[202710],[202711],[202712],[202801],[202802],[202803],[202804],[202805],[202806],[202807],[202808],[202809],[202810],[202811],[202812],[202901],[202902],[202903],[202904],[202905],[202906],[202907],[202908],[202909],[202910],[202911],[202912],[203001],[203002],[203003],[203004],[203005],[203006],[203007],[203008],[203009],[203010],[203011],[203012],[203101],[203102],[203103],[203104],[203105],[203106],[203107],[203108],[203109],[203110],[203111],[203112],[203201],[203202],[203203],[203204],[203205],[203206],[203207],[203208],[203209],[203210],[203211],[203212])
) as pt