April 28, 2008 at 3:06 pm
This has been working base on the #DateList table (thanks) but what how would I combined the following two recordsets into a single record set:
SELECT d.DateValue AS DateofRange , Count(Distinct c.ConsultantID) AS SignedCount
FROM #DateList d LEFT JOIN Consultant c ON d.DateValue=c.NACDate
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue
ORDER BY d.DateValue
SELECT
d.DateValue AS DateofRange ,
Count(c.DeactivationDate) AS 'ResignationsCount'
FROM #DateList d
LEFT JOIN Consultant c
ON d.DateValue=c.DeactivationDate
AND c.StatusID = '72HOURRESIGNATION'
AND c.Active = 0
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue
ORDER BY d.DateValue
DROP TABLE #DateList
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 28, 2008 at 3:10 pm
You could either join them or union them to put them together. Which are you looking for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 3:14 pm
I think I need to UNion them because the final reslut set should be some like
DateOfRange, SignedCount(1st script), ResigniationCount (2nd script)
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 28, 2008 at 3:23 pm
In that case, just add a Union All between the two queries, as written. I'd recommend adding a Category or Type column to each, so that you can tell which numbers are which.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 3:28 pm
I am not sure that gives me what I want. The records set needs to be 'flat'. When I use the UNION ALL it just returns everything in two columns and not 3 that I need.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 28, 2008 at 4:33 pm
You can use the function on the link below to create a date table for any range of dates you need.
You can either use it like a table in your query, or use it to load another table.
Date Table Function F_TABLE_DATE
April 29, 2008 at 7:36 am
Sorry, Art. Didn't read the second sentence where you answered me about the Union vs Join.
What you want to do is Join them.
Turn both in derived tables (I recommend CTEs for this), then join them.
Something like:
;with
SCount as
(SELECT d.DateValue AS DateofRange , Count(Distinct c.ConsultantID) AS SignedCount
FROM #DateList d LEFT JOIN Consultant c ON d.DateValue=c.NACDate
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue),
RCount as
(SELECT
d.DateValue AS DateofRange ,
Count(c.DeactivationDate) AS 'ResignationsCount'
FROM #DateList d
LEFT JOIN Consultant c
ON d.DateValue=c.DeactivationDate
AND c.StatusID = '72HOURRESIGNATION'
AND c.Active = 0
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue)
select scount.DateofRange, SignedCount, ResignationsCount
from scount
inner join rcount
on scount.dateofrange = rcount.dateofrange
ORDER BY scount.dateofrange
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 7:54 am
GSquared (4/28/2008)
create table dbo.Numbers (
Number int identity (0, 1) primary key,
Junk bit)
go
insert into dbo.Numbers (junk)
select top 10000 0
from sys.all_objects s1
cross join sys.all_objects s2
go
alter table dbo.Numbers
drop column junk
go
select dateadd(day, number, '1/1/1900')
from dbo.Numbers
That will create a "Numbers" table for you, with 10,000 rows in it.
I keep two versions: Numbers (0-10,000), and BigNumbers (0-100,000,000). Numbers is good for most uses, and BigNumbers handles the ones that Numbers won't. (A cross-join of Numbers to Numbers with Row_Number() would do the same thing, but less efficiently.)
The last select will give you every day from 1/1/1900 to 19 May 1927 (that's 10-thousand days). If you go up to 100-thousand days, it goes from 1/1/1900 to 16 Oct 2173. 1-million goes to 28 Nov 4637, which will certainly be enough for any normal use.
Heh... that explains a lot of things in the past for me, Gus. If you really want some speed out of numbers table solutions, you need to add a clustered primary key to the number column... like this...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--Jeff Moden
April 29, 2008 at 8:49 am
;with
SCount as
(SELECT d.DateValue AS DateofRange , Count(Distinct c.ConsultantID) AS SignedCount
FROM #DateList d LEFT JOIN Consultant c ON d.DateValue=c.NACDate
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue),
RCount as
(SELECT
d.DateValue AS DateofRange ,
Count(c.DeactivationDate) AS 'ResignationsCount'
FROM #DateList d
LEFT JOIN Consultant c
ON d.DateValue=c.DeactivationDate
AND c.StatusID = '72HOURRESIGNATION'
AND c.Active = 0
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue)
select scount.DateofRange, SignedCount, ResignationsCount
from scount
inner join rcount
on scount.dateofrange = rcount.dateofrange
ORDER BY scount.dateofrange
This is so cool. I take it I can just keep adding to this for my other counts?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 29, 2008 at 8:56 am
Yep.
If you have a lot of rows being run at once, or if it's a lot of counts, you might be better off using a temp table than using a whole bunch of CTEs. If you try the CTEs (this solution), and it's too slow, try temp tables instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 9:05 am
If I have to use multiple temp tables, how would I join them together in order to get one flat recordset back?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 29, 2008 at 9:41 am
I went with the seperate temp tables and did a inner join to link them all together in a single record set. But I have one last issue:
Select t1.DateOfRange, t1.SignedCount - t2.ResignationsCount As NetSignedCount
INTO #NetSigned FROM #SignedCount t1
LEFT OUTER JOIN #ResignCounts t2 ON t1.DateofRange = t2.DateOfRange
GROUP BY t1.DateofRange,t1.SignedCount,t2.ResignationsCount
I need to include a running total based on the NetSignedCount to be carried forward for each day in the date range:
Something like:
Date NetsignedCount RunningTotal
Day 1 234 234
Day 2 10 254
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 29, 2008 at 11:31 am
Jeff Moden recently posted an article on the front page of this site about calculating running totals. That should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 1:10 pm
Thanls for the resource. This is what I came up based on it.
Select s.DateOfRange, s.SignedCount,r.ResignationsCount,n.NetSignedCount,
(
SELECT SUM(n2.NetSignedCount)
FROM #NetSigned n2
WHERE N2.DateOfRange <= n.DateOfRange
) as RunningTotal,i.InactiveCount, a.ReactivateCount
FROM #SignedCount s
inner join #ResignCounts r
on s.dateofrange = r.dateofrange
inner join #NetSigned n
on s.DateofRange =n.DateofRange
inner join #InactiveCounts i
on s.DateOfRange = i.dateofrange
inner join #ReactivateCounts a
on s.DateofRange = a.DateofRange
GROUP BY
s.DateOfRange, s.SignedCount,r.ResignationsCount,n.NetSignedCount,
i.InactiveCount, a.ReactivateCount,n.DateofRange
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 29, 2008 at 1:38 pm
That way of getting the running total will work, but it is a slow way to do it. There's another way, using an inline variable, that was in the article. It's significantly faster.
If the inline sub-query gets you what you need, okay. But do realize it will slow the query down.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply