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
 Transact-SQL (2008)
 Joining Multiple Views with Multiple Dates

Author  Topic 

deano2020
Starting Member

4 Posts

Posted - 2013-01-28 : 22:47:37
I have multiple views that count how many records were inserted into a table on a given day. Each view has differing date ranges and values. I'm looking to unite these views into one view where I can see on a given day how many records were created per table.

Is there a way to do this? I'm fairly new to SQL so forgive my ignorance!

Thanks in advance!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-28 : 23:37:11
You can write a procedure/ function to return that result set.. Can you post the input data and expected output? Then only we can help you in right direction

--
Chandu
Go to Top of Page

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2013-01-29 : 01:39:37
Write a join Query with all your views.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 02:56:43
you need to do a query like below

SELECT Daterange,TableName,SUM(RecordCount) AS total,..
FROM
(
SELECT
FROM View1
WHERE datefield >= @YourDatevalue AND datefield < @YourDatevalue+1
UNION ALL
SELECT
FROM View2
WHERE datefield >= @YourDatevalue AND datefield < @YourDatevalue+1
...
)t
GROUP BY datefield,TableName

I've assumed view/column names make sure you put actual names instead and also required columns

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

Go to Top of Page

deano2020
Starting Member

4 Posts

Posted - 2013-01-29 : 08:29:02
Below is an example of the views I'm trying to unite. I only want each unique date to show up once in the final view and the daily counts for each day.

View1 = qryProjectProductionInspected
Field1 = CreatedBy
Field2 = CreatedDate
Field3 = DailyCount
Field4 = ProjectID

View2 = qryProjectProductionEntryRefused
Field1 = CreatedBy
Field2 = CreatedDate
Field3 = DailyCount
Field4 = ProjectID

View3 = qryProjectProductionNotHome
Field1 = CreatedBy
Field2 = CreatedDate
Field3 = DailyCount
Field4 = ProjectID

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 08:49:26
[code]
SELECT DATEADD(dd,DATEDIFF(dd,0,CreatedDate),0),ProjectID,SUM(DailyCount) AS Total
FROM
(
SELECT CreatedDate,ProjectID,DailyCount
FROM qryProjectProductionInspected
WHERE CreatedDate >= @YourDatevalue AND CreatedDate < @YourDatevalue+1

UNION ALL

SELECT CreatedDate,ProjectID,DailyCount
FROM qryProjectProductionEntryRefused
WHERE CreatedDate >= @YourDatevalue AND CreatedDate < @YourDatevalue+1

UNION ALL

SELECT CreatedDate,ProjectID,DailyCount
FROM qryProjectProductionNotHome
WHERE CreatedDate >= @YourDatevalue AND CreatedDate < @YourDatevalue+1
)t
GROUP BY DATEADD(dd,DATEDIFF(dd,0,CreatedDate),0),ProjectID
[/code]

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

Go to Top of Page

deano2020
Starting Member

4 Posts

Posted - 2013-01-29 : 09:22:36
I copied your SQL statement and substituted my field names. It looks like below.

A couple other things: The daily count field names in each view are names something different and the date fields in each view are converted to varchar like this: CONVERT (varchar, CreatedDate, 102). Will this cause problems when doing the union?

SELECT DATEADD(dd,DATEDIFF(dd,0,CreatedDate),0),ProjectPrimaryID,SUM(DailyCount) AS Total
FROM
(
SELECT CreatedDate,ProjectPrimaryID,DailyCountBuildingInspectionInspected
FROM qryProjectProductionDailyBuildingInspectionInspected
WHERE CreatedDate >= @2000.01.01 AND CreatedDate < @2050.01.01+1

UNION ALL

SELECT CreatedDate,ProjectPrimaryID,DailyCountBuildingInspectionRefused
FROM qryProjectProductionDailyBuildingInspectionEntryRefused
WHERE CreatedDate >= @2000.01.01 AND CreatedDate < @2050.01.01+1

UNION ALL

SELECT CreatedDate,ProjectPrimaryID,DailyCountBuildingInspectionNotHome
FROM qryProjectProductionDailyBuildingInspectionNotHome
WHERE CreatedDate >= @2000.01.01 AND CreatedDate < @2050.01.01+1
)t
GROUP BY DATEADD(dd,DATEDIFF(dd,0,CreatedDate),0),ProjectPrimaryID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 09:45:05
yes. dont convert dates to varchar. they'll make date manipulations difficult

names of count can be different but make sure there position in select are the same in all select statements

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

Go to Top of Page

deano2020
Starting Member

4 Posts

Posted - 2013-01-29 : 10:04:11
The CreatedDate field is a datetime field so times are stored in the same field as the date. I'd like to only have the date pulled out of these fields. Does something need to be done to each view in this case...or will the select statement you included take this into account?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 10:29:42
quote:
Originally posted by deano2020

The CreatedDate field is a datetime field so times are stored in the same field as the date. I'd like to only have the date pulled out of these fields. Does something need to be done to each view in this case...or will the select statement you included take this into account?


see logic i used. it will strip off timepart

