what is the best way to do this?>

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

SQL Server 2008 Forums

SQL Server Administration (2008)

what is the best way to do this?>

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 10:58:22

I have stored procedure where I need to count out a bunch of numbers from different tables and different criteria

I now just have it do a count and return the differnet numbers

the problem is I want to do it returning a table that I can group by either date or name (both are part of the criteria I am searching by

what is the best way to do this?
a temporary table? how can I fill it in by date and name and then add a bunch of other criteria (which includes counting other fields )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 11:01:48

i feel like you need a temporary table. hope all your tables have date and name fields

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 11:35:23

I think I also need a temp table

but how would I do it by filling in different date with name and date

for example
if i have

CREATE TABLE #t (
totalsignups int,
totalrec int,
totalrem int,
mydate int,
name int
)

then I want to
set all of totalsignups=count(id) from signups where name=name and date=date (name and date different for each row in temp table)

and then the same for the next
set totalrec=count(id) from recs where name=name and date=date (name and date different for each row in temp table)

etc.

what's the best way to end up with a table that I can either group by name or date but has all the different counts in it.

I hope this clarifies my question

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 13:17:17

something like


UPDATE t
SET t.totalsignups = tmp.signupcnt
FROM #t t
INNER JOIN (SELECT date,name,COUNT(id) AS signupcnt FROM signups GROUP BY date,name)tmp
ON t.mydate = tmp.date
AND t.name = tmp.name

UPDATE t
SET t.totalrec =tmp.reccnt
FROM #t t
INNER JOIN (SELECT date,name,COUNT(id) AS reccnt FROM recs GROUP BY date,name)tmp
ON t.mydate = tmp.date
AND t.name = tmp.name
.....

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 13:38:59

i tried this but how do I populate the temp table as it's returning no rows

also I need an individual count for each name and count

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 13:41:22

or to make it easier if I remove the names and just want to get a number for every date (without time) passed in - how would I do this?

so my final result would be

date totalrec totalsignups totalrem

and show a list by date based on the date?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 13:53:14

quote:
Originally posted by esthera

or to make it easier if I remove the names and just want to get a number for every date (without time) passed in - how would I do this?

so my final result would be

date totalrec totalsignups totalrem

and show a list by date based on the date?

it will be similar to above query with only difference being removing name field from everywhere.
b/w you need to first populate the temp table with date values before doing the set of updates as specified above

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 14:01:31

but how do I populate with date values
say I have a fromdate and todate

how do I populate with all the dates and then do the update (and update should be based on date part only and not time)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 14:06:40

see the below link on generating a calendar within date range. use this to populate your table

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

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-30 : 07:54:32

this worked great - thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 09:56:34

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources