Help with date formats 1

[Cpreston]

Hi

I am creating a view that needs to look back from todays date to the last 2 months. I am only ointerested in working dates and also need to inculde a holiday tabloe fo dates

So I need this months working days, Decembers and Novembers. I need it to always look look at the current month and the last 2 full months including the holiday days to be taken away as well as weekends.
So in Febuary i would currrent month Feb and then january and December.

In the view I have a table with caledarmonth and calenderyear fields.
I also ahve a table called calender that as one column with the dates on for many years ahead, for each day.

Could someone give me some ideas how to create this and also take away the hoildaydays from the total working days.

not sure if I have expalined myself very well

Thanks

 

[fredericofonseca]

- (SELECT COUNT(*) AS Expr1
FROM dbo.[148-Holiday] as hol
WHERE hol.HolidayDate >= bd.start_previous_month) -- remove this bracket
AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1
) AS WorkingDaysL1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Thanks had to add bd.start_previous_month in as a Group by also and then the query ran ok.

The working days is giving me 32 where I would be expecting 54. Any ideas please

Thanks

 

[fredericofonseca]

split each one of the components of the main select so you get each individual value - then look and see which one is not the one you were expecting.
very hard to help when we don't have neither the full requirements, neither the data neither the business knowledge to link all the tables.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Ok I will see what I can manage. But as I mentioned the working days appears to be the incorrect thing. The table Productanalysis does only have 2 date fields Year and Month, not a day one.

I wills see what I can do with the select breakdown as you mentioned. Thanks for the help

 

[Cpreston]

Hi

I have tried to decipher everything but not having much luck. It is probably down to my inexperience in this area and I cannot see the obvious.
Is there anyway on here we can get remote support or remote training and what the cost would be. I believe it is just for general help for people and I appreciate you have gone way beyond normal help.

many Thanks

 

[fredericofonseca]

which country (and state if applicable) are you from? that may help someone point good local help.

Some of us here may do it but I don't know who does it - I sometimes help privately but not commonly as I'm too expensive for most.

As for helping here - at least on this forum I am aware of other 5 or 6 that go way out of their way to help - and quite good they are at it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

I am in UK England and in the county of Lincolnshire.

Also I have been working on the code and I am slowly getting further forward. I have the working days now and have been beginning to add in other tables so I can see other data required.
All as been going well but now for some codes I am getting duplicate rows, even though when I check in our database there is only one row it could be. I have attached the result and also the code as I have it below. It appears to be getting 2 rows for branched = 1 and the stockavailable of 0 and 4.3981. The correct row is the one with 4.3981. I have tried many joins but still get the same result. Any ideas please. (byt he way I have just one code selected currently just to try and get rid of the duplicate row) Many Thanks

SELECT pa.ProductID
, (SELECT DATEDIFF(dd, bd.start_previous_month , lid.InvoiceDate) + 1
- DATEDIFF(wk, bd.start_previous_month , lid.InvoiceDate) * 2
- CASE WHEN DATENAME(dw, bd.start_previous_month) = 'Sunday' THEN 1 ELSE 0 END
- CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END
- (SELECT COUNT(*) AS Expr1
FROM dbo.[148-Holiday] as hol
WHERE hol.HolidayDate >= bd.start_previous_month
AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1
) AS WorkingDaysL1
, CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END AS VolumeUsed
, pr.ProductCode
,pr.Description
,pso.StockLevelMin
,pso.StockLevelMax
,st.stockavailable
,pa.BranchID
FROM dbo.ProductAnalysis as pa
INNER JOIN dbo.Product as pr
ON pa.ProductID = pr.ProductID
INNER JOIN dbo.ProductGroup as pg
ON pr.ProductGroupID = pg.ProductGroupID
INNER JOIN dbo.[148-vwQuantityUsedCheck_Usage_MDF3] as qucu
ON pa.ProductID = qucu.ProductID
INNER JOIN dbo.productStockOption as pso
on pso.productid = pr.productid
INNER JOIN dbo.Stock as st ON st.ProductID = PSO.ProductID
CROSS JOIN dbo.LastInvoiceDate AS lid
outer apply (select DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) as start_previous_month) as bd
WHERE (pa.CalendarYear = datepart(year, GETDATE()) OR pa.CalendarYear = datepart(YEAR, DATEADD(month, - 1, GETDATE())))
AND (pa.CalendarMonth = datepart(MONTH, GETDATE()) OR pa.CalendarMonth = datepart(MONTH, DATEADD(month, - 1, GETDATE())))
GROUP BY pa.BranchID
, pa.ProductID
, bd.start_previous_month
, pg.Level1ID
, lid.InvoiceDate
, CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END
, pr.ProductCode
, pr.Description
, pso.StockLevelMin
, pso.StockLevelMax
, st.stockavailable
HAVING pa.BranchID IN (1,9)
AND pg.Level1ID = 893
AND CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END > 0.0001
AND pr.ProductCode Like 'am%'
AND (Pso.stocklevelmin > 0) AND (PSO.StockLevelMax > 0)
and pr.ProductCode = 'AM015068CR'

 

[philhege]

Do you have multiple warehouse locations for the same product ID?

-----------
With business clients like mine, you'd be better off herding cats.

 

[Cpreston]

Hi

No but we have different branches which
the only ones we are interested are 1 and 9.

It appears to bring in 2 lots of stock available and always with 0.00.
On our system it only as one showing.

I also tried select distinct but no luck.

Thanks

 

[fredericofonseca]

what does
select *
from dbo.Stock st
where st.ProductID = 24402 returns? I would say more than one row and that will indicate that you need to add another column to the join


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

select * from stock where productid= 24402 brings back the following result.

 

[fredericofonseca]

change 
   INNER JOIN dbo.Stock as st ON st.ProductID = PSO.ProductID 
to
INNER JOIN dbo.Stock as st
   ON st.ProductID = PSO.ProductID 
   AND st.BranchID = pa.BranchID

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Ok thanks that worked.

I now have to add in some more columns from other tables, but cannot add one

, SUM(PA.QuantityUsed) AS QTYUsed
, pr.ProductCode
,pr.Description
,pso.StockLevelMin
,pso.StockLevelMax
,st.stockavailable
,pa.BranchID

,SUM(WorksOrderLine.TotalVolume) AS m3

I would like to set worksorderline as wo but if I do like the below it does not recognise the column

,SUM(w0.WorksOrderLine.TotalVolume) AS m3

Obviously this in the incorrect method, so where do I add the WO alias in a line so I can use it for the sum?

Thanks as always,

 

[Cpreston]

Hi

Please ignore the last post I have managed to add the columns required.

What I am now trying to achieve is doing is a calculation of qtyused\working days which should give me a useage per day figure.

Could you please advise how I add this line in please so it does the calculation and displays the result.

I will keep on trying in the meantime

Thanks

 

[Cpreston]

Hi

My code is now as below
could someone advise on how to add code for doing calucaltions

I need columns that show

useageperday which should equal WorkingDaysL1 \ QTYUsed

wdaysbeforezerostock which should equal stockavilable \ useageperday

wdays0fwom3 which should equal m3_wo \ useageperday

I have tried many ways to add the calculation in so it displays the result in a olumn but cant get the code right. Please could someone let me know how to achieve this. many thanks


SELECT pa.ProductID
, (SELECT DATEDIFF(dd, bd.start_previous_month , lid.InvoiceDate) + 1
- DATEDIFF(wk, bd.start_previous_month , lid.InvoiceDate) * 2
- CASE WHEN DATENAME(dw, bd.start_previous_month) = 'Sunday' THEN 1 ELSE 0 END
- CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END
- (SELECT COUNT(*) AS Expr1
FROM dbo.[148-Holiday] as hol
WHERE hol.HolidayDate >= bd.start_previous_month
AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1
) AS WorkingDaysL1
,pr.ProductCode
,pr.Description
,pso.StockLevelMin
,pso.StockLevelMax
,st.stockavailable
,pa.BranchID
,SUM(PA.QuantityUsed) AS QTYUsed
,pr.udfTopTier as TOPTIER
,ws.ScheduleNumber as SchedNum
,m3wo.m3 AS M3_WO

FROM dbo.ProductAnalysis as pa
INNER JOIN dbo.Product as pr
ON pa.ProductID = pr.ProductID
INNER JOIN dbo.ProductGroup as pg
ON pr.ProductGroupID = pg.ProductGroupID
INNER JOIN dbo.[148-vwQuantityUsedCheck_Usage_MDF3] as qucu
ON pa.ProductID = qucu.ProductID
INNER JOIN dbo.productStockOption as pso
on pso.productid = pr.productid
INNER JOIN dbo.Stock as st ON st.ProductID = PSO.ProductID
AND st.BranchID = pa.BranchID
Left Outer Join "148-vwOnWOSchedule" as ws
on ws.ProductID = pa.ProductID
Left Outer Join "148-vwOnWorksOrder" as m3wo
on m3wo.ProductID = pa.ProductID

CROSS JOIN dbo.LastInvoiceDate AS lid
outer apply (select DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) as start_previous_month) as bd
WHERE (pa.CalendarYear = datepart(year, GETDATE()) OR pa.CalendarYear = datepart(YEAR, DATEADD(month, - 1, GETDATE())))
AND (pa.CalendarMonth = datepart(MONTH, GETDATE()) OR pa.CalendarMonth = datepart(MONTH, DATEADD(month, - 1, GETDATE())))
GROUP BY pa.BranchID
, pa.ProductID
, bd.start_previous_month
, pg.Level1ID
, lid.InvoiceDate
, CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END
, pr.ProductCode
, pr.Description
, pso.StockLevelMin
, pso.StockLevelMax
, st.stockavailable
,pr.udfTopTier
,m3wo.m3
,ws.ScheduleNumber
HAVING pa.BranchID IN (1,9)
AND pg.Level1ID = 893
AND pr.ProductCode Like 'am%'
AND (Pso.stocklevelmin > 0) AND (PSO.StockLevelMax > 0)
-- and pr.ProductCode = 'AM015068CR'

 

