| Author |
 Topic  |
|
|
deano2020
Starting Member
4 Posts |
 Posted - 01/28/2013 : 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
Flowing Fount of Yak Knowledge
India
1407 Posts |
Posted - 01/28/2013 : 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 |
 |
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
Posted - 01/29/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/29/2013 : 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/
|
|
|
|
deano2020
Starting Member
4 Posts |
Posted - 01/29/2013 : 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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/29/2013 : 08:49:26
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
deano2020
Starting Member
4 Posts |
Posted - 01/29/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/29/2013 : 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/
|
|
|
|
deano2020
Starting Member
4 Posts |
Posted - 01/29/2013 : 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/29/2013 : 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/
|
|
|
|
ScottPletcher
Yak Posting Veteran
USA
78 Posts |
Posted - 01/29/2013 : 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)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/29/2013 : 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/
|
|
|
|
ScottPletcher
Yak Posting Veteran
USA
78 Posts |
Posted - 01/30/2013 : 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')
|
|
|
| |
Topic |
|
|
|
Joining Multiple Views with Multiple Dates
