Re: Spalten alternativ ansprechen

Hallo an alle,

erst mal herzlichen Dank, dass Ihr euch meines Problems annehmt.
Zeitens glaube ich, dass Stefan mit seinem Ansatz meinem Ziel recht
nahe kommt, ohne dass ich mir seinen Vorschlag genauer angesehen hätte
und alles verstanden habe.

Um weitere Verwirrungen zu vermeiden poste ich jetz hier mal mein
"Monate-Script".

(darunter folgt dann nochmal ein Versuch, mein Problem darzustellen)

---------------------------------------------------------------------
CREATE Procedure [12Monate]
as
set QUOTED_IDENTIFIER off
DECLARE @Tag INT
DECLARE @Monat INT
DECLARE @Jahr INT
DECLARE @0 as varchar(20)
DECLARE @1 as varchar(20)
DECLARE @2 as varchar(20)
DECLARE @3 as varchar(20)
DECLARE @4 as varchar(20)
DECLARE @5 as varchar(20)
DECLARE @6 as varchar(20)
DECLARE @7 as varchar(20)
DECLARE @8 as varchar(20)
DECLARE @9 as varchar(20)
DECLARE @10 as varchar(20)
DECLARE @11 as varchar(20)
DECLARE @12 as varchar(20)

DECLARE @0s as varchar(20)
DECLARE @1s as varchar(20)
DECLARE @2s as varchar(20)
DECLARE @3s as varchar(20)
DECLARE @4s as varchar(20)
DECLARE @5s as varchar(20)
DECLARE @6s as varchar(20)
DECLARE @7s as varchar(20)
DECLARE @8s as varchar(20)
DECLARE @9s as varchar(20)
DECLARE @10s as varchar(20)
DECLARE @11s as varchar(20)
DECLARE @12s as char(7)

DECLARE @cmdStr as char(1000)

set @Tag = DAY(GETDATE())
set @Monat = MONTH(GETDATE())
set @JAHR = YEAR(GETDATE())

set @0 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST(CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @0s = cast(cast(DATENAME(month, @0) as char(3)) +
cast(datepart(year, @0) as char(4)) as varchar(7))
set @0 = cast(@0 as char(11))


set @Monat = @Monat-1
set @1 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @1s = cast(cast(DATENAME(month, @1) as char(3)) +
cast(datepart(year, @1) as char(4)) as varchar(7))
set @1 = cast(@1 as char(11))


set @Monat = @Monat-1
set @2 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @2s = cast(cast(DATENAME(month, @2) as char(3)) +
cast(datepart(year, @2) as char(4)) as varchar(7))
set @2 = cast(@2 as char(11))


set @Monat = @Monat-1
set @3 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @3s = cast(cast(DATENAME(month, @3) as char(3)) +
cast(datepart(year, @3) as char(4)) as varchar(7))
set @3 = cast(@3 as char(11))


set @Monat = @Monat-1
set @4 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @4s = cast(cast(DATENAME(month, @4) as char(3)) +
cast(datepart(year, @4) as char(4)) as varchar(7))
set @4 = cast(@4 as char(11))


set @Monat = @Monat-1
set @5 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @5s = cast(cast(DATENAME(month, @5) as char(3)) +
cast(datepart(year, @5) as char(4)) as varchar(7))
set @5 = cast(@5 as char(11))


set @Monat = @Monat-1
set @6 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @6s = cast(cast(DATENAME(month, @6) as char(3)) +
cast(datepart(year, @6) as char(4)) as varchar(7))
set @6 = cast(@6 as char(11))


set @Monat = @Monat-1
set @7 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @7s = cast(cast(DATENAME(month, @7) as char(3)) +
cast(datepart(year, @7) as char(4)) as varchar(7))
set @7 = cast(@7 as char(11))


set @Monat = @Monat-1
set @8 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @8s = cast(cast(DATENAME(month, @8) as char(3)) +
cast(datepart(year, @8) as char(4)) as varchar(7))
set @8 = cast(@8 as char(11))


set @Monat = @Monat-1
set @9 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST( CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @9s = cast(cast(DATENAME(month, @9) as char(3)) +
cast(datepart(year, @9) as char(4)) as varchar(7))
set @9 = cast(@9 as char(11))

set @Monat = @Monat-1
set @10 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST(CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @10s = cast(cast(DATENAME(month, @10) as char(3)) +
cast(datepart(year, @10) as char(4)) as varchar(7))
set @10 = cast(@10 as char(11))

set @Monat = @Monat-1
set @11 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST(CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @11s = cast(cast(DATENAME(month, @11) as char(3)) +
cast(datepart(year, @11) as char(4)) as varchar(7))
set @11 = cast(@11 as char(11))

set @Monat = @Monat-1
set @12 = case when @Monat > 0 then
CAST(CAST(@Jahr AS CHAR(4)) + REPLACE(STR(@Monat,2),' ',0) + '01' AS
DATETIME)
else
CAST(CAST(@Jahr-1 AS CHAR(4)) + REPLACE(STR(@Monat+12,2),' ',0) +
'01' AS DATETIME)
end
set @12s = cast(cast(DATENAME(month, @12) as char(3)) +
cast(datepart(year, @12) as char(4)) as varchar(7))
set @12 = cast(@12 as char(11))

set @cmdStr = "SELECT '" + @12 + "' as " + @12s
+ ", '" + @11 + "' as " + @11s
+ ", '" + @10 + "' as " + @10s
+ ", '" + @9 + "' as " + @9s
+ ", '" + @8 + "' as " + @8s
+ ", '" + @7 + "' as " + @7s
+ ", '" + @6 + "' as " + @6s
+ ", '" + @5 + "' as " + @5s
+ ", '" + @4 + "' as " + @4s
+ ", '" + @3 + "' as " + @3s
+ ", '" + @2 + "' as " + @2s
+ ", '" + @1 + "' as " + @1s
+ ", '" + @0 + "' as " + @0s

exec(@cmdStr)
GO
-------------------------------------------------------------------------

Sollte so überall laufen. Evtl. unsauberes Scripting bitte ich zu
entschuldigen - bin newbi.

Nun nochmal zum Problem:

Stellt euch vor, ich habe eine Auftragsdatei mit Aufträgen der letzten
2 Mio Jahre. Ich möchte aber von allen Kunden nur die Aufträge aus den
letzten 12 Monaten sehen, um z.B. nachzuschauen, wie die Tendenzen
sind. Dann kann ich jeden Monat in die einfache Abfrage:
---------------
select
KUNDE,
case when DATUM between '...' and '...' then UMSATZ as
Monat1_vor12Monaten,
case when DATUM between '...' and '...' then UMSATZ as
Monat2_vor11Monaten,
case when DATUM between '...' and '...' then UMSATZ as
Monat3_vor10Monaten,
etc.
from AUFTRAEGE
---------------
und die Datumswerte UND VOR ALLEM AUCH DIE SPALTENNAMEN DER MONATE
aendern,

oder!

Ich mach mir das obige Script, in dem ich immer die letzen 12 Monate
habe und versuch dann sowas wie:

---------------
select
KUNDE
case when DATUM between 12Monate.@Spalte1 and 12Monate.@Spalte2 then
Umsatz as 12Monate.@SpaltenName1,
case when DATUM between 12Monate.@Spalte2 and 12Monate.@Spalte3 then
Umsatz as 12Monate.@SpaltenName2,
etc.
from AUFTRAEGE
---------------

Ich möchte einfach die Werte aus der 12Monate-SP in einer Abrage als
Bedingung nutzen. Das kann ich aber nicht, weil ich dazu den
Spaltennamen angeben muss. Da der nächsten Monat aber anders ist,
sollte ich den SapltenNamen nicht mit Namen (ABSOLUT) angeben sondern
mit seiner Position (RELATIV), z.B. die Xte Spalte aus der
12Monate-SP.

Noch Fragen! :-[

Gruß, Hanno
.

Relevant Pages

  • Re: a view based on stored procedure
    ... DECLARE @Week_1 DATETIME ... DECLARE @CubeTime DATETIME ... INSERT INTO WrkAvailPlanHours ...
    (microsoft.public.sqlserver.programming)
  • Re: Language problem
    ... @datum datetime ... > Then in application I have settings of my connection: ... > everything else doesn't because all other functions are set to slovenian. ... > DECLARE @dt DATETIME,@datumMesec dateTime,@datumTedenZ datetime,@datumTedenK ...
    (microsoft.public.sqlserver.programming)
  • RE: User Defined Function Help
    ... @startDateTime datetime = null, ... DECLARE @selectFromList varchar ... SET @subservice = RTRIM ... EXEC ...
    (microsoft.public.sqlserver.programming)
  • Re: get time in SQL server
    ... >conversion to datetime data type but could not do it. ... >declare @date2 as datetime ... declare @date1 varchar ...
    (microsoft.public.sqlserver.server)
  • Re: Recordset or Object is closed HELP PLEASE!
    ... DECLARE @IntYearBeforeLast INT ... DECLARE @DateSepFirstYearBeforeLast DATETIME ...
    (microsoft.public.excel.programming)
Loading