我怎么做这个查询 [英] how I Can Do This Query

问题描述

我有Tow Tables它们的定义是

  CREATE  表 [dbo]。[LEAVS_DEF]（
 [Id]  INT   IDENTITY （ 1 ， 1 ） NOT   NULL ，
 [LEAV_DESCRIPTIONS]  NVARCHAR （ 200 ） NOT   NULL ，
 [ IS_HOURS]  BIT   DEFAULT （（ 0 ） ） NOT   NULL ，
 [IS_DAYS]  BIT   DEFAULT （（ 0 ）） NOT   NULL ，
 [IS_FORMAL_MISSION]  BIT   DEFAULT （（ 0 ）） NOT   NULL ，
 [ AFFECT_SALARY_PERCENT]  NUMERIC （ 3 ） DEFAULT （ （ 0 ）） NOT   NULL ，
 [UNJUSTIFIED]  BIT   DEFAULT （（ 0 ）） NOT   NULL ，
 [USER_NAME]  NVARCHAR （ 100 ）  NOT   NULL ，
 [ALTER_DATE] DATETIME2（ 7 ） NOT   NULL ，
 [IS_SICK]  BIT   DEFAULT （（ 0 ）） NOT   NULL ，
  PRIMARY   KEY   CLUSTERED （[Id]  ASC ）
）; 
 
  CREATE   TABLE  [dbo]。[LEAVS_REQUESTS]（
 [Id]  INT   IDENTITY （ 1 ，  1 ） NOT   NULL ，
 [EMP_NO]  INT   NOT   NULL ， 
 [LEEAV_DEF]  INT   NOT   NULL ，
 [START_DATE]  DATE   NULL ，
 [START_TIME]  TIME （ 7 ） NULL ，
 [DAYS_NUMBER]  NUMERIC （ 3 ） NULL ，
 [TIME_LENGTH]  TIME （ 7 ） NULL ，
 [IS_EMERGENCY]  BIT   DEFAULT （（ 0 ）） NOT   NULL ，
 [IS_APPROVED]  BIT   DEFAULT （（ 0 ）） NOT   NULL ，
 [LEAVS_REASON]  NVARCHAR （< span class =code-digit> 100 ） NULL ，
 [PRINCIPLE_OPENION]  NVARCHAR （ 200 ）< span class =code-keyword> NULL ，
 [USER_NAME]  NVARCHAR （ 100 ） NOT   NULL ，
 [ALTER_DATE] DATETIME2（ 7 ） NOT   NULL ，
  PRIMARY   KEY   CLUSTERED （[Id]  ASC ），
  CONSTRAINT  [AK_LEAVS_REQUESTS_START_DATE_EMPN_UMBER]  UNIQUE   NONCLUSTERED （[EMP_NO]  ASC ，[START_DATE]  ASC ），
  CONSTRAINT  [FK_LEAVS_REQUESTS_EMPLOYEES]  FOREIGN   ķ EY （[EMP_NO]） REFERENCES  [dbo]。[EMPLOYEES]（[EMP_NUMBER]），
  CONSTRAINT  [FK_LEAVS_REQUESTS_LEAVS_DEF]  FOREIGN   KEY （[LEEAV_DEF]） REFERENCES  [dbo]。[LEAVS_DEF]（[Id]）
）; 

我想通过两个表来组合Qurey结果来自



EMP_NO - 总Is_Sick Leavs - 总IS_FORMAL_MISSION leavs - 总UNJUSTIFIED leavs



如果任何三个字段没有值，则在emp_no行的行中返回0

解决方案

谢谢你Ben J. Boyle你的评论是帮助我的基础

我已经解决了它



我不会说那是最好的方法，但我有很好的结果如果有任何人有一个最好的方式，请提供给我们



完成它第一页

  CREATE   VIEW  [dbo]。[View_EMP_LEV] 
  AS  
 选择 lr.EMP_NO， em.FIRST_NAME + ' ' + em.LAST_NAME  AS  EMPLOYEE，ld.LEAV_DESCRIPTIONS，
 WO.DAY_LENGTH，lr.START_DATE，lr.DAYS_NUMBER，lr.START_TIME，lr.TIME_LENGTH，
 ld.IS_HOURS，ld.IS_DAYS，ld.IS_FORMAL_MISSION，
 lr.IS_APPROVED，lr.IS_EMERGENCY，ld.UNJUSTIFIED，ld.IS_SICK 
 来自 LEAVS_REQUESTS lr，LEAVS_DEF ld，EMPLOYEES em，WORKING_HOURS WO 
 其中 lr.LEEAV_DEF = ld.Id 和 lr.EMP_NO = em.EMP_NUMBER 和 lr.IS_APPROVED = 1 
  AND  WO.Id =（选择 WorkingHoursID  FROM  [dbo]。[GetWorkingHoursID]（lr.EMP_NO，lr.START_DATE））

和第二个

  CREATE  查看 [dbo]。[View_lEAVS_MONTHLY_SUMMARY] 
  AS  
  SELECT  VEL.EMP_NO，VEL.EMPLOYEE，格式（VEL.START_DATE，'  MM-yyyy' ） as  Work_month，
 sum（ case   when  IS_SICK =  1  然后（DATEDIFF（MINUTE， 0 ，VEL.DAY_LENGTH）* VEL.DAYS_NUMBER）+ DATEDIFF（MINUTE， 0 ，VEL.TIME_LENGTH） else   0   end ） as  SICK_LEAVS，
 sum（ case  当 IS_FORMAL_MISSION =  1  然后（DATEDIFF（MINUTE， 0 ，VEL.DAY_LENGTH）* VEL.DAYS_NUMBER）+ DATEDIFF（MINUTE， 0 ，VEL.TIME_LENGTH） else   0   end ） as  FORMAL_LEAVS，
 sum（ case   时UNJUSTIFIED = 1  then （DATEDIFF（MINUTE， 0 ，VEL.DAY_LENGTH）* VEL.DAYS_NUMBER）+ DATEDIFF（MINUTE， 0 ，VEL.TIME_LENGTH） else   0   end ） as  UNJ USTIFIED_LEAVS，
 sum（ case    UNJUSTIFIED = 0  AND  IS_FORMAL_MISSION =  0   AND  IS_SICK =  0  然后（DATEDIFF（MINUTE， 0 ，VEL.DAY_LENGTH）* VEL.DAYS_NUMBER） + DATEDIFF（MINUTE， 0 ，VEL.TIME_LENGTH） else   0   end ） as  JUSTIFIED_LEAVS 
  FROM  View_EMP_LEV VEL 
  GROUP   BY  VEL.EMP_NO，VEL.EMPLOYEE，格式（VEL.START_DATE，'  MM-yyyy'）

I Have Tow Tables the definitions for them is

CREATE TABLE [dbo].[LEAVS_DEF] (
    [Id]                    INT            IDENTITY (1, 1) NOT NULL,
    [LEAV_DESCRIPTIONS]     NVARCHAR (200) NOT NULL,
    [IS_HOURS]              BIT            DEFAULT ((0)) NOT NULL,
    [IS_DAYS]               BIT            DEFAULT ((0)) NOT NULL,
    [IS_FORMAL_MISSION]     BIT            DEFAULT ((0)) NOT NULL,
    [AFFECT_SALARY_PERCENT] NUMERIC (3)    DEFAULT ((0)) NOT NULL,
    [UNJUSTIFIED]           BIT            DEFAULT ((0)) NOT NULL,
    [USER_NAME]             NVARCHAR (100) NOT NULL,
    [ALTER_DATE]            DATETIME2 (7)  NOT NULL,
    [IS_SICK]               BIT            DEFAULT ((0)) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[LEAVS_REQUESTS] (
    [Id]                INT            IDENTITY (1, 1) NOT NULL,
    [EMP_NO]            INT            NOT NULL,
    [LEEAV_DEF]         INT            NOT NULL,
    [START_DATE]        DATE           NULL,
    [START_TIME]        TIME (7)       NULL,
    [DAYS_NUMBER]       NUMERIC (3)    NULL,
    [TIME_LENGTH]       TIME (7)       NULL,
    [IS_EMERGENCY]      BIT            DEFAULT ((0)) NOT NULL,
    [IS_APPROVED]       BIT            DEFAULT ((0)) NOT NULL,
    [LEAVS_REASON]      NVARCHAR (100) NULL,
    [PRINCIPLE_OPENION] NVARCHAR (200) NULL,
    [USER_NAME]         NVARCHAR (100) NOT NULL,
    [ALTER_DATE]        DATETIME2 (7)  NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [AK_LEAVS_REQUESTS_START_DATE_EMPN_UMBER] UNIQUE NONCLUSTERED ([EMP_NO] ASC, [START_DATE] ASC),
    CONSTRAINT [FK_LEAVS_REQUESTS_EMPLOYEES] FOREIGN KEY ([EMP_NO]) REFERENCES [dbo].[EMPLOYEES] ([EMP_NUMBER]),
    CONSTRAINT [FK_LEAVS_REQUESTS_LEAVS_DEF] FOREIGN KEY ([LEEAV_DEF]) REFERENCES [dbo].[LEAVS_DEF] ([Id])
);

I Want To make a Qurey Combined from tow tables the results in the from

EMP_NO -- Total Is_Sick Leavs -- total IS_FORMAL_MISSION leavs -- total UNJUSTIFIED leavs

and if any field of three has no value it returns 0 in the row for the emp_no line

解决方案

Thank you Ben J. Boyle your comment is the base that helped me
I've solved it

I'll not say that is the best way to do it but I've Got My Results well if any one has a best way please provide us with

its done in tow views the first

CREATE VIEW [dbo].[View_EMP_LEV]
	AS 
	select lr.EMP_NO, em.FIRST_NAME + ' ' + em.LAST_NAME AS EMPLOYEE, ld.LEAV_DESCRIPTIONS, 
WO.DAY_LENGTH, lr.START_DATE,lr.DAYS_NUMBER, lr.START_TIME, lr.TIME_LENGTH, 
ld.IS_HOURS, ld.IS_DAYS, ld.IS_FORMAL_MISSION, 
lr.IS_APPROVED, lr.IS_EMERGENCY, ld.UNJUSTIFIED, ld.IS_SICK 
from LEAVS_REQUESTS lr, LEAVS_DEF ld, EMPLOYEES em, WORKING_HOURS WO
where lr.LEEAV_DEF=ld.Id and lr.EMP_NO= em.EMP_NUMBER and lr.IS_APPROVED=1
AND WO.Id= (SELECT WorkingHoursID FROM [dbo].[GetWorkingHoursID](lr.EMP_NO,lr.START_DATE))

and the second

CREATE VIEW [dbo].[View_lEAVS_MONTHLY_SUMMARY]
	AS 
	SELECT VEL.EMP_NO, VEL.EMPLOYEE, format(VEL.START_DATE,'MM-yyyy') as Work_month,
sum(case when IS_SICK = 1 then (DATEDIFF(MINUTE,0,VEL.DAY_LENGTH) * VEL.DAYS_NUMBER) +  DATEDIFF(MINUTE,0,VEL.TIME_LENGTH) else 0 end) as SICK_LEAVS,
sum(case when IS_FORMAL_MISSION = 1 then (DATEDIFF(MINUTE,0,VEL.DAY_LENGTH) * VEL.DAYS_NUMBER) +  DATEDIFF(MINUTE,0,VEL.TIME_LENGTH) else 0 end) as FORMAL_LEAVS,
sum(case when UNJUSTIFIED =1 then (DATEDIFF(MINUTE,0,VEL.DAY_LENGTH) * VEL.DAYS_NUMBER) +  DATEDIFF(MINUTE,0,VEL.TIME_LENGTH) else 0 end) as UNJUSTIFIED_LEAVS,
sum(case when UNJUSTIFIED =0 AND IS_FORMAL_MISSION = 0 AND IS_SICK = 0 then (DATEDIFF(MINUTE,0,VEL.DAY_LENGTH) * VEL.DAYS_NUMBER) +  DATEDIFF(MINUTE,0,VEL.TIME_LENGTH) else 0 end) as JUSTIFIED_LEAVS
FROM View_EMP_LEV VEL
GROUP BY VEL.EMP_NO, VEL.EMPLOYEE, format(VEL.START_DATE,'MM-yyyy')

这篇关于我怎么做这个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！