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 Branch6From
Sales_DetailsWhere 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 Branch6From Sales_DetailsWhere dtDate
Between '01/01/2010' And '01/06/2010'Group By 1
With Sales(Sales_Person) As(Select Distinct Sales_Person_CodeFrom
Sales_DetailsWhere 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_PersonAnd
SD1.dtDate = '01/01/2010'Left Join Sales_Details SD2 On SD2.Sales_Person_Code =
S.Sales_PersonAnd SD2.dtDate = '01/02/2010'Left Join Sales_Details SD3 On
SD3.Sales_Person_Code = S.Sales_PersonAnd SD3.dtDate = '01/03/2010'Left Join
Sales_Details SD4 On SD4.Sales_Person_Code = S.Sales_PersonAnd SD4.dtDate =
'01/04/2010'Left Join Sales_Details SD5 On SD5.Sales_Person_Code =
S.Sales_PersonAnd SD5.dtDate = '01/05/2010'Left Join Sales_Details SD6 On
SD6.Sales_Person_Code = S.Sales_PersonAnd SD6.dtDate = '01/06/2010'
SQL3:With Sales(Sales_Person) As(Select Distinct Sales_Person_CodeFrom
Sales_DetailsWhere 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_PersonAnd
SD1.dtDate = '01/01/2010'Left Join Sales_Details SD2 ON SD2.Sales_Person_Code =
S.Sales_PersonAnd SD2.dtDate = '01/02/2010'Left Join Sales_Details SD3 ON
SD3.Sales_Person_Code = S.Sales_PersonAnd SD3.dtDate = '01/03/2010'Left Join
Sales_Details SD4 ON SD4.Sales_Person_Code = S.Sales_PersonAnd SD4.dtDate =
'01/04/2010'Left Join Sales_Details SD5 ON SD5.Sales_Person_Code =
S.Sales_PersonAnd SD5.dtDate = '01/05/2010'Left Join Sales_Details SD6 ON
SD6.Sales_Person_Code = S.Sales_PersonAnd SD6.dtDate = '01/06/2010'
Left Join Sales_Details SD ON SD.Sales_Person_Code = S.Sales_PersonWhere
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.tysv...@kreftregisteret.no> 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
<!--#yiv1442300629 #yiv1442300629ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1442300629
#yiv1442300629ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1442300629
#yiv1442300629ygrp-mkp #yiv1442300629hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1442300629 #yiv1442300629ygrp-mkp #yiv1442300629ads
{margin-bottom:10px;}#yiv1442300629 #yiv1442300629ygrp-mkp .yiv1442300629ad
{padding:0 0;}#yiv1442300629 #yiv1442300629ygrp-mkp .yiv1442300629ad a
{color:#0000ff;text-decoration:none;}#yiv1442300629 #yiv1442300629ygrp-sponsor
#yiv1442300629ygrp-lc {font-family:Arial;}#yiv1442300629
#yiv1442300629ygrp-sponsor #yiv1442300629ygrp-lc #yiv1442300629hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1442300629
#yiv1442300629ygrp-sponsor #yiv1442300629ygrp-lc .yiv1442300629ad
{margin-bottom:10px;padding:0 0;}#yiv1442300629 a
{color:#1e66ae;}#yiv1442300629 #yiv1442300629actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1442300629
#yiv1442300629activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1442300629
#yiv1442300629activity span {font-weight:700;}#yiv1442300629
#yiv1442300629activity span:first-child
{text-transform:uppercase;}#yiv1442300629 #yiv1442300629activity span a
{color:#5085b6;text-decoration:none;}#yiv1442300629 #yiv1442300629activity span
span {color:#ff7900;}#yiv1442300629 #yiv1442300629activity span
.yiv1442300629underline {text-decoration:underline;}#yiv1442300629
.yiv1442300629attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1442300629 .yiv1442300629attach div a
{text-decoration:none;}#yiv1442300629 .yiv1442300629attach img
{border:none;padding-right:5px;}#yiv1442300629 .yiv1442300629attach label
{display:block;margin-bottom:5px;}#yiv1442300629 .yiv1442300629attach label a
{text-decoration:none;}#yiv1442300629 blockquote {margin:0 0 0
4px;}#yiv1442300629 .yiv1442300629bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1442300629
.yiv1442300629bold a {text-decoration:none;}#yiv1442300629 dd.yiv1442300629last
p a {font-family:Verdana;font-weight:700;}#yiv1442300629 dd.yiv1442300629last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1442300629
dd.yiv1442300629last p span.yiv1442300629yshortcuts
{margin-right:0;}#yiv1442300629 div.yiv1442300629attach-table div div a
{text-decoration:none;}#yiv1442300629 div.yiv1442300629attach-table
{width:400px;}#yiv1442300629 div.yiv1442300629file-title a, #yiv1442300629
div.yiv1442300629file-title a:active, #yiv1442300629
div.yiv1442300629file-title a:hover, #yiv1442300629 div.yiv1442300629file-title
a:visited {text-decoration:none;}#yiv1442300629 div.yiv1442300629photo-title a,
#yiv1442300629 div.yiv1442300629photo-title a:active, #yiv1442300629
div.yiv1442300629photo-title a:hover, #yiv1442300629
div.yiv1442300629photo-title a:visited {text-decoration:none;}#yiv1442300629
div#yiv1442300629ygrp-mlmsg #yiv1442300629ygrp-msg p a
span.yiv1442300629yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1442300629
.yiv1442300629green {color:#628c2a;}#yiv1442300629 .yiv1442300629MsoNormal
{margin:0 0 0 0;}#yiv1442300629 o {font-size:0;}#yiv1442300629
#yiv1442300629photos div {float:left;width:72px;}#yiv1442300629
#yiv1442300629photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv1442300629
#yiv1442300629photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1442300629
#yiv1442300629reco-category {font-size:77%;}#yiv1442300629
#yiv1442300629reco-desc {font-size:77%;}#yiv1442300629 .yiv1442300629replbq
{margin:4px;}#yiv1442300629 #yiv1442300629ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1442300629 #yiv1442300629ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1442300629
#yiv1442300629ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1442300629
#yiv1442300629ygrp-mlmsg select, #yiv1442300629 input, #yiv1442300629 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv1442300629
#yiv1442300629ygrp-mlmsg pre, #yiv1442300629 code {font:115%
monospace;}#yiv1442300629 #yiv1442300629ygrp-mlmsg *
{line-height:1.22em;}#yiv1442300629 #yiv1442300629ygrp-mlmsg #yiv1442300629logo
{padding-bottom:10px;}#yiv1442300629 #yiv1442300629ygrp-mlmsg a
{color:#1E66AE;}#yiv1442300629 #yiv1442300629ygrp-msg p a
{font-family:Verdana;}#yiv1442300629 #yiv1442300629ygrp-msg
p#yiv1442300629attach-count span {color:#1E66AE;font-weight:700;}#yiv1442300629
#yiv1442300629ygrp-reco #yiv1442300629reco-head
{color:#ff7900;font-weight:700;}#yiv1442300629 #yiv1442300629ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv1442300629 #yiv1442300629ygrp-sponsor
#yiv1442300629ov li a {font-size:130%;text-decoration:none;}#yiv1442300629
#yiv1442300629ygrp-sponsor #yiv1442300629ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv1442300629
#yiv1442300629ygrp-sponsor #yiv1442300629ov ul {margin:0;padding:0 0 0
8px;}#yiv1442300629 #yiv1442300629ygrp-text
{font-family:Georgia;}#yiv1442300629 #yiv1442300629ygrp-text p {margin:0 0 1em
0;}#yiv1442300629 #yiv1442300629ygrp-text tt {font-size:120%;}#yiv1442300629
#yiv1442300629ygrp-vital ul li:last-child {border-right:none !important;}-->
