Solved

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

Posted on 2011-09-02
3
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 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