[fredericofonseca]

select *
     , WorkingDaysL1 / QTYUsed as UsagePerDay
     , stockavailable / (WorkingDaysL1 / QTYUsed) as wdaysbeforezerostock 
     , m3_wo / (WorkingDaysL1 / QTYUsed) as wdays0fwom3 
from ( SELECT pa.ProductID
            , (SELECT DATEDIFF(dd, bd.start_previous_month , lid.InvoiceDate) + 1
                      - DATEDIFF(wk, bd.start_previous_month , lid.InvoiceDate) * 2
                      - CASE WHEN DATENAME(dw, bd.start_previous_month) = 'Sunday' THEN 1 ELSE 0 END
                      - CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END
                      - (SELECT COUNT(*) AS Expr1
                         FROM dbo.[148-Holiday] as hol
                         WHERE hol.HolidayDate >= bd.start_previous_month 
                         AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1
               ) AS WorkingDaysL1
            , pr.ProductCode
            , pr.Description
            , pso.StockLevelMin
            , pso.StockLevelMax
            , st.stockavailable
            , pa.BranchID
            , SUM(PA.QuantityUsed) AS QTYUsed
            , pr.udfTopTier as TOPTIER
            , ws.ScheduleNumber as SchedNum
            , m3wo.m3 AS M3_WO

       FROM dbo.ProductAnalysis as pa
       INNER JOIN dbo.Product as pr
          ON pa.ProductID = pr.ProductID
       INNER JOIN dbo.ProductGroup as pg
          ON pr.ProductGroupID = pg.ProductGroupID
       INNER JOIN dbo.[148-vwQuantityUsedCheck_Usage_MDF3] as qucu
          ON pa.ProductID = qucu.ProductID
       INNER JOIN dbo.productStockOption as pso
          on pso.productid = pr.productid
       INNER JOIN dbo.Stock as st
          ON st.ProductID = PSO.ProductID 
          AND st.BranchID = pa.BranchID
       Left Outer Join "148-vwOnWOSchedule" as ws
          on ws.ProductID = pa.ProductID
       Left Outer Join "148-vwOnWorksOrder" as m3wo
          on m3wo.ProductID = pa.ProductID
       CROSS JOIN dbo.LastInvoiceDate AS lid
       outer apply (select DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) as start_previous_month) as bd
       WHERE (pa.CalendarYear = datepart(year, GETDATE()) OR pa.CalendarYear = datepart(YEAR, DATEADD(month, - 1, GETDATE())))
       AND (pa.CalendarMonth = datepart(MONTH, GETDATE()) OR pa.CalendarMonth = datepart(MONTH, DATEADD(month, - 1, GETDATE())))
       GROUP BY pa.BranchID
              , pa.ProductID
              , bd.start_previous_month
              , pg.Level1ID
              , lid.InvoiceDate
              , CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END
              , pr.ProductCode
              , pr.Description
              , pso.StockLevelMin
              , pso.StockLevelMax
              , st.stockavailable
              , pr.udfTopTier 
              , m3wo.m3 
              , ws.ScheduleNumber
       HAVING pa.BranchID IN (1,9)
          AND pg.Level1ID = 893
          AND pr.ProductCode Like 'am%'
          AND (Pso.stocklevelmin > 0)
          AND (PSO.StockLevelMax > 0)
          -- and pr.ProductCode = 'AM015068CR'
       ) t

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Thanks for the code. I have applied it and a new issue seems to have appeared. The QTYused only appears to be calculating for jan 2016 and Feb 2016 and is ignoring December 2015.

