Hi SET :),
 
It was nice to get your replay.
 
Thanks for your valuable help.
 
I will work on your suggestion. Thanks for Q.2 sql.
 
Thanks And Regards.
 
 
 
Vishal

--- On Fri, 14/5/10, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:


From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: RE: [firebird-support] SQL output required in vertical format
To: "'firebird-support@yahoogroups.com'" <firebird-support@yahoogroups.com>
Date: Friday, 14 May, 2010, 2:08 PM


 



Hi Vishal!
Firebird is not ideal for your question no. 1, but if you know the number of columns to return, then, well, it is almost doable (you have to do some tweaking to my suggestion to get sensible column names, I have not included the dates although that would be just casting the dates as char or varchar and do a UNION). With an unknown number of columns to return, then I'd say it is unsolvable using only SQL. Here's one way to do things if there's three dates, it is easy to expand:

WITH RECURSIVE MyDates(MyDate) AS
(SELECT (CAST :FromDate) AS DATE
FROM RDB$DATABASE
UNION ALL
SELECT MyDate+1
FROM MyDates
WHERE MyDate < :ToDate),
SalesPersons(SalePersonCode) AS
(SELECT DISTINCT SalesPersonCode
FROM SalesDetails
WHERE dtDate BETWEEN :FromDate and :ToDate)

SELECT SP.SalePersonCode, SD1.BranchCode, SD2.BranchCode, SD3.BranchCode
FROM SalesPersons SP
JOIN MyDates MD1 ON MD.MyDate = :FromDate
JOIN MyDates MD2 ON MD1.MyDate+1 = MD2.MyDate
JOIN MyDates MD3 ON MD2.MyDate+1 = MD3.MyDate
LEFT JOIN SalesDetails SD1
ON SP.SalePersonCode = SD1.SalePersonCode
AND MD1.MyDate = SD1.dtDate
LEFT JOIN SalesDetails SD2
ON SP.SalePersonCode = SD2.SalePersonCode
AND MD2.MyDate = SD2.dtDate
LEFT JOIN SalesDetails SD3
ON SP.SalePersonCode = SD3.SalePersonCode
AND MD3.MyDate = SD3.dtDate

Your question no. 2 is simpler:
SELECT SalePersonCode,
sum(CASE WHEN BranchCode = 'Brn1' THEN 1 ELSE 0 END) AS Brn1,
sum(CASE WHEN BranchCode = 'Brn2' THEN 1 ELSE 0 END) AS Brn2,
sum(CASE WHEN BranchCode = 'Brn3' THEN 1 ELSE 0 END) AS Brn3,
sum(CASE WHEN BranchCode = 'Brn4' THEN 1 ELSE 0 END) AS Brn4,
sum(CASE WHEN BranchCode = 'Brn5' THEN 1 ELSE 0 END) AS Brn5
FROM SalesDetails
WHERE dtDate BETWEEN :FromDate and :ToDate
GROUP BY 1

Mixing these two together should not be all too difficult, you just need separate referenced to SalesDetails and expand the GROUP BY.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Vishal Tiwari
Sent: 14. mai 2010 06:41
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL output required in vertical format

Hi All,
 
I have a table "SalesDetails" as follows:
 
dtDate Date Not Null,
SalesPersonCode Varchar(15) Not Null,
BranchCode Varchar(15),
Primary Key(dtDate, SalesPersonCode);

This table will contain the details of each sales person for each date under which brach he/she worked. There are fixed 5 branches. Please have a look into sample data:

dtDate        SalesPersonCode BranchCode
01/01/2010  ABC                    Brn1
01/01/2010  LMN                    Brn5
01/01/2010  PQR                    Brn2
02/01/2010  ABC                    Brn4
02/01/2010  LMN                    Brn5
02/01/2010  PQR                    Brn2
 
 
1) I need to display the follwing desired output into the report, for this i need to get column wise each date details for each employee for give date period i.e. specified Start date to Specified End date. If i fail to express, please look into the following required query output for the report.
 
SalesPersoncode Date1 Date2 Date3 Date4 ..... Date30
ABC                    Brn1  Brn4  Brn4  Brn4  ..... Brn1
LMN                    Brn5  Brn5  Brn5  Brn2  ..... Brn3
PQR                    Brn2  Brn2  Brn2  Brn2  ..... Brn2
 
 
2) With this i need to get under each brench for given date period how many time SalesPerson has worked in tabular format:
 
SalesPerson Brn1 Brn2 Brn3 Brn4 Brn5
ABC             4    0    4    2    20
LMN             4    0    2    4    20
PQR            20   0    4    6    0

I need to achieve above results using sql statement(s) only.
 
And is it possible to get No.1 and No.2 output in using single sql, if not then what could be the best way to achieve this output.

Thanks in advance.
 
Vishal









[Non-text portions of this message have been removed]