Solved

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

Posted on 2011-09-02
3
270 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Set bit value based on sums 4 47
Why does this keep coming up NULL? 2 43
Move SQL 2005 Express to Server 2012R2 19 100
Strange msg in the SSMS pane 13 47
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now