May 16, 2017 at 7:38 am
I have two queries the first one is for the header information and the second is for the record information. I know I can union but then I have to have the same columns in each but I can't use that so how can I do it?? Thanks in advance and please don't beat me up to bad.
the results I need is
Header information
record information
record information(if there is more than one record for the header information)
Header information
record information
Header information
record information
etc....
Header query
SELECT '1234' AS clientid
,' ' as trade_style
,'A' AS record_type
,SO.ompCustomerOrganizationID
,so.ompShipLocationID
,cmoName
,cmoAddressLine2
,cmoCity
,cmoState
,cmoPhoneNumber
FROM m1_kf.dbo.SalesOrders SO
LEFT JOIN m1_kf.dbo.Organizations ON cmoOrganizationID = SO.ompCustomerOrganizationID
WHERE SO.ompOrderDate >= CONVERT(VARCHAR, GETDATE()-1, 101)
record query
select
'1234' AS clientid
,' ' as trade_style
,'R' AS record_type
,ompCustomerOrganizationID
,ompSalesOrderID
,ompOrderTotalBase
,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') AS [MMDDYY]
,ompPaymentTermID
,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') AS [MMDDYY]
from m1_kf.dbo.SalesOrders where ompOrderDate >= CONVERT(VARCHAR, GETDATE()-1, 101)
May 16, 2017 at 8:08 am
What is going to digest the results of these queries? I can't think of many applications that can handle different columns in a single result set.
May 16, 2017 at 8:28 am
Firstly, I'm actually a little more concerned about your WHERE statement of:CONVERT(VARCHAR, GETDATE()-1, 101)
Why are you converting your date to a varchar? Is your field ompOrderDate also a varchar? If so, this is going to give you some odd results (for example, 12/12/2016 would be seen as > 05/15/2017). If you're field is a date(time), pass it a date(time). For example:CONVERT(date, GETDATE()-1)
If it's not a date(time) datatype, then you should make serious considerations of changing this.
Now for your question at hand. 🙂 Unfortunately, to my dismay, I had to do something similar to this recently for a file submission. My header section had 5 columns, policy sections had 20 columns, driver was about 6, and vehicle had about 8. I don't know why people ask for data in a format like this, it's not pretty, but i imagine it because they have some kind of odd iterative system, that works on a line by line process.
Looking at your query as well, I would hazard a guess that your header row would return more than once as you're joining from the SalesOrders to the Organizations table. So multiple Sales orders = multiple lines. You don't describe in your post what field defines where a record end and a header starts, so I've created some different sample data to display how I basically did my solution. The main part is getting the data you want, and marking your Header rows. Then making sure your data types and column numbers are the same, and finally getting the ordering of your data right. Hopefully you can apply this to your scenario.CREATE TABLE #Company
(CompanyID int IDENTITY(1,1),
CompanyName varchar(50),
Addr1 varchar(50),
Addr2 varchar(50),
Addr3 varchar(50),
Pcode varchar(10),
Phone varchar(15));
GO
CREATE TABLE #Sale
(SaleID int IDENTITY(1,1),
CompanyID int,
CustomerName varchar(50),
Phone varchar(15),
SaleValue decimal(8,2),
SaleDate datetime);
GO
INSERT INTO #Company (CompanyName, Addr1, Addr2, Addr3, Pcode, Phone)
VALUES ('Mr Smith''s Cars','123 Road','City','County','AB12 2BD', '01234 567890'),
('John Terry Sports','13 lane', 'Little Town','Big County', 'SD1 1GD', '01657 475463');
GO
INSERT INTO #Sale (CompanyID,CustomerName, Phone, SaleValue, SaleDate)
VALUES (1, 'John Smith', '01654 340545', 1204.35, '20170516 15:11:22.000'),
(1, 'Paul Johns', '01964 968724', 784.35, '20170514 12:12:59.000'),
(1, 'Jane Air', '04854 687627', 8841.74, '20170516 12:57:48.000'),
(2, 'Joe Bloggs', '07498 473241', 657.72, '20170516 09:24:01.000');
GO
SELECT *
FROM #Company;
SELECT *
FROM #Sale;
GO
WITH Companies AS (
SELECT *,
1 AS Header
FROM #Company C
WHERE C.CompanyID IN (SELECT S.CompanyID
FROM #Sale S
WHERE S.CompanyID = C.CompanyID
AND S.SaleDate >= CONVERT(date, GETDATE() -1))),
Sales AS (
SELECT *,
0 AS Header
FROM #Sale S
WHERE S.SaleDate >= CONVERT(date, GETDATE() -1)),
Unions AS (
SELECT C.CompanyID,
C.CompanyName,
C.Addr1, C.Addr2, C.Addr3, C.Pcode,
C.Phone,
C.Header
FROM Companies C
UNION ALL
SELECT S.CompanyID,
S.CustomerName, CAST(S.Phone AS varchar(50)),
CAST(S.SaleValue AS varchar(50)), CONVERT(varchar, SaleDate, 101),
NULL, NULL,
Header
FROM Sales S)
SELECT U.CompanyName, U.Addr1, U.Addr2, U.Addr3, U.Pcode, U.Phone
FROM Unions U
ORDER BY U.CompanyID, U.Header DESC;
GO
DROP TABLE #Company;
DROP TABLE #Sale;
GO
P.s. I still hate SSC scrambling my indentations, and I am not fixing it this time. Sorry, I agree, it looks awful.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 16, 2017 at 9:02 am
Usually, you want to repeat header information as part of the key for each row. That way, the front end will consume the data and present it in the correct way. Otherwise, you might mix the order and mess up your data.
SELECT '1234' AS clientid
,' ' as trade_style
,'A' AS record_type
,SO.ompCustomerOrganizationID
,so.ompShipLocationID
,cmoName
,cmoAddressLine2
,cmoCity
,cmoState
,cmoPhoneNumber
,ompOrderTotalBase
,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') AS [MMDDYY]
,ompPaymentTermID
,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') AS [MMDDYY]
FROM m1_kf.dbo.SalesOrders SO
LEFT JOIN m1_kf.dbo.Organizations ON cmoOrganizationID = SO.ompCustomerOrganizationID
WHERE SO.ompOrderDate >= CONVERT(VARCHAR, GETDATE()-1, 101)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply