combine two queries?

[jordanking]

Hello,

I have two queries that I want to combine the results from. They are both based on the same SQL View but one invloves grouping and the other is a flat result set:

QUERY # 1 ************

SELECT 
	(ROW_NUMBER() OVER (PARTITION BY intDriverID ORDER BY intDriverID, intREGENT)) AS intStopNum
	, intResidential
	, dtServiceDate
	, sintSigns
	, chrProdInstruc
	, chrCompany
	, chrCustomer
	, chrCustomerDetail
	, intPhoneCode
	, chrAddress
	, intDriverID
	, chrServiceCode 
	, intMLS
	, intREGENT
	, chrRegion
	, chrName
	, chrPostCode
	, chrQuadrant
	, chrDriverName
	, ISNULL(intRemoveService,0)
FROM  dbo.vw_Residential_Driver_Lists 
WHERE dtServiceDate >= '2007-03-07' AND dtServiceDate < '2007-03-08' AND intRemoveService > 0

RESULTS INsome fields were removed from following result set to make it easier to view)

intStopNum           intResidential sintSigns chrName              chrPostCode        chrDriverName            intRemoveService        
-------------------- -------------- --------- -------------------- ------------------ ------------------------ ----------------
1                    65298          1         Residential          WT                 Scott                    8
2                    65336          1         Residential          BPP                Scott                    8
3                    65342          1         Residential          YPP                Scott                    8
4                    65300          1         Residential          WT                 Scott                    8
5                    65280          1         Residential          WT                 Scott                    8
6                    65368          1         Residential          WPP                Scott                    8
7                    65356          1         Residential          WT                 Scott                    8
8                    65359          1         Residential          BS                 Scott                    8
9                    65309          1         Residential          WT                 Scott                    8
10                   65253          1         Residential          WPP                Scott                    8
11                   65246          1         Residential          WT                 Scott                    8
...(records removed to shorten representaion)
1                    65313          1         Residential          WPP                Brian Seals              44
2                    65297          1         Residential          WT                 Brian Seals              8
3                    65393          1         Residential          WS                 Brian Seals              8
4                    65366          1         Residential          WT                 Brian Seals              8
5                    65350          1         Residential          WPP                Brian Seals              8
...
1                    65291          1         Residential          BS                 Grant                    8
2                    65289          1         Residential          BS                 Grant                    8
3                    65290          1         Residential          BS                 Grant                    8
4                    65278          1         Residential          BS                 Grant                    8
21                   64562          1         Residential          BS                 Grant                    44
...
1                    65324          1         Residential          YPP                Dale                     8
2                    65314          1         Residential          BS                 Dale                     8
3                    65276          1         Residential          WT                 Dale                     8
4                    65272          1         Residential          WT                 Dale                     8
5                    65365          1         Residential          BS                 Dale                     8
6                    65371          1         Residential          WPP                Dale                     8

QUERY # 2 **************

SELECT   chrDriverName, chrPostCode AS chrCode, COUNT(chrPostCode) AS intCount
FROM     vw_Residential_Driver_Lists
WHERE    (dtServiceDate >= '2007-03-07') AND (dtServiceDate < '2007-03-08') AND intRemoveService > 0
GROUP BY chrPostCode, chrDriverName
ORDER BY chrDriverName, chrCode

RESULTS IN

chrDriverName                                      chrCode                                            intCount
-------------------------------------------------- -------------------------------------------------- -----------
Brian Seals                                        WPP                                                2
Brian Seals                                        WS                                                 1
Brian Seals                                        WT                                                 2
Dale                                               BS                                                 7
Dale                                               WPP                                                5
Dale                                               WT                                                 7
Dale                                               YPP                                                2
Grant                                              BS                                                 13
Grant                                              WPP                                                5
Grant                                              WT                                                 3
Scott                                              BPP                                                1
Scott                                              BS                                                 4
Scott                                              WPP                                                8
Scott                                              WT                                                 8
Scott                                              YPP                                                1

I want some way to include the second query's resluts in the first so it would look like:

intNumStop    intResidential  ...(all other fields from first query)...  WPP        WT       YPP         BS         WS
------------- ----------------     ------------ ---------- ------------  ---------  -------- ----------  -------   ---------
1             1234                                         scott         1           2        4           5         0
1             1689                                         scott         1           2        4           5         0
1             1899                                         scott         1           2        4           5         0
1             1534                                         grant         0           3        4           0         0
1             2357                                         grant         0           3        4           0         0
1             8794                                         dale          2           1        0           2         1

where chrCode and intCount from the second query are somehow grouped with the chrDrivername from the first query. I don't care if the result from the second query is displayed more than once (like for each line of the respective "chrDriveName").

.....
I'd rather be surfing

 

[jordanking]

hello again,

well after almost 4 hours, i used a new function in sql server 2005 to pivot the data and then used a derived table to attach the results

SELECT 
	(ROW_NUMBER() OVER (PARTITION BY dbo.vw_Residential_Driver_Lists.intDriverID ORDER BY dbo.vw_Residential_Driver_Lists.intDriverID, dbo.vw_Residential_Driver_Lists.intREGENT)) AS intStopNum
	, dbo.vw_Residential_Driver_Lists.intResidential
	, dbo.vw_Residential_Driver_Lists.dtServiceDate
	, dbo.vw_Residential_Driver_Lists.sintSigns
	, dbo.vw_Residential_Driver_Lists.chrProdInstruc
	, dbo.vw_Residential_Driver_Lists.chrCompany
	, dbo.vw_Residential_Driver_Lists.chrCustomer
	, dbo.vw_Residential_Driver_Lists.chrCustomerDetail
	, dbo.vw_Residential_Driver_Lists.intPhoneCode
	, dbo.vw_Residential_Driver_Lists.chrAddress
	, dbo.vw_Residential_Driver_Lists.intDriverID
	, dbo.vw_Residential_Driver_Lists.chrServiceCode 
	, dbo.vw_Residential_Driver_Lists.intMLS
	, dbo.vw_Residential_Driver_Lists.intREGENT
	, dbo.vw_Residential_Driver_Lists.chrRegion
	, dbo.vw_Residential_Driver_Lists.chrName
	, dbo.vw_Residential_Driver_Lists.chrPostCode
	, dbo.vw_Residential_Driver_Lists.chrQuadrant
	, dbo.vw_Residential_Driver_Lists.chrDriverName
	, ISNULL(dbo.vw_Residential_Driver_Lists.intRemoveService,0)
	, PostGroups.chrDriverName AS chrName2
	, PostGroups.BS
	, PostGroups.WS
	, PostGroups.WPP
	, PostGroups.YPP
	, PostGroups.BPP
	, PostGroups.WT
	, PostGroups.LWT
	, PostGroups.HLP
	, PostGroups.WPPINFO
	, PostGroups.WPPT
	, (PostGroups.BS + PostGroups.WS + PostGroups.WPP + PostGroups.YPP + PostGroups.BPP + PostGroups.WT + PostGroups.LWT + PostGroups.HLP + PostGroups.WPPINFO + PostGroups.WPPT) AS intNumUp
FROM  dbo.vw_Residential_Driver_Lists 
	INNER JOIN
	(
	SELECT chrDriverName, [BS], [WS], [WPP], [YPP], [BPP], [WT], [LWT], [HLP], [WPPINFO], [WPPT] 
	FROM     
		(
		SELECT	dbo.vw_Residential_Driver_Lists.chrPostCode, dbo.vw_Residential_Driver_Lists.chrDriverName 
		FROM	dbo.vw_Residential_Driver_Lists
		WHERE	(dbo.vw_Residential_Driver_Lists.dtServiceDate >= '2007-03-07') AND (dbo.vw_Residential_Driver_Lists.dtServiceDate < '2007-03-08') AND dbo.vw_Residential_Driver_Lists.intRemoveService > 0
		) AS SourceTable
	PIVOT
		(
		COUNT(chrPostCode)
		FOR chrPostCode IN
		([BS], [WS], [WPP], [YPP], [BPP], [WT], [LWT], [HLP], [WPPINFO], [WPPT])
		) AS PivotResult
	) AS PostGroups
	ON dbo.vw_Residential_Driver_Lists.chrDriverName = PostGroups.chrDriverName
WHERE dtServiceDate >= '2007-03-07' AND dtServiceDate < '2007-03-08' AND intRemoveService > 0

.....
I'd rather be surfing

 

[AndyInNC]

I'm not sure if I completely understand what you're trying to accomplish, but it looks like you want the first query to include the counts for each driver by Code. Try adding a searched CASE statement for each Code like:

[tt]
SELECT [tab] chrDriverName
[tab][tab][tab], chrProdInstruc
[tab][tab][tab], chrCompany
[tab][tab][tab], Sum(CASE
[tab][tab][tab][tab]WHEN chrCode = 'WPP' THEN 1
[tab][tab][tab][tab]ELSE 0
[tab][tab][tab]END) AS WPPCount
[tab][tab][tab], Sum(CASE
[tab][tab][tab][tab]WHEN chrCode = 'WS' THEN 1
[tab][tab][tab][tab]ELSE 0
[tab][tab][tab]END) AS WSCount
[tab][tab][tab], chrProdInstruc
[tab][tab][tab], chrCompany
FROM[tab][tab]vw_Residential_Driver_Lists rdl
WHERE[tab][tab]dtServiceDate >= '2007-03-07' AND dtServiceDate < '2007-03-08' AND intRemoveService > 0
GROUP BY[tab]chrDriverName, chrProdInstruc, chrCompany
[/tt]

This will give a count, but group the records together.