I have manually gone into the productanalysis (PA) table and summed the figures and they come out as below, which matches our system.

qtyused
86.0974 DEC
120.4513 Jan
19.0762 Feb
225.6249 (totals)

what I am getting is 139.5275 which appears to be the sum of Jan and Feb. I have tried to change the dates to -2 but still don't get same results. any ideas why it appears to be leaving December out of the equation. The working days is ok so it must be including December but not in the SUM.

Thanks

 

[fredericofonseca]

you didn't do the date ranges as I per example of date manipulation I gave you - do that please.

And as part of any development when one does not get the ranges required we remove the affected code and hardcode the values so we know we are looking at all data.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Sorry I am somewhat confused with all the various changes I have done to get thus far.

could you remind me what I should change to what here my current code is below, I did go through all the posts but maybe it is a lack of my understanding.

select *
, WorkingDaysL1 / QTYUsed as UsagePerDay
, stockavailable / (WorkingDaysL1 / QTYUsed) as wdaysbeforezerostock
, m3_wo / (WorkingDaysL1 / QTYUsed) as wdays0fwom3
from ( SELECT pa.ProductID
, (SELECT DATEDIFF(dd, bd.start_previous_month , lid.InvoiceDate) + 1
- DATEDIFF(wk, bd.start_previous_month , lid.InvoiceDate) * 2
- CASE WHEN DATENAME(dw, bd.start_previous_month) = 'Sunday' THEN 1 ELSE 0 END
- CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END
- (SELECT COUNT(*) AS Expr1
FROM dbo.[148-Holiday] as hol
WHERE hol.HolidayDate >= bd.start_previous_month
AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1
) AS WorkingDaysL1
, pr.ProductCode
, pr.Description
, pso.StockLevelMin
, pso.StockLevelMax
, st.stockavailable
, pa.BranchID
, SUM(PA.QuantityUsed) AS QTYUsed
, pr.udfTopTier as TOPTIER
, ws.ScheduleNumber as SchedNum
, m3wo.m3 AS M3_WO

FROM dbo.ProductAnalysis as pa
INNER JOIN dbo.Product as pr
ON pa.ProductID = pr.ProductID
INNER JOIN dbo.ProductGroup as pg
ON pr.ProductGroupID = pg.ProductGroupID
INNER JOIN dbo.[148-vwQuantityUsedCheck_Usage_MDF3] as qucu
ON pa.ProductID = qucu.ProductID
INNER JOIN dbo.productStockOption as pso
on pso.productid = pr.productid
INNER JOIN dbo.Stock as st
ON st.ProductID = PSO.ProductID
AND st.BranchID = pa.BranchID
Left Outer Join "148-vwOnWOSchedule" as ws
on ws.ProductID = pa.ProductID
Left Outer Join "148-vwOnWorksOrder" as m3wo
on m3wo.ProductID = pa.ProductID
CROSS JOIN dbo.LastInvoiceDate AS lid
outer apply (select DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) as start_previous_month) as bd
WHERE (pa.CalendarYear = datepart(year, GETDATE()) OR pa.CalendarYear = datepart(YEAR, DATEADD(month, - 2, GETDATE())))
AND (pa.CalendarMonth = datepart(MONTH, GETDATE()) OR pa.CalendarMonth = datepart(MONTH, DATEADD(month,- 2, GETDATE())))
GROUP BY pa.BranchID
, pa.ProductID
, bd.start_previous_month
, pg.Level1ID
, lid.InvoiceDate
, CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END
, pr.ProductCode
, pr.Description
, pso.StockLevelMin
, pso.StockLevelMax
, st.stockavailable
, pr.udfTopTier
, m3wo.m3
, ws.ScheduleNumber

HAVING pa.BranchID = 1
AND pg.Level1ID = 893
AND pr.ProductCode Like 'am%'
AND (Pso.stocklevelmin > 0)
AND (PSO.StockLevelMax > 0)
and pa.Productid = 24442

) t

 

[fredericofonseca]

see the post that starts with

hum... few things first.

1 - give your tables alias and then use them - not too long of an alias.
2 - if particular values are repetitive throughout the code place them on a outer apply - specially useful if functions are involved
3 - fully qualify your columns with their corresponding table alias
4 - format your code

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886