Solved

need to add a column that says System and the value in the column must read AWD

Posted on 2011-09-02
3
273 Views
Last Modified: 2012-05-12
Please can you assist me i am union a few count querys. I am getting the volume as requiested
but need to add a column showing which system the volume is coming from.
The bellow Query returns
Volume
16
need it to show the headings and data like bellow example
Volume     System
16               AWD


SELECT  Count(distinct W03EU999S.INXFLD03) as Volume

FROM AWDPROD..W03EU999S W03EU999S
		inner JOIN AWDPROD..W20U999S W20U999S with (nolock) ON W03EU999S.CRDATTIM = W20U999S.CRDATTIM 
		inner join AWDPROD..W01U999S W01U999S with (nolock) on W01U999S.CRDATTIM = W20U999S.CRDATTIM

WHERE (W20U999S.ENDWRKTYPE = 'PAYOUTAPP') 
		and (convert(datetime,convert(char(10),W03EU999S.CRDATTIM))) 
		between convert(char(10),GETDATE()-1,120) and convert(char(10),GETDATE(),120)
			AND (W01U999S.DATAVALUE IN ('Prepaid','flexibond'))

Open in new window

0
Comment
Question by:Davesm
3 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 36472081
You might need to post the whole of your t-sql (the other selects in your union) to understand what you are asking

Where do system and AWD come from? Are they aliases? and if so, what do they alias?
0
 

Author Comment

by:Davesm
ID: 36472097
below are the two scripts i am using

what i need to do is identfy that the volume from the first script is from the pryamid
and the volume from the second script is from AWD

need to make one table showing so i can use the data in ssrs 2005
vloume  System
 X           Pryamid
 X           AWD
select count(offer.Description) As Volume
		
into  #Temp_daily 

from POSTGRESQL.pyramid.[public].ap_application a
		LEFT JOIN POSTGRESQL.pyramid.[public].ap_offering_request AS offr ON offr.application_id = a.application_id 
		LEFT JOIN POSTGRESQL.pyramid.[public].lu_offering AS offer ON offer.id = offr.offering_id
		LEFT JOIN POSTGRESQL.pyramid.[public].ap_application_status apps on apps.Application_Status_ID = a.Application_Status_ID
		LEFT JOIN POSTGRESQL.pyramid.[public].lu_milestone mile on mile.id = apps.application_Milestone_ID

where offer.id in (56,73)
		and convert (datetime,a.application_date)   between convert(char(10),GETDATE()-1,120) and convert(char(10),GETDATE(),120)

union		
		
SELECT  Count(distinct W03EU999S.INXFLD03) as Volume

FROM AWDPROD..W03EU999S W03EU999S
		inner JOIN AWDPROD..W20U999S W20U999S with (nolock) ON W03EU999S.CRDATTIM = W20U999S.CRDATTIM 
		inner join AWDPROD..W01U999S W01U999S with (nolock) on W01U999S.CRDATTIM = W20U999S.CRDATTIM

WHERE (W20U999S.ENDWRKTYPE = 'PAYOUTAPP') 
		and (convert(datetime,convert(char(10),W03EU999S.CRDATTIM))) 
		between convert(char(10),GETDATE()-1,120) and convert(char(10),GETDATE(),120)
			AND (W01U999S.DATAVALUE IN ('Prepaid','flexibond'))

Open in new window

0
 
LVL 4

Accepted Solution

by:
mohammad827 earned 500 total points
ID: 36472322
Below should do it for you. Also I would recommend UNION ALL unless you would be getting duplicate records which need to be filtered out

select count(offer.Description) As Volume, 'Pyramid' As System
into  #Temp_daily
from POSTGRESQL.pyramid.[public].ap_application a
            LEFT JOIN POSTGRESQL.pyramid.[public].ap_offering_request AS offr ON offr.application_id = a.application_id
            LEFT JOIN POSTGRESQL.pyramid.[public].lu_offering AS offer ON offer.id = offr.offering_id
            LEFT JOIN POSTGRESQL.pyramid.[public].ap_application_status apps on apps.Application_Status_ID = a.Application_Status_ID
            LEFT JOIN POSTGRESQL.pyramid.[public].lu_milestone mile on mile.id = apps.application_Milestone_ID
where offer.id in (56,73)
            and convert (datetime,a.application_date)   between convert(char(10),GETDATE()-1,120) and convert(char(10),GETDATE(),120)

union            
            
SELECT  Count(distinct W03EU999S.INXFLD03) as Volume, 'AWD' As System
FROM AWDPROD..W03EU999S W03EU999S
            inner JOIN AWDPROD..W20U999S W20U999S with (nolock) ON W03EU999S.CRDATTIM = W20U999S.CRDATTIM
            inner join AWDPROD..W01U999S W01U999S with (nolock) on W01U999S.CRDATTIM = W20U999S.CRDATTIM
WHERE (W20U999S.ENDWRKTYPE = 'PAYOUTAPP')
            and (convert(datetime,convert(char(10),W03EU999S.CRDATTIM)))
            between convert(char(10),GETDATE()-1,120) and convert(char(10),GETDATE(),120)
                  AND (W01U999S.DATAVALUE IN ('Prepaid','flexibond'))
Toggle HighlightingOpen in New WindowSelect All
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question