more details here

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

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

Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-29 : 14:53:21
Please try this, see if it gives you what you need.



SELECT
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate) AS CreatedDate,
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID) AS ProjectPrimaryID,
MAX(qI.DailyCountBuildingInspectionInspected) AS DailyCountBuildingInspectionInspected,
MAX(qR.DailyCountBuildingInspectionRefused) AS DailyCountBuildingInspectionRefused,
MAX(qN.DailyCountBuildingInspectionNotHome) AS DailyCountBuildingInspectionNotHome
FROM qryProjectProductionDailyBuildingInspectionInspected qI
FULL OUTER JOIN qryProjectProductionDailyBuildingInspectionEntryRefused qR ON
qR.CreatedDate >= '20000101' AND qR.CreatedDate < '20500101' AND
qR.CreatedDate = qI.CreatedDate AND
qR.ProjectPrimaryID = qI.ProjectPrimaryID
FULL OUTER JOIN qryProjectProductionDailyBuildingInspectionNotHome qN ON
qN.CreatedDate >= '20000101' AND qN.CreatedDate < '20500101' AND
qN.CreatedDate = qI.CreatedDate AND
qN.ProjectPrimaryID = qI.ProjectPrimaryID
WHERE
qI.CreatedDate >= '20000101' AND qI.CreatedDate < '20500101'
GROUP BY
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate),
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID)
ORDER BY
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate),
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 23:50:04
quote:
Originally posted by ScottPletcher

Please try this, see if it gives you what you need.



SELECT
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate) AS CreatedDate,
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID) AS ProjectPrimaryID,
MAX(qI.DailyCountBuildingInspectionInspected) AS DailyCountBuildingInspectionInspected,
MAX(qR.DailyCountBuildingInspectionRefused) AS DailyCountBuildingInspectionRefused,
MAX(qN.DailyCountBuildingInspectionNotHome) AS DailyCountBuildingInspectionNotHome
FROM qryProjectProductionDailyBuildingInspectionInspected qI
FULL OUTER JOIN qryProjectProductionDailyBuildingInspectionEntryRefused qR ON
qR.CreatedDate >= '20000101' AND qR.CreatedDate < '20500101' AND
qR.CreatedDate = qI.CreatedDate AND
qR.ProjectPrimaryID = qI.ProjectPrimaryID
FULL OUTER JOIN qryProjectProductionDailyBuildingInspectionNotHome qN ON
qN.CreatedDate >= '20000101' AND qN.CreatedDate < '20500101' AND
qN.CreatedDate = qI.CreatedDate AND
qN.ProjectPrimaryID = qI.ProjectPrimaryID
WHERE
qI.CreatedDate >= '20000101' AND qI.CreatedDate < '20500101'
GROUP BY
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate),
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID)
ORDER BY
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate),
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID)




This will miss out cases where a project is not present in qryProjectProductionDailyBuildingInspectionInspected due to the WHERE condition

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

Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-30 : 10:22:54
quote:
Originally posted by visakh16

quote:
Originally posted by ScottPletcher

Please try this, see if it gives you what you need.



SELECT
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate) AS CreatedDate,
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID) AS ProjectPrimaryID,
MAX(qI.DailyCountBuildingInspectionInspected) AS DailyCountBuildingInspectionInspected,
MAX(qR.DailyCountBuildingInspectionRefused) AS DailyCountBuildingInspectionRefused,
MAX(qN.DailyCountBuildingInspectionNotHome) AS DailyCountBuildingInspectionNotHome
FROM qryProjectProductionDailyBuildingInspectionInspected qI
FULL OUTER JOIN qryProjectProductionDailyBuildingInspectionEntryRefused qR ON
qR.CreatedDate >= '20000101' AND qR.CreatedDate < '20500101' AND
qR.CreatedDate = qI.CreatedDate AND
qR.ProjectPrimaryID = qI.ProjectPrimaryID
FULL OUTER JOIN qryProjectProductionDailyBuildingInspectionNotHome qN ON
qN.CreatedDate >= '20000101' AND qN.CreatedDate < '20500101' AND
qN.CreatedDate = qI.CreatedDate AND
qN.ProjectPrimaryID = qI.ProjectPrimaryID
WHERE
qI.CreatedDate >= '20000101' AND qI.CreatedDate < '20500101'
GROUP BY
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate),
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID)
ORDER BY
COALESCE(qI.CreatedDate, qR.CreatedDate, qN.CreatedDate),
COALESCE(qI.ProjectPrimaryID, qR.ProjectPrimaryID, qN.ProjectPrimaryID)




This will miss out cases where a project is not present in qryProjectProductionDailyBuildingInspectionInspected due to the WHERE condition

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






Yes, technically should be:

WHERE
(qI.CreatedDate >= '20000101' AND qI.CreatedDate < '20500101') OR
(qR.CreatedDate >= '20000101' AND qR.CreatedDate < '20500101') OR
(qN.CreatedDate >= '20000101' AND qN.CreatedDate < '20500101')
Go to Top of Page
   

- Advertisement -