how to do-table 1 that check table 2 and adding missing dates

Question

Post reply

how to do-table 1 that check table 2 and adding missing dates

midan1

SSCertifiable

Points: 5100
More actions
December 21, 2007 at 6:06 am

#180018

can sql server do this ?
table 1 that check table 2 and adding missing dates
this my employee table
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
-----------------------------------------------------------------------------------
and i need to see the missing dates like this
in table 2
------------------------------------------------------
table 2 (adding missing dates with zero 0 value)
table Employee_all_month
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 0
12345678 12/04/2007 0
12345678 13/04/2007 0
12345678 14/04/2007 0
12345678 15/04/2007 0
12345678 16/04/2007 0
12345678 17/04/2007 0
12345678 18/04/2007 0
12345678 19/04/2007 0
12345678 20/04/2007 0
.................................and adding missing dates with zero 0 until the end of the month
.................................
12345678 31/04/2007 0
98765432 01/04/2007 0
98765432 02/04/2007 0
98765432 03/04/2007 0
98765432 04/04/2007 0
98765432 05/04/2007 0
98765432 06/04/2007 0
98765432 07/04/2007 0
98765432 08/04/2007 0
98765432 09/04/2007 0
..............................and adding missing dates with zero 0 only whre no dates in this month
.......................
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
TNX

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Create a table of dates (as I suggested in answer to another of your questions) and left join it to table1.

John

midan1 SSCertifiable Points: 5100 More actions · Answer 2

TNX men

i try to do it + fiil the zero value :hehe:

the main problem i need to do it to specific month in year

how to do it like this ?

select from tb_all_month (month,year)

and get the month from start to end (1-28) OR (1-30) or (1-31)

and olso is it possible to add empolyee from tb_employee

that don'T appear at all this specific month and give the employee the value zero 0

from start to end of the month

TNX

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I think it would help if you posted some DDL for all relevant tables, because I'm confused about what tables you have and what they're for. Please include foreign key constraints.

As for your other question about including employees who don't appear in the table, make sure you understand what an INNER JOIN, a LEFT OUTER JOIN, a RIGHT OUTER JOIN and a FULL OUTER JOIN are. You can find information in Books Online or on the internet. Post back if there's anything in particular you don't understand.

Good luck

John

midan1 SSCertifiable Points: 5100 More actions · Answer 4

tnx

i have only 3 tables

1) employee_tb

empid name

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

12345678 abc-defg

98765432 hijk-lmno

........ ..........

100 employee

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

2)

tb_shift_type

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

shift TXT_shift

1 morning

2 evening

3 night

0 missing

3)

table employee_shift

table 2

table Employee on work

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

empid basedate shift

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

12345678 01/04/2007 1

12345678 02/04/2007 1

12345678 03/04/2007 1

12345678 04/04/2007 1

12345678 05/04/2007 1

12345678 06/04/2007 1

12345678 07/04/2007 1

12345678 08/04/2007 1

12345678 09/04/2007 1

12345678 10/04/2007 1

98765432 20/04/2007 1

98765432 21/04/2007 3

98765432 22/04/2007 3

98765432 23/04/2007 5

98765432 25/04/2007 4

98765432 26/04/2007 4

98765432 27/04/2007 4

98765432 28/04/2007 4

98765432 30/04/2007 4

4)

i need output in view!! all month +missing dates with zero 0

evry day that the employee is missing in this month

i put for him like this (ID + date + shfit=0 )

and the opposite if the employee is i work

i insert for him

(ID + date + shfit= 1 or 2 or 3)

------

12345678 11/04/2007 0

-------

view _ Employee_all_month

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

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

empid basedate shift

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

12345678 01/04/2007 1

12345678 02/04/2007 1

12345678 03/04/2007 1

12345678 04/04/2007 1

12345678 05/04/2007 1

12345678 06/04/2007 1

12345678 07/04/2007 1

12345678 08/04/2007 1

12345678 09/04/2007 1

12345678 10/04/2007 1

12345678 11/04/2007 0

12345678 12/04/2007 0

12345678 13/04/2007 0

12345678 14/04/2007 0

12345678 15/04/2007 0

12345678 16/04/2007 0

12345678 17/04/2007 0

12345678 18/04/2007 0

12345678 19/04/2007 0

12345678 20/04/2007 0

.................................and adding missing dates with zero 0 until the end of the month

.................................

12345678 31/04/2007 0

98765432 01/04/2007 0

98765432 02/04/2007 0

98765432 03/04/2007 0

98765432 04/04/2007 0

98765432 05/04/2007 0

98765432 06/04/2007 0

98765432 07/04/2007 0

98765432 08/04/2007 0

98765432 09/04/2007 0

..............................and adding missing dates with zero 0 only whre no dates in this month

.......................

98765432 20/04/2007 1

98765432 21/04/2007 3

98765432 22/04/2007 3

98765432 23/04/2007 5

98765432 25/04/2007 4

98765432 26/04/2007 4

98765432 27/04/2007 4

98765432 28/04/2007 4

98765432 30/04/2007 4

TNX

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

Midan,

What John is asking for is covered in the following URL...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

You have a lot of data posted... we should use your data to give you the best answer. As the article I posted suggests, it would be a lot easier to get help if you provided both table creation code and formatted your data for automatic insertion. Most of us help out of the goodness of our hearts and we just don't have the time to manually format your data so we can deliver a fully tested reply.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

If shifts have to be 0, 1, 2 or 3, why do you have 4s and 5s in your view?

Something like this may do it for you. If it doesn't give you exactly what you're looking for, try to tweak it before posting again. It relies on your creating the date table (dates) that I mentioned before.

John

SELECT c.empid, c.mydate, COALESCE(s.shift,0)

FROM (

SELECT mydate, empid FROM dates d

CROSS JOIN tbl_employee t) c

LEFT OUTER JOIN employee_shift s

ON c.mydate = s.basedate AND c.empid = s.empid

ORDER BY c.empid, c.mydate