Solved

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

21 Experts available now in Live!

Get 1:1 Help Now