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.
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 |
|
|
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 2008MCTS - Database Development SQL SERVER 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 02:56:43
|
you need to do a query like belowSELECT Daterange,TableName,SUM(RecordCount) AS total,..FROM(SELECTFROM View1WHERE datefield >= @YourDatevalue AND datefield < @YourDatevalue+1UNION ALLSELECTFROM View2WHERE datefield >= @YourDatevalue AND datefield < @YourDatevalue+1...)tGROUP BY datefield,TableName I've assumed view/column names make sure you put actual names instead and also required columns------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 = qryProjectProductionInspectedField1 = CreatedByField2 = CreatedDateField3 = DailyCountField4 = ProjectIDView2 = qryProjectProductionEntryRefusedField1 = CreatedByField2 = CreatedDateField3 = DailyCountField4 = ProjectIDView3 = qryProjectProductionNotHomeField1 = CreatedByField2 = CreatedDateField3 = DailyCountField4 = ProjectID |
|
|
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 TotalFROM(SELECT CreatedDate,ProjectID,DailyCountFROM qryProjectProductionInspectedWHERE CreatedDate >= @YourDatevalue AND CreatedDate < @YourDatevalue+1UNION ALLSELECT CreatedDate,ProjectID,DailyCountFROM qryProjectProductionEntryRefusedWHERE CreatedDate >= @YourDatevalue AND CreatedDate < @YourDatevalue+1UNION ALLSELECT CreatedDate,ProjectID,DailyCountFROM qryProjectProductionNotHomeWHERE CreatedDate >= @YourDatevalue AND CreatedDate < @YourDatevalue+1)tGROUP BY DATEADD(dd,DATEDIFF(dd,0,CreatedDate),0),ProjectID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 TotalFROM(SELECT CreatedDate,ProjectPrimaryID,DailyCountBuildingInspectionInspectedFROM qryProjectProductionDailyBuildingInspectionInspectedWHERE CreatedDate >= @2000.01.01 AND CreatedDate < @2050.01.01+1UNION ALLSELECT CreatedDate,ProjectPrimaryID,DailyCountBuildingInspectionRefusedFROM qryProjectProductionDailyBuildingInspectionEntryRefusedWHERE CreatedDate >= @2000.01.01 AND CreatedDate < @2050.01.01+1UNION ALLSELECT CreatedDate,ProjectPrimaryID,DailyCountBuildingInspectionNotHomeFROM qryProjectProductionDailyBuildingInspectionNotHomeWHERE CreatedDate >= @2000.01.01 AND CreatedDate < @2050.01.01+1)tGROUP BY DATEADD(dd,DATEDIFF(dd,0,CreatedDate),0),ProjectPrimaryID |
|
|
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 difficultnames of count can be different but make sure there position in select are the same in all select statements------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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? |
|
|
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 timepartmore details herehttp://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 DailyCountBuildingInspectionNotHomeFROM qryProjectProductionDailyBuildingInspectionInspected qIFULL OUTER JOIN qryProjectProductionDailyBuildingInspectionEntryRefused qR ON qR.CreatedDate >= '20000101' AND qR.CreatedDate < '20500101' AND qR.CreatedDate = qI.CreatedDate AND qR.ProjectPrimaryID = qI.ProjectPrimaryIDFULL OUTER JOIN qryProjectProductionDailyBuildingInspectionNotHome qN ON qN.CreatedDate >= '20000101' AND qN.CreatedDate < '20500101' AND qN.CreatedDate = qI.CreatedDate AND qN.ProjectPrimaryID = qI.ProjectPrimaryIDWHERE 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
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 DailyCountBuildingInspectionNotHomeFROM qryProjectProductionDailyBuildingInspectionInspected qIFULL OUTER JOIN qryProjectProductionDailyBuildingInspectionEntryRefused qR ON qR.CreatedDate >= '20000101' AND qR.CreatedDate < '20500101' AND qR.CreatedDate = qI.CreatedDate AND qR.ProjectPrimaryID = qI.ProjectPrimaryIDFULL OUTER JOIN qryProjectProductionDailyBuildingInspectionNotHome qN ON qN.CreatedDate >= '20000101' AND qN.CreatedDate < '20500101' AND qN.CreatedDate = qI.CreatedDate AND qN.ProjectPrimaryID = qI.ProjectPrimaryIDWHERE 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 DailyCountBuildingInspectionNotHomeFROM qryProjectProductionDailyBuildingInspectionInspected qIFULL OUTER JOIN qryProjectProductionDailyBuildingInspectionEntryRefused qR ON qR.CreatedDate >= '20000101' AND qR.CreatedDate < '20500101' AND qR.CreatedDate = qI.CreatedDate AND qR.ProjectPrimaryID = qI.ProjectPrimaryIDFULL OUTER JOIN qryProjectProductionDailyBuildingInspectionNotHome qN ON qN.CreatedDate >= '20000101' AND qN.CreatedDate < '20500101' AND qN.CreatedDate = qI.CreatedDate AND qN.ProjectPrimaryID = qI.ProjectPrimaryIDWHERE 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 MVPhttp://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') |
|
|
|
|
|
|
|