SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joining Multiple Views with Multiple Dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deano2020
Starting Member

4 Posts

Posted - 01/28/2013 :  22:47:37  Show Profile  Reply with Quote
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
2215 Posts

Posted - 01/28/2013 :  23:37:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 01/29/2013 :  01:39:37  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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

India
52317 Posts

Posted - 01/29/2013 :  02:56:43  Show Profile  Reply with Quote
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 - 01/29/2013 :  08:29:02  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/29/2013 :  08:49:26  Show Profile  Reply with Quote

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/

Go to Top of Page

deano2020
Starting Member

4 Posts

Posted - 01/29/2013 :  09:22:36  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/29/2013 :  09:45:05  Show Profile  Reply with Quote
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 - 01/29/2013 :  10:04:11  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/29/2013 :  10:29:42  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 01/29/2013 :  14:53:21  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/29/2013 :  23:50:04  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 01/30/2013 :  10:22:54  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000