IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
               WHERE SYSCOLUMNS.NAME = 'SANADEPOID' AND SYSOBJECTS.name = 'STOKSUBE')
ALTER TABLE dbo.STOKSUBE ADD
	SANADEPOID int NULL
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_STOKSUBE_DEPO]') AND parent_object_id = OBJECT_ID(N'[dbo].[STOKSUBE]'))
ALTER TABLE [dbo].[STOKSUBE]  WITH CHECK ADD  CONSTRAINT [FK_STOKSUBE_DEPO] FOREIGN KEY([SANADEPOID])
REFERENCES [dbo].[DEPO] ([DEPOID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_STOKSUBE_DEPO]') AND parent_object_id = OBJECT_ID(N'[dbo].[STOKSUBE]'))
ALTER TABLE [dbo].[STOKSUBE] CHECK CONSTRAINT [FK_STOKSUBE_DEPO]
GO

UPDATE TBIRIM SET DEPOID = NULL FROM TBIRIM WHERE DEPOID NOT IN (SELECT DEPOID FROM DEPO)
GO

UPDATE TBIRIM SET ANADEPOIDILAC = NULL FROM TBIRIM WHERE ANADEPOIDILAC NOT IN (SELECT DEPOID FROM DEPO)
GO

UPDATE TBIRIM SET ANADEPOIDMALZ = NULL FROM TBIRIM WHERE ANADEPOIDMALZ NOT IN (SELECT DEPOID FROM DEPO)
GO

UPDATE    STOKSUBE
SET              SANADEPOID = q.SANADEPOID
FROM         STOK INNER JOIN
                      TITEM ON STOK.STOKID = TITEM.STOKID INNER JOIN
                      STOKSUBE ON STOK.STOKID = STOKSUBE.STOKID INNER JOIN
                          (SELECT     QSUBEDEPO.SUBEID, QSUBEDEPO.SANADEPOID, QSUBEDEPO.SAYI
                            FROM          (SELECT     SUBEID, ANADEPOIDILAC AS SANADEPOID, COUNT(*) AS SAYI
                                                    FROM          TBIRIM
                                                    WHERE      (ANADEPOIDILAC IS NOT NULL)
                                                    GROUP BY SUBEID, ANADEPOIDILAC) AS QSUBEDEPO INNER JOIN
                                                       (SELECT     SUBEID, MAX(SAYI) AS MAKSSAYI
                                                         FROM          (SELECT     SUBEID, COUNT(*) AS SAYI
                                                                                 FROM          TBIRIM
                                                                                 WHERE      (ANADEPOIDILAC IS NOT NULL)
                                                                                 GROUP BY SUBEID, ANADEPOIDILAC) AS QBIRIMANADEPO
                                                         GROUP BY SUBEID) AS QSUBEMAKSSAYI ON QSUBEDEPO.SUBEID = QSUBEMAKSSAYI.SUBEID AND QSUBEDEPO.SAYI = QSUBEMAKSSAYI.MAKSSAYI) AS Q ON 
                      STOKSUBE.STOKSUBEID = Q.SUBEID AND STOKSUBE.STOKID = STOK.STOKID
WHERE     (TITEM.ITEMTURUID = 3) AND (STOKSUBE.STOKID > 0) AND (STOKSUBE.SANADEPOID IS NULL)
GO

INSERT INTO    STOKSUBE (SUBEID, STOKID, SANADEPOID, SERIBARKODKULLANILIR)
SELECT DISTINCT Q.SUBEID, STOK.STOKID, q.SANADEPOID, STOK.SERIBARKODKULLANILIR
FROM         (SELECT     QSUBEDEPO.SUBEID, QSUBEDEPO.SANADEPOID, QSUBEDEPO.SAYI
                            FROM          (SELECT     SUBEID, ANADEPOIDILAC AS SANADEPOID, COUNT(*) AS SAYI
                                                    FROM          TBIRIM
                                                    WHERE      (ANADEPOIDILAC IS NOT NULL)
                                                    GROUP BY SUBEID, ANADEPOIDILAC) AS QSUBEDEPO INNER JOIN
                                                       (SELECT     SUBEID, MAX(SAYI) AS MAKSSAYI
                                                         FROM          (SELECT     SUBEID, COUNT(*) AS SAYI
                                                                                 FROM          TBIRIM
                                                                                 WHERE      (ANADEPOIDILAC IS NOT NULL)
                                                                                 GROUP BY SUBEID, ANADEPOIDILAC) AS QBIRIMANADEPO
                                                         GROUP BY SUBEID) AS QSUBEMAKSSAYI ON QSUBEDEPO.SUBEID = QSUBEMAKSSAYI.SUBEID AND QSUBEDEPO.SAYI = QSUBEMAKSSAYI.MAKSSAYI) AS Q CROSS JOIN
				      STOK INNER JOIN
                      TITEM ON STOK.STOKID = TITEM.STOKID LEFT OUTER JOIN 
                      STOKSUBE ON STOK.STOKID = STOKSUBE.STOKID AND STOKSUBE.SUBEID = Q.SUBEID
WHERE     (TITEM.ITEMTURUID = 3) AND (STOK.STOKID > 0) AND (STOKSUBE.STOKID IS NULL)
GO

UPDATE    STOKSUBE
SET              SANADEPOID = q.SANADEPOID
FROM         STOK INNER JOIN
                      TITEM ON STOK.STOKID = TITEM.STOKID INNER JOIN
                      STOKSUBE ON STOK.STOKID = STOKSUBE.STOKID INNER JOIN
                          (SELECT     QSUBEDEPO.SUBEID, QSUBEDEPO.SANADEPOID, QSUBEDEPO.SAYI
                            FROM          (SELECT     SUBEID, ANADEPOIDMALZ AS SANADEPOID, COUNT(*) AS SAYI
                                                    FROM          TBIRIM
                                                    WHERE      (ANADEPOIDMALZ IS NOT NULL)
                                                    GROUP BY SUBEID, ANADEPOIDMALZ) AS QSUBEDEPO INNER JOIN
                                                       (SELECT     SUBEID, MAX(SAYI) AS MAKSSAYI
                                                         FROM          (SELECT     SUBEID, COUNT(*) AS SAYI
                                                                                 FROM          TBIRIM
                                                                                 WHERE      (ANADEPOIDMALZ IS NOT NULL)
                                                                                 GROUP BY SUBEID, ANADEPOIDMALZ) AS QBIRIMANADEPO
                                                         GROUP BY SUBEID) AS QSUBEMAKSSAYI ON QSUBEDEPO.SUBEID = QSUBEMAKSSAYI.SUBEID AND QSUBEDEPO.SAYI = QSUBEMAKSSAYI.MAKSSAYI) AS Q ON 
                      STOKSUBE.STOKSUBEID = Q.SUBEID AND STOKSUBE.STOKID = STOK.STOKID
WHERE     (TITEM.ITEMTURUID = 5) AND (STOKSUBE.STOKID > 0) AND (STOKSUBE.SANADEPOID IS NULL)
GO

INSERT INTO    STOKSUBE (SUBEID, STOKID, SANADEPOID, SERIBARKODKULLANILIR)
SELECT DISTINCT Q.SUBEID, STOK.STOKID, q.SANADEPOID, STOK.SERIBARKODKULLANILIR
FROM         (SELECT     QSUBEDEPO.SUBEID, QSUBEDEPO.SANADEPOID, QSUBEDEPO.SAYI
                            FROM          (SELECT     SUBEID, ANADEPOIDMALZ AS SANADEPOID, COUNT(*) AS SAYI
                                                    FROM          TBIRIM
                                                    WHERE      (ANADEPOIDMALZ IS NOT NULL)
                                                    GROUP BY SUBEID, ANADEPOIDMALZ) AS QSUBEDEPO INNER JOIN
                                                       (SELECT     SUBEID, MAX(SAYI) AS MAKSSAYI
                                                         FROM          (SELECT     SUBEID, COUNT(*) AS SAYI
                                                                                 FROM          TBIRIM
                                                                                 WHERE      (ANADEPOIDMALZ IS NOT NULL)
                                                                                 GROUP BY SUBEID, ANADEPOIDMALZ) AS QBIRIMANADEPO
                                                         GROUP BY SUBEID) AS QSUBEMAKSSAYI ON QSUBEDEPO.SUBEID = QSUBEMAKSSAYI.SUBEID AND QSUBEDEPO.SAYI = QSUBEMAKSSAYI.MAKSSAYI) AS Q CROSS JOIN
				      STOK INNER JOIN
                      TITEM ON STOK.STOKID = TITEM.STOKID LEFT OUTER JOIN 
                      STOKSUBE ON STOK.STOKID = STOKSUBE.STOKID AND STOKSUBE.SUBEID = Q.SUBEID
WHERE     (TITEM.ITEMTURUID = 5) AND (STOK.STOKID > 0) AND (STOKSUBE.STOKID IS NULL)
GO