-
ThePuiu
-
-
-
Joined on 02-14-2007
-
Oradea
-
db_owner
-
-
|
Nu ma descurc cu o interogare....
Salut, ma 'lupt' cu o interogare si nu reusesc sa-i dau de cap...daca ma poate ajuta cineva cu o idee ar fi excelent! Deci...am un tabel NOTE si unul PARTENERI. Pornind de la tabelele de mai jos, as dori sa obtin un rezultat de forma:
CREATE TABLE [dbo].[NOTE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATADOC] [datetime] NULL,
[TIPDOC] [nvarchar](50) NULL,
[NRDOC] [nvarchar](50) NULL,
[EXP] [nvarchar](50) NULL,
[CONTDB] [nvarchar](50) NULL,
[CONTCR] [nvarchar](50) NULL,
[LEI] [money] NULL) ON [PRIMARY]
CREATE TABLE [dbo].[PARTENERI](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NUME] [nvarchar](250) NULL,
[CF] [nvarchar](50) NULL,
CONSTRAINT [PK__PARTENERI__08EA5793] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ__PARTENERI__09DE7BCC] UNIQUE NONCLUSTERED
(
[NUME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[SCHEME_RG](
[ID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[OPERATIE] [decimal](18, 0) NULL,
[COL] [decimal](18, 0) NULL,
[CONTDB] [nvarchar](50) NULL,
[CONTCR] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','1111','MARFA','371.1','378.1',111.01)
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','1111','MARFA','371.1','401.MAMAMIA',657.9)
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','1111','MARFA','371.1','4428',146.09)
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','2222','MARFA','371.1','378.1',17.24)
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','2222','MARFA','371.1','401.COCACOLA',92)
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','2222','MARFA','371.1','4428',20.76)
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','3333','VANZARE','4111.PEPSICOLA','707',119.5)
INSERT INTO [NOTE] ([DATADOC],[TIPDOC],[NRDOC],[EXP],[CONTDB],[CONTCR],[LEI]) VALUES ('20080901','FAC','3333','VANZARE','4111.PEPSICOLA','4427',22.7)
INSERT INTO [PARTENERI] ([NUME],[CF]) VALUES ('MAMAMIA','1234')
INSERT INTO [PARTENERI] ([NUME],[CF]) VALUES ('COCACOLA','4522')
INSERT INTO [PARTENERI] ([NUME],[CF]) VALUES ('PEPSICOLA','413111')
INSERT INTO [SCHEME_RG] ([OPERATIE],[COL],[CONTDB],[CONTCR]) VALUES (0,1,'371.%','401.%')
INSERT INTO [SCHEME_RG] ([OPERATIE],[COL],[CONTDB],[CONTCR]) VALUES (0,1,'371.%','4428')
INSERT INTO [SCHEME_RG] ([OPERATIE],[COL],[CONTDB],[CONTCR]) VALUES (0,1,'371.%','378.%')
INSERT INTO [SCHEME_RG] ([OPERATIE],[COL],[CONTDB],[CONTCR]) VALUES (1,1,'4111.%','707')
INSERT INTO [SCHEME_RG] ([OPERATIE],[COL],[CONTDB],[CONTCR]) VALUES (1,1,'4111.%','4427')
INSERT INTO [SCHEME_RG] ([OPERATIE],[COL],[CONTDB],[CONTCR]) VALUES (0,2,'381.%','401.%')
INSERT INTO [SCHEME_RG] ([OPERATIE],[COL],[CONTDB],[CONTCR]) VALUES (1,2,'381.%','401.%')
REZULTAT
-------------------------------------------------------------
DATADOC, NRDOC, NUME_PARTENER, OPERATIE, COL1, COL2
-------------------------------------------------------------
01/09/2008, 1111, MAMAMIA, 0, 915.00, 0.00
01/09/2008, 2222, COCACOLA, 0, 130,00, 0.00
01/09/2008, 3333, PEPSICOLA, 1, 142.2, 0.00
Ce am incercat eu nu e bine...imi baga de mai multe ori (3 ori) liniile care apartin unei facturi din cauza ca nu stiu cum sa fac o conexiune intre tabela PARTENERI.NUME si contul analitic al 401.XXXX (care apare din pacate doar la unu din cele 3 rinduri corespunzatoare unui document).
Pls help ca nu mai stiu ce sa fac! Daca credeti ca e important, o sa postez interogarea asa cum am gindit-o eu (daca nu ar trebui sa afisez coloana PARTENER ar fi functionala).
Multumesc anticipat!
Revin cu codul:
SELECT DATADOC AS 'Data', NRDOC AS 'Numar',
SUM(CASE WHEN OPERATIE = 0 AND COL = 1 THEN VALOARE ELSE 0 END) AS 'A',
SUM(CASE WHEN OPERATIE = 1 AND COL = 1 THEN VALOARE ELSE 0 END) AS 'B',
SUM(CASE WHEN OPERATIE = 0 AND COL = 2 THEN VALOARE ELSE 0 END) AS 'C',
SUM(CASE WHEN OPERATIE = 1 AND COL = 2 THEN VALOARE ELSE 0 END) AS 'D'
FROM (SELECT N.DATADOC, N.NRDOC, S.OPERATIE, S.COL, SUM(N.LEI) AS VALOARE
FROM dbo.NOTE AS N INNER JOIN dbo.SCHEME_RG AS S ON N.CONTDB LIKE S.CONTDB AND N.CONTCR LIKE S.CONTCR
WHERE (N.DATADOC BETWEEN '20080901' AND '20080901')
GROUP BY N.DATADOC, N.NRDOC, S.OPERATIE, S.COL) AS X
GROUP BY DATADOC, NRDOC, OPERATIE, COL
ORDER BY 'Data', 'Numar','A' desc
|
|