Making multiple queries as single query?

Question

Post reply

Making multiple queries as single query?

krishna-439630

SSC Journeyman

Points: 99
More actions
May 13, 2007 at 11:32 pm

#124051

Hi I have the following query with me.
Can one help in formatting this query as single query.
Thanks for help.

/* x days old : Creating Table for building the working days between given two dates*/

/*Drop Table working_days;*/
Create Table working_days As(
Select
cast(cast(N516AA_REC_DATE_CCYYMMDD As date format 'yyyymmdd') As date format 'yyyy-mm-dd') start_date, CURRENT_DATE end_date, (CURRENT_DATE - cast(cast(N516AA_REC_DATE_CCYYMMDD As date format 'yyyymmdd') As date format 'yyyy-mm-dd') )+1 no_of_days, no_of_days / 7 * 5 working_days /*excluding sat and sun*/, no_of_days mod 7 remain_days,
Case
When remain_days=0
Then CURRENT_DATE-remain_days
When remain_days In (1,2,3,4,5,6,7)
Then CURRENT_DATE-remain_days+1
End As check_date
From krishna
)with data primary index(start_date)
;

/*drop table test ;*/
Create Table test (
start_date date,remain_days integer, check_date date, dow integer,working_days integer
) primary index(start_date)
;
select * from test;
Insert Into test
Select start_date,remain_days, check_date, dayofweek(check_date),working_days
From working_days
;
/*Drop Table test1;*/
Create Table test1 As (
Select
start_date,remain_days, check_date, dow,
Case
When remain_days=0
And dow In (1,7)
Then working_days+remain_days-1
When remain_days=0
And dow In (2,3,4,5,6)
Then working_days+remain_days
When remain_days=1
And dow In (1,7)
Then working_days+remain_days-1
When remain_days=1
And dow In (2,3,4,5,6)
Then working_days+remain_days
When remain_days=2
And dow In (1,6)
Then working_days+remain_days-1
When remain_days=2
And dow=7
Then working_days+remain_days-2
When remain_days=2
And dow In (2,3,4,5)
Then working_days+remain_days
When remain_days=3
And dow In (1,5)
Then working_days+remain_days-1
When remain_days=3
And dow In (7 ,6)
Then working_days+remain_days-2
When remain_days=3
And dow In (2,3,4)
Then working_days+remain_days
When remain_days=4
And dow In (1,4)
Then working_days+remain_days-1
When remain_days=4
And dow In (5,7 ,6)
Then working_days+remain_days-2
When remain_days=4
And dow In (2,3)
Then working_days+remain_days
When remain_days=5
And dow In (1,3)
Then working_days+remain_days-1
When remain_days=5
And dow In (4,5,7 ,6)
Then working_days+remain_days-2
When remain_days=5
And dow=2
Then working_days+remain_days
When remain_days=6
And dow In (1,2)
Then working_days+remain_days-1
When remain_days=6
And dow In (3,4,5,7 ,6)
Then working_days+remain_days-2
End As wd
From test)
with data
Primary Index(start_date)
;

Select a.start_date,a.end_date,c.wd
From working_days a Left Join test b
On a.start_date=b.start_date
Left Join test1 c
On b.start_date=c.start_date
;

Regards,
Krishna

Viewing 5 posts - 1 through 4 (of 4 total)

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

bledu SSChampion Points: 12335 More actions · Answer 1

if you want to get the working days between two dates, you can have a look at this thread

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=211734

and see if helps, if not then let us know, and we will take it from there

Everything you can imagine is real.

krishna-439630 SSC Journeyman Points: 99 More actions · Answer 2

Hi Veteran,

it was just look good.

but my prob is i should not use any proc.

so for that reason i wrote a big query for finding the working days.

can u help for my question now??

bledu SSChampion Points: 12335 More actions · Answer 3

hey, can you annotate what the code is supposed to be doing because I am failing to understand notes like [/*drop table test ;*/]

where are you running this script i.e. in the application or somewhere else and can i assume that you are using sql 2000

Everything you can imagine is real.

krishna-439630 SSC Journeyman Points: 99 More actions · Answer 4

hi,

Ignore [/*drop table test ;*/]

i using Teradata.

Thanks