how I Can Do This Query

Question

1.00/5 (2 votes)

See more:

SQL-Server-2012

I Have Tow Tables the definitions for them is

SQL

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

Posted 9-Jul-15 5:56am

samerselo

Add a Solution

Comments

Michael_Davies 9-Jul-15 12:03pm

What have you tried?

samerselo 9-Jul-15 13:42pm

I can't think of a valid query all what I tried is invalid

Ben J. Boyle 9-Jul-15 14:07pm

How do the two tables link? you table defs won't work for us because you don;t include the employees table.

Here's a quick and dirty query that shows the method I would use to handle the question based on one of your tables:
Select [USER_NAME],
sum(case when IS_SICK = 1 then 1 else 0 end) as is_sick,
sum(case when IS_FORMAL_MISSION = 1 then 1 else 0 end) as is_formal_mission,
sum(case when UNJUSTIFIED =1 then 1 else 0 end) as unjustified
from leavs_def
group by [USER_NAME]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

samerselo · Accepted Answer · 2015-07-09T10:23:00

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

SQL

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

SQL

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')