While Loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

While Loop

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-03-09 : 07:29:18

Dear All,
I ahev to create procedure in which i have to insert some rows in table.Table structure is Work_Date,Emp_Id.If user is passing a three parameter month,year,emp_id then in this procedure insert statement should run so suppose i have massed this parameter
3,2010,'ABC'
so in table it should store like this

03/01/2010 - ABC
03/02/2010 - ABC
03/03/2010 - ABC
03/04/2010 - ABC
till
03/31/2010 - ABC

please help

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-09 : 07:44:09

This could be probably be done more efficiently, but this way is pretty clear. I'll leave it to you to adapt to exactly what you need
Jim

DECLARE @month int
DECLARE @year int
DECLARE @value char(3)

DECLARE @monthStart datetime
DECLARE @monthEnd datetime

SET @month = 3
SET @year = 2010
SET @value = 'ABC'

SET @monthStart = DATEADD(month,@month-1,DATEADD(yy,@year-1900,0))
SET @monthEnd = DATEADD(month,@month,DATEADD(yy,@year-1900,0))

SELECT DATEADD(day,spt.Number,@monthStart),@value
FROM master..spt_values spt
WHERE type= 'p' and DATEADD(day,spt.Number,@monthStart) < @monthEnd

Everyday I learn something that somebody else already knew

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-09 : 08:17:31

DECLARE @month int
DECLARE @year int
DECLARE @value char(3)

DECLARE @monthStart datetime
DECLARE @monthEnd datetime

SET @month = 3
SET @year = 2010
SET @value = 'ABC'

SET @monthStart = CONVERT(DATETIME, CAST(@Month AS VARCHAR(2)) + '/01/' + CAST(@year AS VARCHAR(4)))
SET @monthEnd = DATEADD(d, -1, DATEADD(m,1,@MonthStart))

SELECT CONVERT(DATETIME, CAST(@Month AS VARCHAR(2)) + '/' + CAST(Number AS VARCHAR(2)) + '/' + CAST(@year AS VARCHAR(4))) ,@value
FROM master..spt_values spt
WHERE type= 'p' and Number BETWEEN DATEPART(day,@monthStart) AND DATEPART(day,@monthEnd)

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 08:56:12

Generate actual dates and do the formation at the front end application (If you use it)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:19:01

you could use the below table for that


DECLARE @month int,@year int,@emp_id varchar(10)
SELECT @month=3,@year=2010,@emp_id='ABC'--your values

SELECT f.Date,@emp_id AS emp_id
FROM dbo.CalendarTable(DATEADD(mm,@month-1,DATEADD(yy,@year-1900,0)),DATEADD(mm,@month,DATEADD(yy,@year-1900,0))-1,0)f

output
-----------------------------
Date	emp_id
2010-03-01 00:00:00.000	ABC
2010-03-02 00:00:00.000	ABC
2010-03-03 00:00:00.000	ABC
2010-03-04 00:00:00.000	ABC
2010-03-05 00:00:00.000	ABC
2010-03-06 00:00:00.000	ABC
2010-03-07 00:00:00.000	ABC
2010-03-08 00:00:00.000	ABC
2010-03-09 00:00:00.000	ABC
2010-03-10 00:00:00.000	ABC
2010-03-11 00:00:00.000	ABC
2010-03-12 00:00:00.000	ABC
2010-03-13 00:00:00.000	ABC
2010-03-14 00:00:00.000	ABC
2010-03-15 00:00:00.000	ABC
2010-03-16 00:00:00.000	ABC
2010-03-17 00:00:00.000	ABC
2010-03-18 00:00:00.000	ABC
2010-03-19 00:00:00.000	ABC
2010-03-20 00:00:00.000	ABC
2010-03-21 00:00:00.000	ABC
2010-03-22 00:00:00.000	ABC
2010-03-23 00:00:00.000	ABC
2010-03-24 00:00:00.000	ABC
2010-03-25 00:00:00.000	ABC
2010-03-26 00:00:00.000	ABC
2010-03-27 00:00:00.000	ABC
2010-03-28 00:00:00.000	ABC
2010-03-29 00:00:00.000	ABC
2010-03-30 00:00:00.000	ABC
2010-03-31 00:00:00.000	ABC

dbo.CalendarTable can be found below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources