T-SQL

Re: un alt Script...o alta provocare

Alex — Fri, 16 Feb 2007 10:57:29 GMT

multumesc mult Razvan......intradevar....baza de date e groaznica....daca iti vine sa crezi nu are absolut nici un FK definit pe ea......in plus exista tabele care nu au nici macar un PK.....asa am primit-o si eu......si ce e si mai groaznic este ca nu exista codul aplicatiei care foloseste aceasta baza de date.....ca poate atunci m-as fi bagat mai adanc in ea......atat cat ma pricep si eu........multe lucruri care se executa pe aceasta baza (insert,update,delete,select) se fac din codul aplicatiei neexistand cum zic eu ca ar fi normal proceduri in baza pt acestea (insert,update,delete,select)....acum poate ma intelegeti cu ce ma lupt zilnic...si-mi mananc creierii.....

dar cum bine se zice....."ce nu te omoara te intareste".....cel putin asa sper sa fie :)

o sa incerc sa-ti raspund la intrebari:

1.intradevar legatura intre tabele se face ON QASLogInfoProba.QASUserName = QASUser.UserIdentification....nu are FK intre ele.....dar o sa fac..

2.QASItemType contine intredevar tipul evenimentului respectiv Logon sau Logoff adica la o anumita data QASItemTime am Logon dupa care pt acelasi QASUserName am Logoff....fiind posibil ca este user sa faca in accesi zi mai multe Logon/Logoff....pe diferite sau pe aceesi QASStation

inregistrand un QASLiCounter pt fiecare

3.cred ca a fost impartita astfel pe CycleTime_L01...L02 etc si nu doar o tabela CycleTime...pt ca fiecare aceasta L01,L02 contine undeva pe la 1 milion de inregistrari

ideea de fetchuire pica pt ca am incercat ceva asemanator si dupa ce am asteptat aprox 15 min.....l-am oprit......cred ca nu este posibila o fetchuire pe asemenea tabele

in concluzie nu stiu exact cum am sa reusesc :(

Re: un alt Script...o alta provocare

rsocol — Thu, 15 Feb 2007 20:23:17 GMT

Mai întâi, avem următoarele probleme:

1. Care e legătura dintre tabelele QASLogInfoProba şi QASUserProba ? Cred că e vorba de QASUserName, care ar fi trebuit să fie un foreign key spre UserIdentification, dar nu poate fi, deoarece tipurile de date sunt diferite.

2. Cum se reprezintă logon şi logoff în tabela QASLogInfo, pentru că văd o singură coloană de tip datetime. Cumva QASItemType reprezintă tipul evenimentului ?

3. Ideea de a avea tabele separate "CycleTimes_L01", "CycleTimes_L02", etc este în contradicţie cu regulile de normalizare a bazelor de date. Ar fi trebuit să existe o singură tabelă CycleTimes, iar L01, L02, etc să fie nişte valori într-o coloană a acestei tabele.

Dacă am fi avut nişte tabele mai normale, query-ul putea să fie de genul următor:

SELECT UserName, ComponentName,
SUM(DurationInSeconds) as TotalDurationInSeconds
FROM Users u INNER JOIN Sessions s ON u.UserID=s.UserID
INNER JOIN CycleTimes t ON s.Station=t.Station
INNER JOIN Components c ON c.ComponentID=t.ComponentID
GROUP BY UserName, ComponentName

În condiţiile în care trebuie să lucrezi cu tabele nenormalizate, cu foreign key-uri care lipsesc şi cu informaţii care sunt ascunse ca ultimele 3 caractere dintr-o coloană, eu personal nu vreau să dau o soluţie completă pentru aşa ceva. În principiu, poţi folosi cursoare, dynamic sql şi tabele temporare ca să rezolvi orice, dar soluţia corectă porneşte de la o structură sănătoasă a bazei de date şi am impresia că asta lipseşte în primul rând. Cu structura existentă, codul ar putea fi de genul următor:

DECLARE Utilizatori CURSOR FOR
SELECT UserIdentification FROM QASUserProba

WHILE 1=1 BEGIN

FETCH NEXT FROM Utilizator INTO @Utilizator
IF @@FETCH_STATUS<>0 BREAK

DECLARE Statii CURSOR FOR
SELECT QASStation FROM QASLogInfoProba WHERE QASUserName=@Utilizator

WHILE 1=1 BEGIN

FETCH NEXT FROM Statii INTO @Statie
IF @@FETCH_STATUS<>0 BREAK

     DECLARE @SQL nvarchar(4000)
     SET @SQL='
       INSERT INTO #Temp
   SELECT '''+@Utilizator+''', '''+@Statie+''', NumeComponenta, SUM(timeInWsSeconds)
       FROM CycleTimes_'+@Statie+' WHERE QASStation='''+@Statie+''' AND ???
       GROUP BY NumeComponenta
     '

     EXEC (@SQL)

END
END

Evident, codul de mai sus e incomplet, vulnerabil la SQL Injection, neperformant şi într-un cuvânt, groaznic. Cel mai bine e să corectezi structura bazei de date, chiar dacă poţi să-l completezi ca să fie acceptabil.

Răzvan

un alt Script...o alta provocare

Alex — Thu, 15 Feb 2007 11:45:08 GMT

salut...am urmatoarele tabele

CREATE TABLE [dbo].[QASUsersProba] (
[UserIdentification] [varchar] (150) NOT NULL ,
[UserLevel] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[Status] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[QASUsersProba] WITH NOCHECK ADD
CONSTRAINT [PK_QASUsersProba] PRIMARY KEY CLUSTERED
(
[UserIdentification]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[QASUsersProba] WITH NOCHECK ADD
CONSTRAINT [DF_QASUsersProba_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO

-----------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[QASLogInfoProba] (
[QASLICounter] [int] IDENTITY (1, 1) NOT NULL ,
[QASItemTime] [datetime] NOT NULL ,
[QASUserName] [varchar] (50) NOT NULL ,
[QASItemType] [varchar] (50) NOT NULL ,
[QASStation] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[QASLogInfoProba] WITH NOCHECK ADD
CONSTRAINT [PK_QASLogInfoProba] PRIMARY KEY CLUSTERED
(
[QASLICounter]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-------------------------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[CycleTimes_L01] (
[CTCounter] [int] IDENTITY (1, 1) NOT NULL ,
[assemblyPaletteID] [varchar] (16) NOT NULL ,
[SerialNumber] [varchar] (25) NULL ,
[startTime] [datetime] NOT NULL ,
[endTime] [datetime] NOT NULL ,
[timeInWsSeconds] [int] NULL ,
[APLCounter] [int] NULL ,
[QASStation] [nvarchar] (3) NULL ,
[QASLICounter] AS ([dbo].[FgetQASLICounter]([endtime], [QASStation])) ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CycleTimes_L01] WITH NOCHECK ADD
CONSTRAINT [PK_CycleTimes_L01] PRIMARY KEY CLUSTERED
(
[CTCounter]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[CycleTimes_L01] WITH NOCHECK ADD
CONSTRAINT [DF_CycleTimes_L01_QASStation] DEFAULT ('L01') FOR [QASStation]
GO

----------------------------------

CREATE TABLE [dbo].[CycleTimes_L02] (
[CTCounter] [int] IDENTITY (1, 1) NOT NULL ,
[assemblyPaletteID] [varchar] (16) NOT NULL ,
[SerialNumber] [varchar] (25) NULL ,
[startTime] [datetime] NOT NULL ,
[endTime] [datetime] NOT NULL ,
[timeInWsSeconds] [int] NULL ,
[APLCounter] [int] NULL ,
[QASStation] [nvarchar] (3) NULL ,
[QASLICounter] AS ([dbo].[FgetQASLICounter]([endtime], [QASStation])) ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CycleTimes_L02] WITH NOCHECK ADD
CONSTRAINT [PK_CycleTimes_L02] PRIMARY KEY CLUSTERED
(
[CTCounter]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[CycleTimes_L02] WITH NOCHECK ADD
CONSTRAINT [DF_CycleTimes_L02_QASStation] DEFAULT ('L02') FOR [QASStation]
GO

--------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[N_CompName] (
[ID_CompName] [int] IDENTITY (1, 1) NOT NULL ,
[Seria] [varchar] (10) NULL ,
[Nume] [nvarchar] (50) NULL ,
[recData] [datetime] NULL ,
[recUser] [nvarchar] (50) NULL ,
[recStation] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[N_CompName] WITH NOCHECK ADD
CONSTRAINT [PK_N_CompName] PRIMARY KEY CLUSTERED
(
[ID_CompName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[N_CompName] WITH NOCHECK ADD
CONSTRAINT [DF__N_CompNam__recDa__0E8E2250] DEFAULT (getdate()) FOR [recData],
CONSTRAINT [DF__N_CompNam__recUs__0B7CAB7B] DEFAULT (suser_sname()) FOR [recUser],
CONSTRAINT [DF__N_CompNam__recSt__086B34A6] DEFAULT (host_name()) FOR [recStation]
GO

------------------------------------

Trebuie sa iau UserIdentification din tabela QASUserProba adica fiecare utilizator sa verific perioada de timp
in care a fost logon si logoff in functie de fiecare zi verific daca a fost logat pe mai multe statii QASStation din QASLogInfo
...iau aceste QASStation si ma duc in CycleTimes_(QASStation) (Exemplu: CycleTimes_L01 sau L02.....pt N statii) intru in ea si calculez(adun) timeInWsSeconds pe ziua respectiva pt acel QASLiCounter pe fiecare QASStation iau si SerialNumber ultimele 3 si scot din N_CompName
Nume-le respectivului Componenet pe care a lucrat

cum pot scoate aceste rezultate .....ma intereseaza mai mult cum pot parametriza in functie de CycleTimes_(QASStation....L01...L02...) ca sa treaca pe unde trebuie si sa calculeze timpul de lucru.

e destul de greu de explicat....nu ma certati daca nu intelegeti nimic din intrebarea mea....e vina mea recunosc....dar de multe ori in timp ce va scriam o intrebare.....gaseam si raspunsul la ea :).....mi se clarificau si mie lucrurile mai bine....:)))