Subject Re: [firebird-support] SQL output required in vertical format
Author Vishal Tiwari
Post date 2017-07-04T17:15:18Z
Hi SET,

I was Just working on one training Session and remembered you. Just sharing the SQL I prepared. Kindly give your Sign off on it. :)

CREATE TABLE SALES_DETAILS
(
  DTDATE Date NOT NULL,
  SALES_PERSON_CODE Varchar(15) NOT NULL,
  BRANCH_CODE Varchar(15),
  PRIMARY KEY (DTDATE,SALE_PERSON_CODE)
);


INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('01.01.2010', 'S001', 'Branch1');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('02.01.2010', 'S001', 'Branch2');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('03.01.2010', 'S001', 'Branch3');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('04.01.2010', 'S001', 'Branch1');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('05.01.2010', 'S001', 'Branch1');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('06.01.2010', 'S001', 'Branch2');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('01.01.2010', 'S002', 'Branch4');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('02.01.2010', 'S002', 'Branch5');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('03.01.2010', 'S002', 'Branch6');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('04.01.2010', 'S002', 'Branch6');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('05.01.2010', 'S002', 'Branch4');
INSERT INTO SALES_DETAILS (DTDATE, SALES_PERSON_CODE, BRANCH_CODE) VALUES ('06.01.2010', 'S002', 'Branch4');


SQL1:
Select Sales_Person_Code, 
Sum(Case When Branch_Code = 'Branch1' Then 1 Else 0 End) AS Branch1,
Sum(Case When Branch_Code = 'Branch2' Then 1 Else 0 End) AS Branch2,
Sum(Case When Branch_Code = 'Branch3' Then 1 Else 0 End) AS Branch3,
Sum(Case When Branch_Code = 'Branch4' Then 1 Else 0 End) AS Branch4,
Sum(Case When Branch_Code = 'Branch5' Then 1 Else 0 End) AS Branch5,
Sum(Case When Branch_Code = 'Branch6' Then 1 Else 0 End) AS Branch6
From Sales_Details
Where dtDate Between '01/01/2010' And '01/06/2010'
Group By 1

SQL2:
Select Sales_Person_Code, 
Sum (Case Branch_Code When 'Branch1' Then 1 Else 0 End) AS Branch1,
Sum (Case Branch_Code When 'Branch2' Then 1 Else 0 End) AS Branch2,
Sum (Case Branch_Code When 'Branch3' Then 1 Else 0 End) AS Branch3,
Sum (Case Branch_Code When 'Branch4' Then 1 Else 0 End) AS Branch4,
Sum (Case Branch_Code When 'Branch5' Then 1 Else 0 End) AS Branch5,
Sum (Case Branch_Code When 'Branch6' Then 1 Else 0 End) AS Branch6
From Sales_Details
Where dtDate Between '01/01/2010' And '01/06/2010'
Group By 1

With Sales(Sales_Person) As
(Select Distinct Sales_Person_Code
From Sales_Details
Where dtDate Between '01/01/2010' And '01/06/2010'
)

Select S.Sales_Person, 
SD1.Branch_Code As "01-Jan-2010", 
SD2.Branch_Code As "02-Jan-2010", 
SD3.Branch_Code As "03-Jan-2010", 
SD4.Branch_Code As "04-Jan-2010", 
SD5.Branch_Code As "05-Jan-2010", 
SD6.Branch_Code As "06-Jan-2010"

from Sales S

Left Join Sales_Details SD1 On SD1.Sales_Person_Code = S.Sales_Person
And SD1.dtDate = '01/01/2010'
Left Join Sales_Details SD2 On SD2.Sales_Person_Code = S.Sales_Person
And SD2.dtDate = '01/02/2010'
Left Join Sales_Details SD3 On SD3.Sales_Person_Code = S.Sales_Person
And SD3.dtDate = '01/03/2010'
Left Join Sales_Details SD4 On SD4.Sales_Person_Code = S.Sales_Person
And SD4.dtDate = '01/04/2010'
Left Join Sales_Details SD5 On SD5.Sales_Person_Code = S.Sales_Person
And SD5.dtDate = '01/05/2010'
Left Join Sales_Details SD6 On SD6.Sales_Person_Code = S.Sales_Person
And SD6.dtDate = '01/06/2010'


SQL3:
With Sales(Sales_Person) As
(Select Distinct Sales_Person_Code
From Sales_Details
Where dtDate Between '01/01/2010' And '01/06/2010'
)

Select S.Sales_Person, 
SD1.Branch_Code As "01-Jan-2010", 
SD2.Branch_Code As "02-Jan-2010", 
SD3.Branch_Code As "03-Jan-2010", 
SD4.Branch_Code As "04-Jan-2010", 
SD5.Branch_Code As "05-Jan-2010", 
SD6.Branch_Code As "06-Jan-2010",

Sum(Case When SD.Branch_Code = 'Branch1' Then 1 Else 0 End) AS Branch1,
Sum(Case When SD.Branch_Code = 'Branch2' Then 1 Else 0 End) AS Branch2,
Sum(Case When SD.Branch_Code = 'Branch3' Then 1 Else 0 End) AS Branch3,
Sum(Case When SD.Branch_Code = 'Branch4' Then 1 Else 0 End) AS Branch4,
Sum(Case When SD.Branch_Code = 'Branch5' Then 1 Else 0 End) AS Branch5,
Sum(Case When SD.Branch_Code = 'Branch6' Then 1 Else 0 End) AS Branch6

From Sales S

Left Join Sales_Details SD1 ON SD1.Sales_Person_Code = S.Sales_Person
And SD1.dtDate = '01/01/2010'
Left Join Sales_Details SD2 ON SD2.Sales_Person_Code = S.Sales_Person
And SD2.dtDate = '01/02/2010'
Left Join Sales_Details SD3 ON SD3.Sales_Person_Code = S.Sales_Person
And SD3.dtDate = '01/03/2010'
Left Join Sales_Details SD4 ON SD4.Sales_Person_Code = S.Sales_Person
And SD4.dtDate = '01/04/2010'
Left Join Sales_Details SD5 ON SD5.Sales_Person_Code = S.Sales_Person
And SD5.dtDate = '01/05/2010'
Left Join Sales_Details SD6 ON SD6.Sales_Person_Code = S.Sales_Person
And SD6.dtDate = '01/06/2010'

Left Join Sales_Details SD ON SD.Sales_Person_Code = S.Sales_Person
Where SD.dtDate Between '01/01/2010' And '01/06/2010'

Group By 

S.Sales_Person, 
SD1.Branch_Code, 
SD2.Branch_Code, 
SD3.Branch_Code, 
SD4.Branch_Code, 
SD5.Branch_Code, 
SD6.Branch_Code


On Friday, 14 May 2010 1:40 AM, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:


 
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