Referencing two tables - stored procedure

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

Referencing two tables - stored procedure

Author

Topic

rankone
Starting Member

24 Posts

Posted - 2011-06-26 : 00:36:21

Hello Everybody!

I am not sure if this task is easy or complicated. But I need to write a stored procedure that needs to reference a table & I am unsure on how to go about this, I think this could be achieved through a join but I could be wrong.

Scenario:
In my application I have the USER inputting a Start Date & End Date. now this goes into the database no problem, I can sum these two date ranges & populate a column in SQL with the number of days in the same table (Table A), which also isn't a problem. The problem is I don't want to account for weekends & specific individual dates. I have a table (Table B) that has all the information of weekend dates of every month & those individual specific dates as well. What I am trying to achieve is, once the User inputs the Start Date and End Date which gets input into "Table A", the stored procedure populates the column Value ("TotalDays") by doing a sum by referencing the weekends/holidays data in "Table B". So for instance if I pick 7 days, which include a Saturday & Sunday, my sum value should be 5.

I hope someone can help me out or point me in the right direction.
Thanks a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-26 : 06:21:52

You can subtract

your_number_of_Days - (select count(*) from table_b where table_b.datecol >= table_a.StartDate and table_b.datecol <= table_a.EndDate)

If that isn't the solution then please give table structure and sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

rankone
Starting Member

24 Posts

Posted - 2011-06-26 : 11:41:50

Hey webfred,

based on your answer should I populate a column in Table A first & then take those number of days & do use your statement? Such as Table A Column = TotalRawDays
First populate this column in Table A by:

Select datediff(d,Start_Date,End_Date) As TotalRawDays from Table_A.

And then use your query.

TotalRawDays - (select count(*) from table_b where table_b.datecol >= table_a.StartDate and table_b.datecol <= table_a.EndDate)

Would this work? I mean I could also use the weekday function within datediff which would only give me the weekdays & then just subtract out the individual dates too?

Thanks for your response.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-26 : 13:30:00

I meaned something like this:

Select
datediff(d,Start_Date,End_Date) - (select count(*) from table_b where table_b.datecol >= table_a.StartDate and table_b.datecol <= table_a.EndDate) As TotalDays
from Table_A

No, you're never too old to Yak'n'Roll if you're too young to die.

rankone
Starting Member

24 Posts

Posted - 2011-06-27 : 09:38:55

Thanks a ton webfred, this worked! Thanks again

rankone
Starting Member

24 Posts

Posted - 2011-06-27 : 15:06:52

quote:
Originally posted by webfred

I meaned something like this:

Select
datediff(d,Start_Date,End_Date) - (select count(*) from table_b where table_b.datecol >= table_a.StartDate and table_b.datecol <= table_a.EndDate) As TotalDays
from Table_A

No, you're never too old to Yak'n'Roll if you're too young to die.

Turns out I jumped the gun on this. I found as I added more records to the table, this started listing everything & I needed it to only do this function on specific records. Can I add an IF or Case clause to this stating. IF this ColumnName = "YES" then perform this statement?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-27 : 16:20:21

What should be the ELSE to that IF?
Maybe you can give example tables, data and wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

rankone
Starting Member

24 Posts

Posted - 2011-06-27 : 16:44:36

The results should be if the Column Value of a Column in Table A is 'Yes', then the above query should run Else it should just do a select all of Column B in Table B where ID = @ID

rankone
Starting Member

24 Posts

Posted - 2011-06-27 : 19:06:04

Hey webfred,

I got this working by creating variables & using it in an If Statement. Although I had a question, as several records get generated, is there a way to sum everything up as one. As I think the count is counting multiple records & I need one value which is the sum of all these records. I am not using an increment of ID on each record so I cannot use those values. So based on the query, the data would be

------------------------------------
UserID | Type |
--------------------------------------
101 3
101 4
101 1
101 10

I need to sum this to be Total = 18

is there a way to do that?

Asif5566
Starting Member

9 Posts

Posted - 2011-06-27 : 23:41:22

quote:
Originally posted by rankone

Hey webfred,

I got this working by creating variables & using it in an If Statement. Although I had a question, as several records get generated, is there a way to sum everything up as one. As I think the count is counting multiple records & I need one value which is the sum of all these records. I am not using an increment of ID on each record so I cannot use those values. So based on the query, the data would be

------------------------------------
UserID | Type |
--------------------------------------
101 3
101 4
101 1
101 10

I need to sum this to be Total = 18

is there a way to do that?

Use might use ROLL UP Operator:

SELECT UserID, SUM(Type) AS Total
FROM Table
GROUP BY UserID, Type
WITH ROLLUP

rankone
Starting Member

24 Posts

Posted - 2011-06-28 : 10:02:48

The RollUp method didn't work, any other suggestions?

Here's my code:


DECLARE
@UserID int, 
@RandomCol varchar (100), 
SET 
@UserID = 12101
SET
@RandomCol = (Select RandomCol from TableA where UserID = @UserID  AND RandomCol = 'YES') 
IF @RandomCol = 'YES' 
BEGIN 
Select Total - (Select
datediff(d,StartDate,EndDate) - (select count(*)) 
from table_b 
where table_b.datecol >= table_a.StartDate 
and table_b.datecol <= table_a.EndDate) As TotalDays
from Table_A) AS TotalRemain from Table_B
Where UserID = @UserID
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 10:29:07

I am not able to understand your needs - sorry.
Maybe this kind of asking a question can help to make it more clear?

No, you're never too old to Yak'n'Roll if you're too young to die.

rankone
Starting Member

24 Posts

Posted - 2011-06-28 : 11:03:53

Hey well you have my code, with the sample data structure I posted as well & in the code all the table variables are defined. The Output would be

------------------------------
USERID | Total
12101 18

That's what I am looking for, because right now the subquery is returning multiple results

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 11:11:01

select UserId, sum(Total) as Total from
(your_existing_query_comes_here) as dt
group by UserId

No, you're never too old to Yak'n'Roll if you're too young to die.

rankone
Starting Member

24 Posts

Posted - 2011-06-28 : 11:14:22

I am confused as to where in the query would this be inputted at? If you could point that out it would be helpful. Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 12:08:02

I am confused too.

You said your output is this:
------------------------------
USERID | Total
12101 18

I can't see any select statement in this whole thread where USERID is a selected column.
So what else can I do but guessing?

Sorry but I am done.
Maybe another mate is able to help here...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources