Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-02
3
Medium Priority
?
281 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

885 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