Cartesian Query on Dates--Crazy Query to boot!

[edmana]

Group,

I have the following tables:

tblServiceOrders (This is a table of tickets)
SONum
Date

tblItemsServiced (This is a table of items on the ticket linked by SONum)
SONum
SerialNum

tblCustomerAssets (This is a table of assets the customer owns which may/may not be on a
SerialNum ticket. Can be linked by SerialNum to tblItemsServiced
Desc

I need (ultimately) to produce a report that shows each day within a date range and shows all items in tblCustomerAssets and a true/false if the item was/was not part of a service ticket in tblServiceOrders.

I am having a hard time w/ this. I have no table of dates currently. I googled creating a table automatically but am not sure if this is the best way vs. a temp table. After I do that, I am guessing I can do a cartesian query but don't know at that point how to do the true/false part.

Anyone have any ideas?

Thanks!
Ed

 

[bborissov]

Here one way to create a date table

DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20120701'
SET @dEnd   = '20120731'

-- Single select statement here
select r.range_date
from  (
select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from       (select 0 b1  union select 1    b1)  t1
cross join (select 0 b2  union select 2    b2)  t2
cross join (select 0 b3  union select 4    b3)  t3
cross join (select 0 b4  union select 8    b4)  t4
cross join (select 0 b5  union select 16   b5)  t5
cross join (select 0 b6  union select 32   b6)  t6
cross join (select 0 b7  union select 64   b7)  t7
cross join (select 0 b8  union select 128  b8)  t8
cross join (select 0 b9  union select 256  b9)  t9
cross join (select 0 b10 union select 512  b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
order by r.range_date

Borislav Borissov
VFP9 SP2, SQL Server

 

[bborissov]

Maybe query should look like this:

DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20120701'
SET @dEnd   = '20120731'

-- Single select statement here
;WITH Cte_Dates (range_date)
AS
(
select r.range_date
from  (
select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from       (select 0 b1  union select 1    b1)  t1
cross join (select 0 b2  union select 2    b2)  t2
cross join (select 0 b3  union select 4    b3)  t3
cross join (select 0 b4  union select 8    b4)  t4
cross join (select 0 b5  union select 16   b5)  t5
cross join (select 0 b6  union select 32   b6)  t6
cross join (select 0 b7  union select 64   b7)  t7
cross join (select 0 b8  union select 128  b8)  t8
cross join (select 0 b9  union select 256  b9)  t9
cross join (select 0 b10 union select 512  b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
)


SELECT Cte_Dates.range_date
FROM  Cte_Dates
LEFT JOIN (SELECT tblServiceOrders.Date,
                  tblCustomerAssets.*
           FROM tblItemsServiced 
           INNER JOIN tblServiceOrders ON tblItemsServiced.SONum  = tblServiceOrders.SONum
           INNER JOIN tblCustomerAssets ON tblItemsServiced.SerialNum =  tblCustomerAssets.SerialNum) Tbl1
order by Cte_Dates.range_date

Borislav Borissov
VFP9 SP2, SQL Server

 

[simian336]

Could you provide a data sample with the expected result.

Simi

 

[edmana]

Borislav,

Thanks!

I think this will give me anything that had a service order. How would I go about showing all items in tblCustomer assets?

Ed

 

[bborissov]

I don't know what you want.
As Simi said, just post some example data and desired result from it.

Borislav Borissov
VFP9 SP2, SQL Server

 

[edmana]

tblService Orders

SONum Date
1 7/1/2012
2 7/2/2012
3 7/5/2012
4 7/6/2012

tblItemsServiced
SONum SerialNum
1 1001
1 1002
2 1003
3 1005
4 1006

tblCustomerInventory

SerialNum Desc
1001 Widget 1
1002 Widget 2
1003 Widget 3
1004 Widget 4
1005 Widget 5
1006 Widget 6
1007 Widget 7


Output
Date Serial Number WasServiced
7/1/2012 1001 True
7/1/2012 1002 True
7/1/2012 1003 False
7/1/2012 1004 False
7/1/2012 1005 False
7/1/2012 1006 False
7/1/2012 1007 False

7/2/2012 1001 False
7/2/2012 1002 False
7/2/2012 1003 True
7/2/2012 1004 False
7/2/2012 1005 False
7/2/2012 1006 False
7/2/2012 1007 False

7/3/2012 1001 False
7/3/2012 1002 False
7/3/2012 1003 False
7/3/2012 1004 False
7/3/2012 1005 False
7/3/2012 1006 False
7/3/2012 1007 False
...

Basically, I need all days within date range, all equipment in tblCustomerAssets, and a flag showing if the item was serviced on that date (or not).

Ed

 

[bborissov]

DECLARE @tblService TABLE (SONum int, Dat datetime)
INSERT INTO @tblService VALUES (1, '20120701')
INSERT INTO @tblService VALUES (2, '20120702')
INSERT INTO @tblService VALUES (3, '20120705')
INSERT INTO @tblService VALUES (4, '20120706')

DECLARE @tblItemsServiced TABLE (SONum int,  SerialNum int)
INSERT INTO @tblItemsServiced VALUES (1, 1001)
INSERT INTO @tblItemsServiced VALUES (1, 1002)
INSERT INTO @tblItemsServiced VALUES (2, 1003)
INSERT INTO @tblItemsServiced VALUES (3, 1005)
INSERT INTO @tblItemsServiced VALUES (4, 1006)

DECLARE @tblCustomerInventory TABLE (SerialNum int, Description varchar(200))
INSERT INTO @tblCustomerInventory VALUES (1001,'Widget 1')
INSERT INTO @tblCustomerInventory VALUES (1002,'Widget 2')
INSERT INTO @tblCustomerInventory VALUES (1003,'Widget 3')
INSERT INTO @tblCustomerInventory VALUES (1004,'Widget 4')
INSERT INTO @tblCustomerInventory VALUES (1005,'Widget 5')
INSERT INTO @tblCustomerInventory VALUES (1006,'Widget 6')
INSERT INTO @tblCustomerInventory VALUES (1007,'Widget 7')


DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20120701'
SET @dEnd   = '20120707'

-- Single select statement here
;WITH Cte_Dates (range_date)
AS
(
select r.range_date
from  (
select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from       (select 0 b1  union select 1    b1)  t1
cross join (select 0 b2  union select 2    b2)  t2
cross join (select 0 b3  union select 4    b3)  t3
cross join (select 0 b4  union select 8    b4)  t4
cross join (select 0 b5  union select 16   b5)  t5
cross join (select 0 b6  union select 32   b6)  t6
cross join (select 0 b7  union select 64   b7)  t7
cross join (select 0 b8  union select 128  b8)  t8
cross join (select 0 b9  union select 256  b9)  t9
cross join (select 0 b10 union select 512  b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
)


SELECT Cte_Dates.range_date,
       tblCustomerInventory.*,
       CASE WHEN Tbl.SerialNum IS NULL THEN 'False' ELSE 'True' END AS  WasServiced
FROM Cte_Dates
CROSS JOIN  @tblCustomerInventory tblCustomerInventory
LEFT JOIN (SELECT tblItemsServiced.SerialNum,
                  tblService.Dat
           FROM @tblItemsServiced  tblItemsServiced
           INNER JOIN @tblService tblService ON tblService.SONum = tblItemsServiced.SONum) Tbl
    ON  tblCustomerInventory.SerialNum = Tbl.SerialNum AND
       Cte_Dates.range_date = Tbl.Dat 
ORDER BY Cte_Dates.range_date, tblCustomerInventory.SerialNum

Borislav Borissov
VFP9 SP2, SQL Server

 

[simian336]

Nice Borislav...

Simi